Outlook & VBA : Export contact information to Excel

  • Article

Please find the following script that retrieves the FullName and BusinessTelephoneNumber properties for all the contacts in an Outlook address book:

 

    1:  On Error Resume Next

    2:   

    3:  Const olFolderContacts = 10

    4:   

    5:  Set objOutlook = CreateObject("Outlook.Application")

    6:  Set objNamespace = objOutlook.GetNamespace("MAPI")

    7:   

    8:  Set colContacts = objNamespace.GetDefaultFolder(olFolderContacts).Items

    9:   

   10:  Set objExcel = CreateObject("Excel.Application")

   11:  objExcel.Visible = True

   12:  Set objWorkbook = objExcel.Workbooks.Add()

   13:  Set objWorksheet = objWorkbook.Worksheets(1)

   14:   

   15:  objExcel.Cells(1, 1) = "Name"

   16:  objExcel.Cells(1, 2) = "Business Phone"

   17:   

   18:  i = 2

   19:   

   20:  For Each objContact In colContacts

   21:      objExcel.Cells(i, 1).Value = objContact.FullName

   22:      objExcel.Cells(i, 2).Value = objContact.BusinessTelephoneNumber

   23:      i = i + 1

   24:  Next

   25:   

   26:  Set objRange = objWorksheet.UsedRange

   27:  objRange.EntireColumn.Autofit