How do I print a list of everyone in the Exchange Global Address List (GAL) ?

The recommended way to create a printed address list containing members of the Global Address List is to create contacts for just the people whose information you need and print it from Contacts. To do this, right-click on the entry or entries and choose Add to Contacts.

The macros on this page create an email message containing the name, alias, email address and phone number of members in an Exchange Global Address List or members of an Exchange Distribution Group. Once in Outlook, the list can be printed or copied to another program (such as Excel or Word.)

Warning: It can take a long time to create a list from a large Global Address List.

List All GAL Members

Sub GetAllGALMembers()

Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olGAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntries
Dim olMember As Outlook.AddressEntry
Dim objMail As Outlook.MailItem

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olGAL = olNS.GetGlobalAddressList()

Set objMail = olApp.CreateItem(olMailItem)
objMail.Body = "Name" & vbTab & "Alias" & vbTab & "Email Address" & vbTab & "Business Phone" & vbCrLf
Set olEntry = olGAL.AddressEntries
On Error Resume Next
' loop through dist list and extract members
Dim i As Long
For i = 1 To olEntry.Count
  Set olMember = olEntry.Item(i)
  
  If olMember.AddressEntryUserType = olExchangeUserAddressEntry Then
  strName = olMember.Name
  strAlias = olMember.GetExchangeUser.Alias
  strAddress = olMember.GetExchangeUser.PrimarySmtpAddress
  strPhone = olMember.GetExchangeUser.BusinessTelephoneNumber
  objMail.Body = objMail.Body & strName & vbTab & " (" & strAlias & ") " & vbTab & strAddress & vbTab & strPhone & vbCrLf
  End If
Next i

objMail.Display

End Sub

List Members of a Distribution list

This macro lists the email address and phone numbers of the members of a specific Exchange distribution group.

Sub GetDGMembers()

Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntry
Dim olMember As Outlook.AddressEntry
Dim lMemberCount As Long
Dim objMail As Outlook.MailItem

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olAL = olNS.AddressLists("Global Address List")

Set objMail = olApp.CreateItem(olMailItem)

' enter the list name
Set olEntry = olAL.AddressEntries("Advertiser Inquiries") 

' get count of dist list members
lMemberCount = olEntry.Members.Count

' loop through dist list and extract members
Dim i As Long
For i = 1 To lMemberCount
  Set olMember = olEntry.Members.Item(i)
  strName = olMember.Name
  strAddress = olMember.GetExchangeUser.PrimarySmtpAddress
  strPhone = olMember.GetExchangeUser.BusinessTelephoneNumber
  
  objMail.Body = objMail.Body & strName & " -- " & strAddress & " -- " & strPhone & vbCrLf
Next i

objMail.Display

End Sub

GAL to Excel

This Outlook macro writes the GAL entries to an Excel workbook. A version of this macro that runs from Excel is at "Use VBA to Export Exchange GAL to Excel".

 Private Const xlUp As Long = -4162
 Sub CopyGALToExcel()
'This is an Outlook Macro
 Dim xlApp As Object
 Dim xlWB As Object
 Dim xlSheet As Object
 Dim bXStarted As Boolean
 
 
Dim i As Long, j As Long, lastRow As Long
Dim olApp As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim olGAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntries
Dim olMember As Outlook.AddressEntry

Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olGAL = olNS.GetGlobalAddressList()

              
'the path of the workbook
 strPath = "d:\Documents\Book1.xlsx"
     On Error Resume Next
     Set xlApp = GetObject(, "Excel.Application")
     If Err <> 0 Then
         Application.StatusBar = "Please wait while Excel source is opened ... "
         Set xlApp = CreateObject("Excel.Application")
         bXStarted = True
     End If
     On Error GoTo 0
     'Open the workbook to input the data
     Set xlWB = xlApp.Workbooks.Open(strPath)
     Set xlSheet = xlWB.Sheets("Sheet1")

    'Find the next empty line of the worksheet
'clear all current entries
xlSheet.Cells.Select
xlApp.Selection.ClearContents

'set and format headings in the worksheet:
xlSheet.Cells(1, 1).Value = "First Name"
xlSheet.Cells(1, 2).Value = "Last Name"
xlSheet.Cells(1, 3).Value = "Phone/Ext"
xlSheet.Cells(1, 4).Value = "Email"
xlSheet.Cells(1, 5).Value = "Title"
xlSheet.Cells(1, 6).Value = "Department"

With xlSheet.Range("A1:F1")

.Font.Bold = True
.HorizontalAlignment = xlCenter

End With

Set olEntry = olGAL.AddressEntries
On Error Resume Next
'first row of entries
j = 2

' loop through dist list and extract members
For i = 1 To olEntry.Count

Set olMember = olEntry.Item(i)

If olMember.AddressEntryUserType = olExchangeUserAddressEntry Then
'add to worksheet
xlSheet.Cells(j, 1).Value = olMember.GetExchangeUser.LastName
xlSheet.Cells(j, 2).Value = olMember.GetExchangeUser.FirstName
xlSheet.Cells(j, 3).Value = olMember.GetExchangeUser.BusinessTelephoneNumber
xlSheet.Cells(j, 4).Value = olMember.GetExchangeUser.PrimarySmtpAddress
xlSheet.Cells(j, 5).Value = olMember.GetExchangeUser.JobTitle
xlSheet.Cells(j, 6).Value = olMember.GetExchangeUser.Department
j = j + 1
End If
Next i

'determine last data row, basis column B (contains Last Name):
lastRow = xlSheet.Cells(Rows.Count, "B").End(xlUp).Row

'format worksheet data area:
xlSheet.Range("A2:F" & lastRow).Sort Key1:=xlSheet.Range("B2"), Order1:=xlAscending
xlSheet.Range("A2:F" & lastRow).HorizontalAlignment = xlLeft
xlSheet.Columns("A:F").EntireColumn.AutoFit

     xlWB.Close 1
     If bXStarted Then
         xlApp.Quit
     End If
     Set xlApp = Nothing
     Set xlWB = Nothing
     Set xlSheet = Nothing
 End Sub

This table lists the fields that would be useful in these macros. See ExchangeUser members for the complete list.

This table lists some of the acceptable Address Types. See OlAddressEntryUserType enumeration for the complete list.

How to use macros

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.

After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To put the code in a module:

1. Right click on Project1 and choose Insert > Module
2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor