Export Outlook Contacts to Excel with VBA

Introduction - Export Outlook Contacts to Excel

Use VBA to export your Outlook Contacts to Excel. With VBA macros, you can choose which Outlook Address Book properties you want to export to your Excel spreadsheet.

Once your contacts list is in Excel, you can play sort, filter and play with your data to your hearts content!

Let’s get right to it.

Example - Export Outlook Contacts to Excel

Sub ExportOutlookAddressBook()
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Exports your Outlook Address Book to Excel.
'NOTES: This macro runs on Excel.
'       Add the Microsoft Outlook Reference library to the project to get this to run
Application.ScreenUpdating = False
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntry
Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olAL = olNS.AddressLists("Contacts") 'Change name if different contacts list name
ActiveWorkbook.ActiveSheet.Range("a1").Select
For Each olEntry In olAL.AddressEntries
     ' your looping code here
     ActiveCell.Value = olEntry.GetContact.FullName 'display name
     ActiveCell.Offset(0, 1).Value = olEntry.Address 'email address
     ActiveCell.Offset(0, 2).Value = olEntry.GetContact.MobileTelephoneNumber 'cell phone number
     ActiveCell.Offset(1, 0).Select
Next olEntry
Set olApp = Nothing
Set olNS = Nothing
Set olAL = Nothing
Application.ScreenUpdating = True
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

Tutorial - Export Outlook Contacts to Excel

Here’s an abridged and censored copy of what my Outlook contacts list looks like when I show it in Excel:

I’m going to make this bold so everyone can see it: This macro runs in Excel - not Outlook. You can modify it to run in Outlook if you’d like, but I designed it to run from Excel. To get the macro to work, you need to add a reference to the Microsoft Outlook Object Library from your Excel VBA Editor.

In the example above, I export your contacts full name, primary email address and cell phone number to a spreadsheet. You can pull different Address Book properties from your contacts list by entering a different property after = olEntry.GetContact. Here are a few of your choices:

There are dozens of contact list properties to choose from!

If your Outlook address book is not titled Contacts, you can change the quoted string in the line that reads:

Set olAL = olNS.AddressLists("Contacts")

Exporting from Microsoft Exchange

If you get an error when running the above macro, it’s likely because your contacts are saved via Microsoft Exchange. To export your contact list from the Exchange, run this macro which replaces the For Loop with a Microsoft Exchange compatible loop:

Sub ExportOutlookAddressBook()
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Exports your Microsoft Exchange Outlook Address Book to Excel.
'NOTES: This macro runs on Excel.
' Add the Microsoft Outlook Reference library to the project to get this to run
Application.ScreenUpdating = False
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim olAL As Outlook.AddressList
Dim olEntry As Outlook.AddressEntry
Set olApp = Outlook.Application
Set olNS = olApp.GetNamespace("MAPI")
Set olAL = olNS.AddressLists("Contacts") 'Change name if different contacts list name
ActiveWorkbook.ActiveSheet.Range("a1").Select
For Each olEntry In olAL.AddressEntries
     ' your looping code here
     On Error Resume Next
     ActiveCell.Value = olEntry.GetExchangeUser.Name 'display name
     ActiveCell.Offset(0, 1).Value = olEntry.GetExchangeUser.PrimarySmtpAddress 'email address
     ActiveCell.Offset(1, 0).Select
Next olEntry
Set olApp = Nothing
Set olNS = Nothing
Set olAL = Nothing
Application.ScreenUpdating = True
End Sub

I want to thank readers ChuckD2011 and YasserKhalil for encouraging me to develop this alternate solution.

Application Ideas - Export Outlook Contacts to Excel

Years ago, I exported my Outlook contacts list at work to Excel just to see how big it was. There were 80630 entries at that time. Shocking! If your contacts list is that big, expect this macro to take a long time to run.

Once you get your email addresses in Excel, you can manipulate the data however you like. Because VBA is so powerful, you can choose to send an email to all the email addresses in your contacts list or a subset of your contacts. For example, you can choose to email all the people in your address book with a first name of “Jim.” I’m not sure why you would want to do that, but the point is you can!

It’s not often I feature a macro that interacts with Outlook. As a matter of fact, it’s been over a year since I showed you how to use VBA to search your Outlook emails. Because Outlook posts are so rare, I like to make them worth your time. When you’re ready to take your VBA to the next level, subscribe using the form below.