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
Advertisements

11 thoughts on “Excel Geeking: Extracting Outlook Address Book Information To Excel Using VBA

  1. Shay

    Hi Scott,

    Thanks for the useful information above, however I am looking for a slightly modified version of this.

    I want to provide the email address of the person, have it look for that and then return other information (department, phone number, etc).

    How can I use this code to search by email and return other properties such as department, number etc.

    Also – how can I generalize that process – is there a list of what those field names are called, is there a way to generate a list?

  2. EC

    I am also wondering how i would get the email address itself in the list. The code works great and I would love to use it, but need the email addresses. Also, what if i do not want the Global Address list, but want a list under “Other Contacts”. Or does it not work like that? Thanks for the work on this.

  3. Eugene

    Great write-up. I too struggled to find an example which simply extracted from Exchange to Excel.

    EC, the e-mail address property is accessible via the “.PrimarySmtpAddress” property. Add this to the example:
    sh.Cells(lCnt, 7) = .PrimarySmtpAddress

      1. Scott

        Not for a bit, I’m moving tomorrow so I’m packing packing packing tonight. I’m a couple of days I’ll be back online.

  4. Raj

    Hi Scott,
    Thanks for sharing and it was very helpful.
    I have the employee names(.Name attribute) displayed in column A.Need to extract the email address from outlook and display it in column B. Can you please advise how to modify this code.

  5. Chris

    Hi

    This is a great and simple tool for doing something I’ve been trying to do for years. Just one question though – is it possible to get the size (number of entries) of the GAL, like you would for an array or table, without cycling through and counting them?

    Thanks
    Chris

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s