Export your Outlook Contacts to Excel
I’m trying to think of why someone would want to export their contacts to Excel… this then got me to think of the reason that makes me do it, and couldn’t find an answer except for the obvious one : “because I can do it”. so whatever your reason is, here goes :
(1) Exposing the Outlook model : in any automation job, your primary task is to make sure that the ‘automated’ application is exposed to Excel, which means you can control the other application’s objects from Excel. In this example, you will need to expose Outlook’s library as follows. In the Visual Basic Editor (Alt+F11 from Excel), go to Tools —> References. Locate the “Microsoft Outlook 9.0 Object Library“ (or other versions i.e. Microsoft Outlook x.x Object Library), check the box, then click on OK.
(2) Import the contacts:
Sub GetContacts()
Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olFldr As Outlook.MAPIFolder
Dim olItms As Outlook.Items
Dim olContact As Variant
Dim i As Long
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace(”MAPI”)
Set olFldr = olNs.GetDefaultFolder(olFolderContacts)
Set olItms = olFldr.Items
olItms.Sort “File As”
i = 1
For Each olContact In olItms
On Error Resume Next
ActiveSheet.Cells(i, 1).Value = olContact.FileAs
ActiveSheet.Cells(i, 2).Value = olContact.Categories
ActiveSheet.Cells(i, 3).Value = “‘” & olContact.MobileTelephoneNumber
ActiveSheet.Cells(i, 4).Value = “‘” & olContact.BusinessTelephoneNumber
ActiveSheet.Cells(i, 5).Value = “‘” & olContact.Email1Address
If olContact.CompanyName <> “” Then
ActiveSheet.Cells(i, 4).Value = olContact.CompanyName
End If
i = i + 1
Next olContact
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub
Dick over at Dick’s Clicks is a must read on Automating Outlook with Excel.
