The following code exports contacts to Excel from Outlook. This code should be placed in a standard module in Excel. (See Where do I paste the code that I want to use in my workbook for placement assistance)
Since the code is late bound, we need to use the following enumeration when calling code meant for Outlook.
Public Enum OlDefaultFolders olFolderCalendar = 9 olFolderConflicts = 19 olFolderContacts = 10 olFolderDeletedItems = 3 olFolderDrafts = 16 olFolderInbox = 6 olFolderJournal = 11 olFolderJunk = 23 olFolderLocalFailures = 21 olFolderNotes = 12 olFolderOutbox = 4 olFolderSentMail = 5 olFolderServerFailures = 22 olFolderSyncIssues = 20 olFolderTasks = 13 olPublicFoldersAllPublicFolders = 18 End Enum
This code should be placed at the top of a standard module.
Export Contacts
This code will return contacts in the local default Contacts folder and write them to an array. You can then put them into a listbox, or dump them onto a worksheet.
An optional header row is also provided, so you can see what each column of values represents. I chose to grab every possible property of the ContactItem Object where possible.
Function ExportContacts(Optional headerRow As Boolean = False) As String()
' exports contacts only (not distribution lists), no user properties
Dim olApp As Object ' Outlook.Application
Dim olNS As Object ' Outlook.Namespace
Dim contactsFolderItems As Object ' Outlook.items
Dim folderItem As Object
Dim contact As Object ' Outlook.ContactItem
Dim tempString() As String
Dim i As Long
Dim numRows As Long
Dim startRow As Long
Set olApp = GetOutlookApp
Set olNS = GetNS(olApp)
Set contactsFolderItems = GetItems(olNS, olFolderContacts)
' if calling procedure wants header row
If headerRow Then
startRow = 1
Else
startRow = 0
End If
numRows = contactsFolderItems.count
' resize array
ReDim tempString(1 To (numRows + startRow), 1 To 111)
' loop through folder items
For i = 1 To numRows
Set folderItem = contactsFolderItems.item(i)
If IsContact(folderItem) Then
Set contact = folderItem
End If
With contact
tempString(i + startRow, 1) = .Anniversary
tempString(i + startRow, 2) = .AssistantName
tempString(i + startRow, 3) = .AssistantTelephoneNumber
tempString(i + startRow, 4) = .BillingInformation
tempString(i + startRow, 5) = .Birthday
tempString(i + startRow, 6) = .Body
tempString(i + startRow, 7) = .Business2TelephoneNumber
tempString(i + startRow, 8) = .BusinessAddress
tempString(i + startRow, 9) = .BusinessAddressCity
tempString(i + startRow, 10) = .BusinessAddressCountry
tempString(i + startRow, 11) = .BusinessAddressPostalCode
tempString(i + startRow, 12) = .BusinessAddressPostOfficeBox
tempString(i + startRow, 13) = .BusinessAddressState
tempString(i + startRow, 14) = .BusinessAddressStreet
tempString(i + startRow, 15) = .BusinessFaxNumber
tempString(i + startRow, 16) = .BusinessHomePage
tempString(i + startRow, 17) = .BusinessTelephoneNumber
tempString(i + startRow, 18) = .CallbackTelephoneNumber
tempString(i + startRow, 19) = .CarTelephoneNumber
tempString(i + startRow, 20) = .Categories
tempString(i + startRow, 21) = .Companies
tempString(i + startRow, 22) = .CompanyAndFullName
tempString(i + startRow, 23) = .CompanyLastFirstNoSpace
tempString(i + startRow, 24) = .CompanyLastFirstSpaceOnly
tempString(i + startRow, 25) = .CompanyMainTelephoneNumber
tempString(i + startRow, 26) = .CompanyName
tempString(i + startRow, 27) = .ComputerNetworkName
tempString(i + startRow, 28) = .CreationTime
tempString(i + startRow, 29) = .CustomerID
tempString(i + startRow, 30) = .Department
tempString(i + startRow, 31) = .Email1Address
tempString(i + startRow, 32) = .Email1AddressType
tempString(i + startRow, 33) = .Email1DisplayName
tempString(i + startRow, 34) = .Email1EntryID
tempString(i + startRow, 35) = .Email2Address
tempString(i + startRow, 36) = .Email2DisplayName
tempString(i + startRow, 37) = .Email2EntryID
tempString(i + startRow, 38) = .Email3Address
tempString(i + startRow, 39) = .Email3AddressType
tempString(i + startRow, 40) = .Email3DisplayName
tempString(i + startRow, 41) = .Email3EntryID
tempString(i + startRow, 42) = .FileAs
tempString(i + startRow, 43) = .FirstName
tempString(i + startRow, 44) = .FTPSite
tempString(i + startRow, 45) = .FullName
tempString(i + startRow, 46) = .FullNameAndCompany
tempString(i + startRow, 47) = .Gender
tempString(i + startRow, 48) = .GovernmentIDNumber
tempString(i + startRow, 49) = .HasPicture
tempString(i + startRow, 50) = .Hobby
tempString(i + startRow, 51) = .Home2TelephoneNumber
tempString(i + startRow, 52) = .HomeAddress
tempString(i + startRow, 53) = .HomeAddressCity
tempString(i + startRow, 54) = .HomeAddressCountry
tempString(i + startRow, 55) = .HomeAddressPostalCode
tempString(i + startRow, 56) = .HomeAddressPostOfficeBox
tempString(i + startRow, 57) = .HomeAddressState
tempString(i + startRow, 58) = .HomeAddressStreet
tempString(i + startRow, 59) = .HomeFaxNumber
tempString(i + startRow, 60) = .HomeTelephoneNumber
tempString(i + startRow, 61) = .IMAddress
tempString(i + startRow, 62) = .Initials
tempString(i + startRow, 63) = .InternetFreeBusyAddress
tempString(i + startRow, 64) = .ISDNNumber
tempString(i + startRow, 65) = .JobTitle
tempString(i + startRow, 66) = .Journal
tempString(i + startRow, 67) = .language
tempString(i + startRow, 68) = .LastFirstAndSuffix
tempString(i + startRow, 69) = .LastFirstNoSpace
tempString(i + startRow, 70) = .LastFirstNoSpaceAndSuffix
tempString(i + startRow, 71) = .LastFirstNoSpaceCompany
tempString(i + startRow, 72) = .LastFirstSpaceOnly
tempString(i + startRow, 73) = .LastFirstSpaceOnlyCompany
tempString(i + startRow, 74) = .LastModificationTime
tempString(i + startRow, 75) = .LastName
tempString(i + startRow, 76) = .LastNameAndFirstName
tempString(i + startRow, 77) = .MailingAddress
tempString(i + startRow, 78) = .MailingAddressCity
tempString(i + startRow, 79) = .MailingAddressCountry
tempString(i + startRow, 80) = .MailingAddressPostalCode
tempString(i + startRow, 81) = .MailingAddressPostOfficeBox
tempString(i + startRow, 82) = .MailingAddressState
tempString(i + startRow, 83) = .MailingAddressStreet
tempString(i + startRow, 84) = .ManagerName
tempString(i + startRow, 85) = .MiddleName
tempString(i + startRow, 86) = .Mileage
tempString(i + startRow, 87) = .MobileTelephoneNumber
tempString(i + startRow, 88) = .NickName
tempString(i + startRow, 89) = .OfficeLocation
tempString(i + startRow, 90) = .OrganizationalIDNumber
tempString(i + startRow, 91) = .OtherAddress
tempString(i + startRow, 92) = .OtherAddressCity
tempString(i + startRow, 93) = .OtherAddressCountry
tempString(i + startRow, 94) = .OtherAddressPostalCode
tempString(i + startRow, 95) = .OtherAddressPostOfficeBox
tempString(i + startRow, 96) = .OtherAddressState
tempString(i + startRow, 97) = .OtherAddressStreet
tempString(i + startRow, 98) = .OtherFaxNumber
tempString(i + startRow, 99) = .OtherTelephoneNumber
tempString(i + startRow, 100) = .PagerNumber
tempString(i + startRow, 101) = .PersonalHomePage
tempString(i + startRow, 102) = .PrimaryTelephoneNumber
tempString(i + startRow, 103) = .Profession
tempString(i + startRow, 104) = .ReferredBy
tempString(i + startRow, 105) = .Sensitivity
tempString(i + startRow, 106) = .Size
tempString(i + startRow, 107) = .Spouse
tempString(i + startRow, 108) = .Subject
tempString(i + startRow, 109) = .Suffix
tempString(i + startRow, 110) = .Title
tempString(i + startRow, 111) = .WebPage
End With
Next i
' first row of array should be header values
If headerRow Then
tempString(1, 1) = "Anniversary"
tempString(1, 2) = "AssistantName"
tempString(1, 3) = "AssistantTelephoneNumber"
tempString(1, 4) = "BillingInformation"
tempString(1, 5) = "Birthday"
tempString(1, 6) = "Body"
tempString(1, 7) = "Business2TelephoneNumber"
tempString(1, 8) = "BusinessAddress"
tempString(1, 9) = "BusinessAddressCity"
tempString(1, 10) = "BusinessAddressCountry"
tempString(1, 11) = "BusinessAddressPostalCode"
tempString(1, 12) = "BusinessAddressPostOfficeBox"
tempString(1, 13) = "BusinessAddressState"
tempString(1, 14) = "BusinessAddressStreet"
tempString(1, 15) = "BusinessFaxNumber"
tempString(1, 16) = "BusinessHomePage"
tempString(1, 17) = "BusinessTelephoneNumber"
tempString(1, 18) = "CallbackTelephoneNumber"
tempString(1, 19) = "CarTelephoneNumber"
tempString(1, 20) = "Categories"
tempString(1, 21) = "Companies"
tempString(1, 22) = "CompanyAndFullName"
tempString(1, 23) = "CompanyLastFirstNoSpace"
tempString(1, 24) = "CompanyLastFirstSpaceOnly"
tempString(1, 25) = "CompanyMainTelephoneNumber"
tempString(1, 26) = "CompanyName"
tempString(1, 27) = "ComputerNetworkName"
tempString(1, 28) = "CreationTime"
tempString(1, 29) = "CustomerID"
tempString(1, 30) = "Department"
tempString(1, 31) = "Email1Address"
tempString(1, 32) = "Email1AddressType"
tempString(1, 33) = "Email1DisplayName"
tempString(1, 34) = "Email1EntryID"
tempString(1, 35) = "Email2Address"
tempString(1, 36) = "Email2DisplayName"
tempString(1, 37) = "Email2EntryID"
tempString(1, 38) = "Email3Address"
tempString(1, 39) = "Email3AddressType"
tempString(1, 40) = "Email3DisplayName"
tempString(1, 41) = "Email3EntryID"
tempString(1, 42) = "FileAs"
tempString(1, 43) = "FirstName"
tempString(1, 44) = "FTPSite"
tempString(1, 45) = "FullName"
tempString(1, 46) = "FullNameAndCompany"
tempString(1, 47) = "Gender"
tempString(1, 48) = "GovernmentIDNumber"
tempString(1, 49) = "HasPicture"
tempString(1, 50) = "Hobby"
tempString(1, 51) = "Home2TelephoneNumber"
tempString(1, 52) = "HomeAddress"
tempString(1, 53) = "HomeAddressCity"
tempString(1, 54) = "HomeAddressCountry"
tempString(1, 55) = "HomeAddressPostalCode"
tempString(1, 56) = "HomeAddressPostOfficeBox"
tempString(1, 57) = "HomeAddressState"
tempString(1, 58) = "HomeAddressStreet"
tempString(1, 59) = "HomeFaxNumber"
tempString(1, 60) = "HomeTelephoneNumber"
tempString(1, 61) = "IMAddress"
tempString(1, 62) = "Initials"
tempString(1, 63) = "InternetFreeBusyAddress"
tempString(1, 64) = "ISDNNumber"
tempString(1, 65) = "JobTitle"
tempString(1, 66) = "Journal"
tempString(1, 67) = "Language"
tempString(1, 68) = "LastFirstAndSuffix"
tempString(1, 69) = "LastFirstNoSpace"
tempString(1, 70) = "LastFirstNoSpaceAndSuffix"
tempString(1, 71) = "LastFirstNoSpaceCompany"
tempString(1, 72) = "LastFirstSpaceOnly"
tempString(1, 73) = "LastFirstSpaceOnlyCompany"
tempString(1, 74) = "LastModificationTime"
tempString(1, 75) = "LastName"
tempString(1, 76) = "LastNameAndFirstName"
tempString(1, 77) = "MailingAddress"
tempString(1, 78) = "MailingAddressCity"
tempString(1, 79) = "MailingAddressCountry"
tempString(1, 80) = "MailingAddressPostalCode"
tempString(1, 81) = "MailingAddressPostOfficeBox"
tempString(1, 82) = "MailingAddressState"
tempString(1, 83) = "MailingAddressStreet"
tempString(1, 84) = "ManagerName"
tempString(1, 85) = "MiddleName"
tempString(1, 86) = "Mileage"
tempString(1, 87) = "MobileTelephoneNumber"
tempString(1, 88) = "NickName"
tempString(1, 89) = "OfficeLocation"
tempString(1, 90) = "OrganizationalIDNumber"
tempString(1, 91) = "OtherAddress"
tempString(1, 92) = "OtherAddressCity"
tempString(1, 93) = "OtherAddressCountry"
tempString(1, 94) = "OtherAddressPostalCode"
tempString(1, 95) = "OtherAddressPostOfficeBox"
tempString(1, 96) = "OtherAddressState"
tempString(1, 97) = "OtherAddressStreet"
tempString(1, 98) = "OtherFaxNumber"
tempString(1, 99) = "OtherTelephoneNumber"
tempString(1, 100) = "PagerNumber"
tempString(1, 101) = "PersonalHomePage"
tempString(1, 102) = "PrimaryTelephoneNumber"
tempString(1, 103) = "Profession"
tempString(1, 104) = "ReferredBy"
tempString(1, 105) = "Sensitivity"
tempString(1, 106) = "Size"
tempString(1, 107) = "Spouse"
tempString(1, 108) = "Subject"
tempString(1, 109) = "Suffix"
tempString(1, 110) = "Title"
tempString(1, 111) = "WebPage"
End If
ExportContacts = tempString
End Function
Helper Functions
Visit Utility Functions for IsContact, GetNS, GetOutlookApp, GetItems.
Sample Usage
This sample procedure gets the contacts from my local Contacts folder and pastes the result into Excel, with header row:
Sub GetContactInfo() Dim results() As String ' get contacts results = ExportContacts() ' paste onto worksheet Range(cells(1, 1), cells(UBound(results), UBound(results, 2))).value = results End Sub
The sample workbook includes the code and a userform so you can see how the results would be put into a listbox.
