Getting data from Outlook Address Lists into Excel

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.

Best Microsoft MCTS Training – Microsoft MCITP Training at Certkingdom.com

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.