John Dalesandro

Excel VBA: Retrieving Outlook Address Book Values

By John Dalesandro

Published

The following custom Excel VBA function queries the Outlook address book for a specific name or alias and returns the requested attribute or property value. The function supports both local contacts as well as Exchange address books.

Enable Microsoft Outlook Object Library

To use this function, you must enable the Microsoft Outlook 16.0 Object Library. The version number varies based on the installed version of Microsoft Excel.

  1. From the Developer ribbon in Microsoft Excel, click Visual Basic.
  2. Once the Visual Basic for Applications window opens, go to the Tools menu and select References….
  3. From the References dialog box, check/enable Microsoft Outlook 16.0 Object Library.
  4. Click the OK button.

Create a New VBA Module

A new module is needed to store the function code if one does not already exist in the workbook. If the code is added directly to a worksheet object, then it will not work properly.

  1. From the Visual Basic for Applications window, go to the Insert menu and select Module.

Add Source Code

Add the following code to the newly created module.

Option Explicit

Public Function GetOutlookAddressBookProperty(alias As String, propertyName As String) As Variant
  On Error GoTo errorHandler

  Dim olApp As Outlook.Application
  Dim olNameSpace As Namespace
  Dim olRecipient As Outlook.Recipient
  Dim olExchUser As Outlook.ExchangeUser
  Dim olContact As Outlook.AddressEntry

  Set olApp = CreateObject("Outlook.Application")
  Set olNameSpace = olApp.GetNamespace("MAPI")
  Set olRecipient = olNameSpace.CreateRecipient(LCase(Trim(alias)))

  olRecipient.Resolve

  If olRecipient.Resolved Then
    Set olExchUser = olRecipient.AddressEntry.GetExchangeUser

    If Not olExchUser Is Nothing Then
      GetOutlookAddressBookProperty = Switch(propertyName = "Job Title", olExchUser.JobTitle, _
                                             propertyName = "Company Name", olExchUser.CompanyName, _
                                             propertyName = "Department", olExchUser.Department, _
                                             propertyName = "Name", olExchUser.Name, _
                                             propertyName = "First Name", olExchUser.FirstName, _
                                             propertyName = "Last Name", olExchUser.LastName, _
                                             propertyName = "Country/Region", olExchUser.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x3A26001E"))
    Else
      Set olContact = olRecipient.AddressEntry
      
      If Not olContact Is Nothing Then
        GetOutlookAddressBookProperty = Switch(propertyName = "Job Title", olContact.GetContact.JobTitle, _
                                               propertyName = "Company Name", olContact.GetContact.CompanyName, _
                                               propertyName = "Department", olContact.GetContact.Department, _
                                               propertyName = "Name", olContact.GetContact.FullName, _
                                               propertyName = "First Name", olContact.GetContact.FirstName, _
                                               propertyName = "Last Name", olContact.GetContact.LastName, _
                                               propertyName = "Country/Region", olContact.GetContact.BusinessAddressCountry)
      Else
        GetOutlookAddressBookProperty = CVErr(xlErrNA)
      End If
    End If
  Else
    GetOutlookAddressBookProperty = CVErr(xlErrNA)
  End If

errorHandler:
  If Err.Number <> 0 Then
    GetOutlookAddressBookProperty = CVErr(xlErrNA)
  End If
End Function

Usage

The GetOutlookAddressBookProperty function is now usable in workbook formulas.

=GetOutlookAddressBookProperty(alias, propertyName)

The alias parameter accepts any string value, but the function works best if the value is a uniquely resolvable attribute of an address book entry, e.g., full name, alias name, alias, etc. The function fails if it is unable to resolve to an individual entry in the address book.

The propertyName parameter accepts the following values:

Further Enhancements

While this example only exposes a few properties, many others are available in the ExchangeUser Interface.

You may notice that accessing Country/Region is slightly different because it uses the PropertyAccessor to retrieve the value for an Exchange user. For reference, Microsoft has a list of MAPI Property Tags. However, it is no longer maintained so please be aware that it may be outdated. As an example, the Unicode property tag value for Country/Region is not listed.

There are actively maintained tools available to help identify MAPI property tags, e.g., MFCMAPI and Outlook Spy (which also publishes a list of MAPI Property Tags). In this code, the Country/Region field corresponds to the PR_BUSINESS_ADDRESS_COUNTRY property tag which has a property tag value of 0x3A26001F.

Similarly, phone numbers are accessed differently depending on whether it is a local contact or Exchange user.

For local contact phone numbers, the following properties are available (among others):

For Exchange entries, the following properties are available for PR_BUSINESS_TELEPHONE_NUMBER, PR_HOME_TELEPHONE_NUMBER, and PR_MOBILE_TELEPHONE_NUMBER respectively.

Summary

The custom Excel VBA function retrieves specific attributes from the Outlook address book, including local contacts and Exchange users, by querying based on a name or alias. To use the function, you need to enable the Microsoft Outlook Object Library in VBA, create a new module for the code, and then apply the function in workbook formulas to fetch various properties like job title, company name, or department.