Getting data from Outlook Address Lists into Excel

Populating a worksheet sheet with data from the exchange Global Address List, or some other outlook address list, should be an easy thing to do, right? When I embarked on this I assumed it was going to be trivial, but what turned out to be the most difficult thing was finding out information on where all the data fields were, and how to access them. This article covers how to do this, and will provide a fully functional downloadable example.

The Outlook Object Model
Before I get started on the topic of this article - how to get outlook address list data into Excel - a few words on Outlook. Unlike some other proprietary software, Outlook has been constrained by the need to be compatible with the rest of the world, to be forever backwards compatible, and yet to be moving forward with other application in the same Office Family. This means that over the years its object model has become labyrinthine, and difficult to navigate. Certainly new capabilities are continually added to give the developer new choices, but whilst all the old choices are still there too, we are both overwhelmed by the multiplicity of methods of doing the same thing and also frustrated that most are only partially implemented, leaving unexposed properties. This has led to a multitude of techniques to get the same information - as well as the use of other approaches like Redemption Objects and CDO to fill the gap. All in all, it's alphabet soup.

Getting Data into Excel.
I had a need to get data into Excel , so that I could do some visualization work on various subsets of the global address list. Since I already have the capability to create Google Visualizations straight from Excel I figured I could do a one shot, "create visualizations straight out of Outlook" type of solution. First though, I needed to generalize and abstract data matching from Global Address List to Excel. As usual there is a downloadable example of this finished project for your use and comment.

Approach
In a sheet with column headings that match an abstracted view of the Outlook Address Book, one of the columns is going to contain the 'key'; (The unique item that is going to be used to find the contact information for each row in the table, for example the email address or the alias or some other outlook field). An inital sheet will look like this, in this case using the alias field as the key field to match on, and the task will be to populate the other columns from our exchange global address list.


Getting the data
In any VBA project, I generally abstract the sheet data from its physical location by using these Data Manipulation Classes. This abstraction of the worksheet is what will be used by the outlook classes. Another advantage of this is that changes are only made to the sheet at the very end, and are not committed if there was a problem during execution. Using this abstraction therefore makes rollback easy (actually unnecessary), and avoids messing around with application.screenupdating.

Creating a class to access outlook
To abstract the outlook address data from its underlying object model, I will create 2 new classes, cOutlookAddressBook (to perform the lookup and populate a cDataSet with matched data from the Outlook address book) and cOutlookApp (to kick off an outlook session). The code for these is below

'cOutlookApp
Option Explicit
Private pWasClosed As Boolean
Private pOutlookApp As Outlook.Application

Public Property Get OutlookApp() As Outlook.Application
    Set OutlookApp = pOutlookApp
End Property
Public Function Init() As Boolean

    Set pOutlookApp = GetObject(, "Outlook.Application")
    'if it wasnt already running, create one
    pWasClosed = pOutlookApp Is Nothing
    If pWasClosed Then
        Set pOutlookApp = New Outlook.Application
    End If
    Init = True
        
End Function
Public Sub Destroy()
    Set pOutlookApp = Nothing
End Sub

'cOutlookAddressBook
Option Explicit
Private pAddressBook As Outlook.AddressList
Private pOutlookApp As cOutlookApp
Private pDirty As Boolean

Public Property Get AddressBook() As Outlook.AddressList
    Set AddressBook = pAddressBook
End Property
Public Function Init(oa As cOutlookApp, sBook As String) As Boolean

    Set pOutlookApp = oa
    ' open the requested address book
    Set pAddressBook = pOutlookApp.OutlookApp.Session.AddressLists(sBook)
    Init = True

End Function
Public Sub Destroy()
    'nothing to do
End Sub
Public Function Populate(ds As cDataSet, target As String) As Boolean
    
    
    Dim e As Outlook.AddressEntry, ex As Outlook.ExchangeUser, sh As String
    Dim sl As String, dr As cDataRow, dc As cCell, sa As String, ltoGo As Long
    ' this is the target key
    sl = LCase(target)
    ltoGo = ds.RowCount
    ' clear out any existing data, except for key
    For Each dr In ds.Rows
        dr.CustomField = False
        For Each dc In ds.Headings
            If LCase(dc.toString) <> sl Then
                dr.Cell(dc.Column).Value = vbNullString
            End If
        Next dc
    Next dr
    
    ' since it takes a while to retrieve exchaneguser, we'll make that outer loop
    For Each e In pAddressBook.AddressEntries
        If ltoGo <= 0 Then Exit For ' if all done lets leave
        Set ex = e.getExchangeUser
        If Not ex Is Nothing Then
            sa = LCase(getValue(e, ex, sl)) ' the key for this record
            For Each dr In ds.Rows
                If Not dr.CustomField Then ' used to determine whether we've been here already

                    If sa = LCase(dr.Cell(sl).toString) Then
                        ' we have a match- we're using the custom field to track completion
                        dr.CustomField = True
                        ltoGo = ltoGo - 1

                        ' need to fill in the lookup fields
                        For Each dc In ds.Headings
                            sh = LCase(dc.toString)
                            ' not the key
                            If sh <> sl Then
                                dr.Cell(dc.Column).Value = getValue(e, ex, sh)
                                If pDirty Then Exit Function
                            End If
                        Next dc
                    End If
                                

                End If
            Next dr
        End If
            
    Next e
    Populate = True
    
End Function
Private Function getValue(e As Outlook.AddressEntry, ex As Outlook.ExchangeUser, colName As String) As String

    getValue = vbNullString
    
    Select Case colName
        Case "alias"
            getValue = ex.Alias
        Case "firstname"
            getValue = ex.FirstName
        Case "lastname"
            getValue = ex.LastName
        Case "officelocation"
            getValue = ex.OfficeLocation
        Case "stateorprovince"
            getValue = ex.StateOrProvince
        Case "streetaddress"
            getValue = ex.StreetAddress
        Case "department"
            getValue = ex.Department
        Case "email"
            getValue = ex.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x39FE001E")
        Case "country"
            getValue = e.PropertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x3a26001e")

        Case Else
            MsgBox (colName & " data not implemented")
            pDirty = True
    End Select

End Function

Execution
To get a field filled in from outlook, just create a column with the name of the abstracted field. You will see from the code above it is pretty straightforward to add additional Outlook fields if they are not yet implemented in the example. Note that use is made of the exchangeUser object as well as the propertyAccessor method and that the column headings should exactly match the case statements in the getValue function, whose purpose is to abstract their Outlook Object model location away from the column data required.

The application procedure
Below is an example of an application that uses these classes (you can download from here) . It expects to execute on the active sheet, and the function, getLikelyColumnRange will assume that everything on the sheet is subject to lookup. You would also probably amend od.Populate(ds, "alias") to identify which is they key field to use for lookup.

Option Explicit
Const sBook = "Global Address List"
Public Sub getOutLookData()
    Dim od As cOutlookAddressbook
    Dim oa As cOutlookApp
    Dim rData As Range, dSets As cDataSets, ds As cDataSet
    ' kick off an outlook session
    Set oa = New cOutlookApp
    If oa.Init Then
        ' get the required addresslist
        Set od = New cOutlookAddressbook
        If od.Init(oa, sBook) Then
            ' you could replace this with a dialogue to get the range to be processed
            Set rData = getLikelyColumnRange
            'create a cdataset of that range
            Set dSets = New cDataSets
            With dSets
                .Create
                .Init rData, , "data"
            End With
            Set ds = dSets.DataSet("data")
            ' now populate - will only actually commit to the sheet if all was well
            ' using the alias field/column as the key
            If od.Populate(ds, "alias") Then
                ds.Commit
            End If

            od.Destroy
        Else
            MsgBox ("Couldnt open address book " & sBook)
            
        End If
        ' clear up
        oa.Destroy
        Set od = Nothing
        Set oa = Nothing
    Else
        MsgBox ("Couldnt start outlook")
    End If
End Sub

Summary
Finding data in the outlook object model was the most time consuming activity in all this. No doubt I (or you, if you make use of this code) will want to extend the getValue function to pick up more data from the address list, which could involve the use of PropertyAccessor.GetProperty() for properties normally unexposed in the Outlook Object Model. I had a lot of trouble finding definitive documentation on where to find what uses what, and reading this 'explanation' from Microsoft along with all the references in other articles to DASL,DAV ,Proptags, schemas and so on, made my head ache, so if anyone knows where to find something complete, simple and defintive - meaning a list of how to get every field displayed by the method ExchangeUser.Details, please comment on this article, or post it here.

Comments, improvements, discussion welcome as usual, and downloadable stuff related to this article is here. In a future article I will probably cover connecting all this to google visualizations right out of Outlook.


By bruce mcpherson   Popularity  (6239 Views)