Beyond Excel: VBA and Database Manipulation

Apr 7 2011   3:41PM GMT

Getting User Names from Outlook Into Excel

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

The company president’s mother died.   The staff wanted to express their sympathy.  Cards were purchased and now we needed to make sure everyone got a chance to sign them.  We needed names on a check list to circulate with the cards so when someone signed, they could check their name and hand the card to someone whose name remained unchecked.  So how do we make a list with everyone’s name and make sure no one is left off?

Everyone in our office has a computer.  Everyone uses Outlook.  All we needed was a quick way to get all user names into Excel to edit, format and print.  This is different from the contact list.  The contact list may, or may not have all users in it and it definitely has far more names than users. 

NOTE! Requires Excel 2003 or later because of ListObject

  1. Open Excel and get to the VBE (Alt-F11)
  2. Use Ctrl-R to bring up the Project Explorer
  3. Double click ThisWorkbook to bring up its code window
  4. Enter this code then run it. 

Sub Network_Users()

     Date   Ini Modification
‘   04/10/11 CWH Initial Programming
‘   04/03/14 CWH Added email addresses

    Dim olA     As Object       ‘Outlook.Application    Start Outlook (OL)
    Dim olNS    As Object       ‘Namespace              OL identifiers context
    Dim olAL    As Object       ‘AddressList            An OL address list
    Dim olAE    As Object       ‘AddressEntry           An Address List entry
    Dim lo      As ListObject   ‘An Excel Table

    On Err GoTo ErrHandler

‘   Create a ListObject/Table in the spreadsheet
    With ActiveSheet
        .Cells.ClearContents                    ‘Clear worksheet completely
        .Cells.ClearFormats                     ‘Clear formats as well
        [A4:B4] = Array(“Names”,”Email”)        ‘Add a column headings
        Set lo = .ListObjects.Add(1, [A4].CurrentRegion, , xlYes)
        lo.Name = “Names”
    End With 

‘   Open Outlook, set context, open “All Users” address list
    Set olA = CreateObject(“Outlook.Application”)
    Set olNS = olA.GetNamespace(“MAPI”)
    Set olAL = olNS.AddressLists(“All Users”) 

'   Add each address entry name to the Excel Table
    For Each olAE In olAL.AddressEntries
        With lo.ListRows.Add
            .Range(1) = olAE.Name
            .Range(2) = olAE.GetExchangeUser.PrimarySmtpAddress
        End With

‘   Format Results
    lo.HeaderRowRange.Style = ActiveWorkbook.Styles(“Heading 1”)
    ActiveWindow.FreezePanes = True

‘   Do this ONLY if you want to close Outlook

ErrHandler:    If Err.Number <> 0 Then MsgBox _
         “Network_Users - Error#" & Err.Number & vbCrLf & _
         Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
     On Error GoTo 0   

End Sub

7  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • jomili

    I'm getting an error message on the line:

    Set lo = .ListObjects("Names")


    Error is "Subscript out of Range"

    10 pointsBadges:
  • Outlook 2007 VBA 全域通訊清單(GAL) Global Address List, dump to file | xiaolabaDIY
    [...] [...]
    0 pointsBadges:
  • Craig Hatmaker
    Hi jomili,Sorry for not responding sooner.  I missed the notification of your comment.  Hopefully you have sorted things out, but if not...The “Subscript out of Range” indicates there is not ListObject with the name of "Names".  So either the line above that errored out, or the name above was not typed exactly - in particular, the name assignment may have been misspelled. In any case, your question prompted me to change the code a bit to prevent such possibilities.  Thanks for your comment, and again, sorry for the late response.
    1,860 pointsBadges:
  • kps9727
    This is great, I'd really like to modify it to include the user's email address in the second column, but sadly my skills are weak.  How do I do this?
    0 pointsBadges:
  • Craig Hatmaker
    Great question. I updated the program to include email addresses. Find this line: .Range(2) = olAE.GetExchangeUser.PrimarySmtpAddress
    1,860 pointsBadges:
    My issue with the code was due to the funky quotes and tick marks.

    Original:[A4:B4] = Array(“Names”,”Email”)
    Becomes" [A4:B4] = Array("Names","Email")

    And original tick
    Original:      Date   Ini Modification
    Becomes: '     Date   Ini Modification
    0 pointsBadges:
  • Craig Hatmaker
    Yep - putting code and getting it back out of these blogs is a chore. For this reason I have started moving code to PDFs and Zip files and I'm changing my blog approach to: "Here is an idea. If you want to know more read this PDF which includes links to code". I hope readers will find the approach more useful.
    1,860 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: