Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Set up an Excel Spreadsheet from Outlook Contacts - vs. 2010

Posted on 2013-12-31
Medium Priority
Last Modified: 2014-01-08
Within my Outlook (2010) there are several Categories that have the necessary contact information that I need would like to transfer to a Workbook, with each category having a different tab.

Attached is a workbook that provides a model on how the contact information is listed, preferably.  What is the best way to have the code loop through the contacts and create an Excel category list on different worksheet tabs?

For one Category, there is actually a distribution list that may be easier to use for one of the categories, and is listed in Col A.   However, I'm not sure if the distribution list has all of the data which is needed, or just a more complete e-mail list than what I have manually put together.
Question by:Cook09
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 12

Expert Comment

by:Harry Lee
ID: 39749295

Instead of trying to get the info using loop to extract data from Outlook, I think a more simple and better approach is to export the Outlook contacts to a CSV file, then from Excel extract the data from the CSV by deleting unwanted columns and rearrange the columns order using vba or manually.

Author Comment

ID: 39752005

Well part of the problem is that some of the fields that are in my Contacts are not in the Export Field.  

Additionally, the categories are all over the place, so it would take time to filter each, manually create a tab, and then copy/paste.  I was looking for something a little more automatic, that would also capture the information needed which is not in the export.
LVL 12

Expert Comment

by:Harry Lee
ID: 39752186

I just did an contacts export from my Outlook 2013, I know you are using 2010, I find that all the field you are looking for is in the exported CSV file.

Which field are you looking for, specifically, that is not exported?

Maybe I can help you to develop a VBA macro in Excel to extract the data from Outlook without going to Outlook to click export to get the job done.
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


Author Comment

ID: 39754996

Well, one thing I found while trying different solutions is to highlight the Customize Heading, drag the mouse about 1/4" and it highlights the entire group. Then all that is needed is to copy and paste it into Excel, which also brings the Headings during the Paste.   I think this is even faster than trying to use code.

Maybe you could assist me with something else I was trying to accomplish, and that is to extract the names within a Distribution List.  Is this even possible?
LVL 12

Accepted Solution

Harry Lee earned 2000 total points
ID: 39755315

You can use the following code to get the list of members of all Outlook Distribution Lists.

Please remember to enable Microsoft Outlook Object Library in Excel VBA Editor.
     Go to VBA Editor
     Tools Menu
     From the list, find Microsoft Outlook xx.0 Object Library (xx.0 is the library version)
     Check the check box then press OK to go back to VBA Editor.

Sub DistListMembers()
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNamespace = objOutlook.GetNamespace("MAPI")
    Dim addrLists As AddressLists
    Set addrLists = objNamespace.Session.AddressLists
    Dim addrList As AddressList
    For Each addrList In addrLists
        If addrList.Name = "All Groups" Then
            Set myAddrList = addrList
            Exit For
        End If
    ActiveSheet.Range("A1") = "Dist List"
    ActiveSheet.Range("B1") = "Memberber Name"
    rw = 2
    For Each Entry In myAddrList.AddressEntries
        If Entry.DisplayType = olDistList Then
            For Each Member In Entry.Members
                        ActiveSheet.Range("A" & rw) = Entry.Name
                        ActiveSheet.Range("B" & rw) = Member.Name
                        rw = rw + 1
        End If
End Sub

Open in new window


Author Comment

ID: 39762831
I did try the code that you provided, and received notification that it was trying to access the Exchange Server.   However, it did error out on....

For Each Member In Entry.Members

Open in new window

I don't know if there is another command that would work, or is it the security settings?

I did find this code, that will import my Contacts, but the e-mail address is in Exchange Format and not really usable.  I did see that a "DisplayName," could provide the e-mail address, but the syntax is different, and still trying to figure it out.

Sub Import_Contacts()

    'Outlook objects. From Local Address Book
    Dim olApp As Outlook.Application
    Dim olNamespace As Outlook.Namespace
    Dim olFolder As Outlook.MAPIFolder
    Dim olConItems As Outlook.Items
    Dim olItem As Object
    'Excel objects.
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    'Location in the imported contact list.
    Dim lnContactCount As Long
    Dim strDummy As String
    'Turn off screen updating.
    Application.ScreenUpdating = False
    'Initialize the Excel objects.
'    Set wbBook = ThisWorkbook
    Set wbBook = ActiveWorkbook
    Set wsSheet = wbBook.Worksheets(1)
    'Format the target worksheet.
    With wsSheet
        .Cells(1, 1).Value = "City"
        .Cells(1, 2).Value = "State"
        .Cells(1, 3).Value = "Zip Code"
        .Cells(1, 4).Value = "MAC Address"
        .Cells(1, 5).Value = "Name"
        .Cells(1, 6).Value = "Job Title"
        .Cells(1, 7).Value = "E-mail"
        .Cells(1, 8).Value = "Account"
        .Cells(1, 9).Value = "Tel No."
        .Cells(1, 10).Value = "Street"
        .Cells(1, 11).Value = "Email"
        With .Range("A1:K1")
            .Font.Bold = True
            .Font.ColorIndex = 10
            .Font.Size = 11
        End With
    End With
    'Initalize the Outlook variables with the MAPI namespace and the default Outlook folder of the current user.
    Set olApp = New Outlook.Application
    Set olNamespace = olApp.GetNamespace("MAPI")
    Set olFolder = olNamespace.GetDefaultFolder(10)
    Set olConItems = olFolder.Items
    'Row number to place the new information on; starts at 2 to avoid overwriting the header
    lnContactCount = 2
    'For each contact: if it is a business contact, write out the business info in the Excel worksheet;
    'otherwise, write out the personal info.
    For Each olItem In olConItems
        If TypeName(olItem) = "ContactItem" Then
            With olItem
                If InStr(olItem.CompanyName, strDummy) > 0 Then
                    Cells(lnContactCount, 1).Value = .BusinessAddressCity
                    Cells(lnContactCount, 2).Value = .BusinessAddressState
                    Cells(lnContactCount, 3).Value = .BusinessAddressPostalCode
                    Cells(lnContactCount, 4).Value = .OfficeLocation
                    Cells(lnContactCount, 5).Value = .FullName
                    Cells(lnContactCount, 6).Value = .JobTitle
                    Cells(lnContactCount, 7).Value = .IMAddress
                    Cells(lnContactCount, 8).Value = .Account
                    Cells(lnContactCount, 9).Value = .BusinessTelephoneNumber
                    Cells(lnContactCount, 10).Value = .BusinessAddressStreet
                    Cells(lnContactCount, 11).Value = .DisplayName --- Not Correct Format
                    Cells(lnContactCount, 1) = .FullName
                    Cells(lnContactCount, 2) = .HomeAddressStreet
                    Cells(lnContactCount, 3) = .HomeAddressPostalCode
                    Cells(lnContactCount, 4) = .HomeAddressCity
                    Cells(lnContactCount, 5) = .FullName
                    Cells(lnContactCount, 6) = .IMAddress
                End If
                On Error Resume Next      'The 11 was a 6 - Not sure what difference this makes
                wsSheet.Hyperlinks.Add Anchor:=Cells(lnContactCount, 11), _
                                       Address:="mailto:" & Cells(lnContactCount, 11).Value, _
                                       TextToDisplay:=Cells(lnContactCount, 11).Value
            End With
            lnContactCount = lnContactCount + 1
        End If
    Next olItem
    'Null out the variables.
    Set olItem = Nothing
    Set olConItems = Nothing
    Set olFolder = Nothing
    Set olNamespace = Nothing
    Set olApp = Nothing
    'Sort the rows alphabetically using the CompanyName or FullName as appropriate, and then autofit.
    With wsSheet
        .Range("A2", Cells(2, 11).End(xlDown)).Sort key1:=Range("A1"), order1:=xlAscending
    End With
    'Turn screen updating back on.
    Application.ScreenUpdating = True
    MsgBox "The list has successfully been created!", vbInformation
End Sub

Open in new window

Do you have any suggestions?
LVL 12

Expert Comment

by:Harry Lee
ID: 39763935
I don't know why you have problem with
For Each Member In Entry.Members

Open in new window

Maybe it has something to do with which MS Outlook Reference Library you are using.


In the above code you have attached, the line 75 should be

                    Cells(lnContactCount, 11).Value = .Email1DisplayName

Open in new window


Author Closing Comment

ID: 39766346
Made the mistake of not adding the correct name to the Distribution List.  When corrected, it ran fine.
LVL 12

Expert Comment

by:Harry Lee
ID: 39766759
I'm glad it works for you.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article will help to fix the below error for MS Exchange server 2010 I. Out Of office not working II. Certificate error "name on the security certificate is invalid or does not match the name of the site" III. Make Internal URLs and External…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
CodeTwo Sync for iCloud ( automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question