Excel Geeking: Extracting Outlook Address Book Information To Excel Using VBA

Yeah, I know there are a lot of posts out there about this already. So here’s another.

Truth be told, when I first went searching for items on this topic, I couldn’t find what I was looking for. Granted, it’s not outside the realm of possibility that I simply suck when it comes to a decent Google search. But even when my word choice did happen to land me luckily on a site with information on this topic, I still never really found what I was looking for.

What I really needed was a way to extract some particular columns of data from the Outlook address book. I was looking for Job Title specifically, and Alias if I could get at it. After poking around the web for a bit, and finding some snippets of code here or there, I finally wised up and decided to just follow the Outlook object model.

I love a good object model. Knowing the parent/child relationships from one object to another makes navigating through the application a cinch. For me, the thing I like most about knowing those parent/child relationships, and knowing what the various collections of objects are, is that it lets me iterate through all of the items in those collections.

That’s what I ended up doing with my routine. Once I found the right object that held the properties I was looking for, it was pretty easy to walk back up the model and make sure that I had all the necessary objects in the code.

In this case, because my company is on the Exchange Server, the Outlook object I needed was the ExchangeUser. It has all of the properties I would need, and then some. In addition to what I pulled, you can get address, phone numbers, manager, all kinds of good things. From there I just needed to work backward to figure out what collection did ExchangeUser belong to, and then back up from there. Once that’s been figured out, a simple For..Next loop does the trick.

The only other nuance here is that I need to run this from Excel, so that I could write the data to a worksheet from which I could slice and dice. No problem. Microsoft includes the ability to run other application object models from Excel. All you need to do is to set a reference to the MS Outlook Object Library, and then the Outlook object model is available to you in Excel. The rest is history.

The code for extracting the data is below. The list of properties the ExchangeUser offers can be found here. Enjoy.

Public Sub GetOutlookExchangeUserInformation()
'   This goes into the Global Address List for the MS Exchange Server
'   and returns a selection of data to a worksheet.
'   To get a view off all the potential data poitns that Outlook contains,
'   see the following URL:
'   https://msdn.microsoft.com/en-us/library/microsoft.office.interop.outlook.exchangeuser_properties.aspx

    ' Variable declarations.
    Dim olApp           As Outlook.Application
    Dim olNameSpace     As Namespace
    Dim olAddrList      As AddressList
    Dim olAddrEntry     As AddressEntry
    Dim olExchgnUser    As ExchangeUser
    Dim sh              As Worksheet
    Dim lCnt            As Long

    ' Set the Outlook object variables
    Set olApp = CreateObject("Outlook.Application")
    Set olNameSpace = olApp.GetNamespace("MAPI")
    Set olAddrList = olNameSpace.AddressLists("Global Address List")

    ' Create a new worksheet.
    Set sh = ThisWorkbook.Worksheets.Add

    ' Add some headers for our data.
    With sh
        .Cells(1, 1) = "NAME"
        .Cells(1, 2) = "FIRST NAME"
        .Cells(1, 3) = "LAST NAME"
        .Cells(1, 4) = "ALIAS"
        .Cells(1, 5) = "JOB TITLE"
        .Cells(1, 6) = "DEPARTMENT"
    End With

    ' Start the counter in the second row.
    lCnt = 2

    ' Iterate through the address entires in the address list.
    For Each olAddrEntry In olAddrList.AddressEntries

        ' For each entry, set the an exchange user object.
        ' I'm using t exchange user object because I find that
        ' most companies using Outlook are set up on MS Exchange Server.
        ' You can also use the GetContact whcih will return the
        ' the same information from the Outlook Contact Address Book
        ' (as opposed to the Global Address List).
        Set olExchgnUser = olAddrEntry.GetExchangeUser

        ' Turn off error handling, because occasionally you hit a
        ' record with nothing in it and it throws an error.
        On Error Resume Next

        ' Write the Outlook data to the worksheet.
        With olExchgnUser
            sh.Cells(lCnt, 1) = .Name
            sh.Cells(lCnt, 2) = .FirstName
            sh.Cells(lCnt, 3) = .LastName
            sh.Cells(lCnt, 4) = .Alias
            sh.Cells(lCnt, 5) = .JobTitle
            sh.Cells(lCnt, 6) = .Department
        End With

        ' Because an exchange server could have tens of thousands of entries,
        ' which will cause this routine to run for several minutes,
        ' update the status bar to let the user know that we are in fact
        ' still running and not hung up.
        Application.StatusBar = "Processing record " & lCnt & "..."

        ' If we did not hit an error, increment the counter.
        ' (If we did hit an error, we skip this because if we do not,
        ' we'll get a blank row in the middle of the data set.)
        If Err.Number = 0 Then lCnt = lCnt + 1
        ' Clear any error.
        Err.Clear
        ' Reset the error handling.
        On Error GoTo 0

    Next olAddrEntry

    ' Clear the status bar.
    Application.StatusBar = ""

    ' Prompt the user that we've finished.
    MsgBox "Extract done."

End Sub