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.
Field name | Description |
---|---|
Address | The X400 e-mail address of the Exchange User |
AddressEntryUserType | Returns olExchangeUserAddressEntry which represents the user type of the ExchangeUser |
Alias | The user's alias |
AssistantName | Name of the user's assistant |
BusinessTelephoneNumber | Business telephone number |
City | City |
Comments | Comments in the GAL entry |
CompanyName | The name in the Company field |
Department | The department field |
DisplayType | Returns olUser from the OlDisplayType representing the nature of the ExchangeUser |
FirstName | The user's first name |
JobTitle | The job title of the user |
LastName | The last name of the ExchangeUser |
MobileTelephoneNumber | The mobile telephone number |
Name | Returns the display name for the ExchangeUser object |
OfficeLocation | The office location field |
PostalCode | Postal code |
PrimarySmtpAddress | The primary Simple Mail Transfer Protocol (SMTP) address |
StateOrProvince | State or province |
StreetAddress | Street address |
This table lists some of the acceptable Address Types. See OlAddressEntryUserType enumeration for the complete list.
Address Type | Description |
---|---|
olExchangeUserAddressEntry | An Exchange mailbox; includes users, rooms, resources. |
olExchangeDistributionListAddressEntry | An Exchange distribution list. |
olExchangePublicFolderAddressEntry | Exchange Public Folder |
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:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor
More Information
Extract the members of an Outlook Distribution List to a Word document (VBA Macro)
Get All Email Addresses used on Exchange Server (PowerShell cmdlet)
How to Print an Exchange Distribution List
How to Print Outlook Address Books
what is the field name for country/region?
Thank you for taking the time to post this. It was very helpful.
Hi,
When I run a code to list the members of a distribution list, I get a Runtime error '-2147467259 (8004005)' at the below line:
Set olMember = olEntry.Members.Item(i)
What could be the problem?
I'm trying to pull the members of a DG into an access table and it worked last week, but now it's not liking it half way thru,
lMemberCount = olEntry.Members.Count
says 53, but get to 36 andolEntry.Members.Item(36).GetExchangeUser.Name
gives me91 - Object variable or With block variable not set
Sorry i missed this earlier. Did you get it solved? Do you know what entry is #36?
Great macros! I'm getting run-time error 287: application-defined or object-defined error on
Set olEntry = olGAL.AddressEntries
of theGetAllGALMembers
subfunction. I added the MS outlook 12.0 object library. What am I missing? I'm sure it is simple. Any further help is greatly appreciated.How to look for details (job title, department) of a particular email address in GAL?
The first macro on the pages gets all entries in the gal - the fields you can lookup are listed at the bottom of the page. The big thing is you need to look up the contact before you can get any values.
Hey! I wanted to know if there is a way to get the email sender's details directly from GAL.
I am not able to get the details for senders who are in GAL but not in my local address book.
Thanks.
You'd need to look it in the gal using the address, then yes.
Any direct method to look up other details in GAL considering email address as search keyword?
My code as below: Public Sub DisplaySenderDetails() Dim Sender As Outlook.AddressEntry Dim xlApp As Object Dim xlWB As Object Dim xlSheet As Object Dim rCount As Long Dim bXStarted As Boolean Dim enviro As String Dim strPath As String Dim strColB, strColC, strColD, strColE, strColF, strColG As String Dim objOL As Outlook.Application Dim objItems As Outlook.Items Dim objFolder As Outlook.MAPIFolder Dim obj As Object Dim objNS As Outlook.NameSpace Dim olItem As Outlook.MailItem Dim strdate As String Dim oExUser As Outlook.ExchangeUser Dim olGAL As Outlook.AddressList Dim olEntry As Outlook.AddressEntries ' Code to set up excel Set objNS = GetNamespace("MAPI") Set olGAL = objNS.GetGlobalAddressList() Set objFolder = objNS.GetDefaultFolder(olFolderInbox).Folders("Abc") Set objItems = objFolder.Items Set olEntry = olGAL.AddressEntries For Each obj In objItems With obj Set Sender = obj.Sender Set olItem = obj If TypeName(obj) = "MailItem" Then On Error Resume Next Dim i As Long For i = 1 To olEntry.Count If olEntry.Item.Address = Sender.Address Then Set oExUser = Sender.GetExchangeUser rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row rCount = rCount + 1 strdate = DateValue(olItem.ReceivedTime) If strdate >= #7/1/2016# Then strColB = Sender.Name strColC = oExUser.JobTitle strColD = oExUser.Department strColE = oExUser.PrimarySmtpAddress strColF = olItem.Subject strColG = olItem.ReceivedTime ' code to export the data in… Read more »