none
Extract data from the Global Address book with VBA RRS feed

  • Question

  • I’ve been trying to figure out some VBA code that would look up a user in the Outlook Global Address book using their e-mail alias and return some of the information that can be seen the Outlook Properties dialog box.

    The ultimate goal is to populate a spreadsheet with this data.  While I am well versed at using VBA with Excel, I am a novice with the Outlook object model.  This seems like it should be easy, but six hours of searching has not turned up many useful answers.  Can’t seem to figure out how to look someone up in the address book using their alias and then accessing the outlook properties.

    Sample code would be appreciated.

    Tuesday, November 27, 2012 7:52 PM

Answers

  • I don't have access to a GAL with custom attributes, but it looks like you can use AddressEntry.PropertyAccessor to access these fields by their index number, not name (e.g. PR_EMS_AB_EXTENSION_ATTRIBUTE_1, PR_EMS_AB_EXTENSION_ATTRIBUTE_2, etc.):

    Programmatically fetching Custom Attributes from Distribution List:
    http://social.msdn.microsoft.com/Forums/en-SG/outlookdev/thread/ea7fa078-ae66-40eb-befc-dcdb71a4b5b1


    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    Friday, November 30, 2012 5:58 PM
    Moderator
  • If you read the thread via the link I provided you would have seen that the namespace is http://schemas.microsoft.com/mapi/proptag/0x802D001E.

    Also see:

    http://social.technet.microsoft.com/Forums/sr-Latn-CS/exchangesvrdevelopmentlegacy/thread/57bc7eb2-f0fb-4e5e-930c-a4a72969e1c3

    INFO: Accessing Custom Attributes of Recipients:
    http://support.microsoft.com/kb/178553/en-us


    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    Friday, November 30, 2012 8:12 PM
    Moderator
  • Thanks for your patience on this. I did read your post and follow the link, but it turns out that using the extended attribute property identifiers for a recipient on an e-mail object doesn't work so well.

    Perhaps it’s just my inexperience with the Outlook object model, but this is what I find so frustrating about the Microsoft documentation:   Absolutely nowhere (that I have found) is there a single reference that puts all of the information in one place and shows you how to use it.

    The good news is, that after a good night’s sleep, I found and fixed the obvious error and finally got most everything working.  The exception is for extension attributes 1 and 2.  For some reason they return blanks even in cases where they are populated in the address entry (I wonder if the property identifiers have changed in Outlook 2010?).  I had to include an error trap because it seems that if an extended attribute has never been populated, it pops an error indicating that the property  “is unknown or cannot be found.”

    Just in case anyone else with the same problem runs across this thread, here’s the final code.  It's probably not the most elegant code, but it worked for me.

    Sub GetOutlookInfo()


    Dim I As Integer
    Dim ToAddr As String
    Dim ErrMsg As String
    Dim CRLF As String
    Dim DistOption As Integer
    Dim ClassID As Long
    Dim ManagerVerified As Boolean
    Dim ActivePersonVerified As Boolean
    Dim ol As Outlook.Application
    Dim DummyEMail As MailItem
    Dim myInspector As Inspector
    Dim ActivePersonRecipient As Recipient
    Dim oAE As Outlook.AddressEntry
    Dim oExUser As Outlook.ExchangeUser
    Dim oPA As Outlook.PropertyAccessor

    Dim AliasRange As Range
    Dim RowsInRange As Integer

    CRLF = Chr(13)
    ErrMsg = ""
    'Instantiate Outlook
    Set ol = CreateObject("Outlook.Application")
    'E-mail aliases are in a named range "aliasrange"
    'Assign the named range to a range object
    Set AliasRange = Range("aliasrange")
    'Create a dummy e-mail to add aliases to
    Set DummyEMail = ol.CreateItem(olMailItem)
    RowsInRange = AliasRange.Rows.Count

    'Loop through the aliases to retreive the Exchange data
    For I = 1 To RowsInRange
            'Assign the current alias to a variable ToAddr
            ToAddr = AliasRange.Cells(I, 1)
            'Use the alias to create a recipient object and add it to the dummy e-mail
            Set ActivePersonRecipient = DummyEMail.Recipients.Add(ToAddr)
            ActivePersonRecipient.Type = olTo
            'Resolve the recipient to ensure it is valid
            ActivePersonVerified = ActivePersonRecipient.Resolve
            'If valid, use the AddressEntry property of the recipient to return an AddressEntry object
            If ActivePersonVerified Then
                Set oAE = ActivePersonRecipient.AddressEntry
                'Use the GetExchangeUser method ot the AddressEntry object to retrieve the ExchangeUser object for the recipient
                Set oExUser = oAE.GetExchangeUser
                'Write the properties of the ExchangeUser object to adjacent columns on the worksheet
                AliasRange.Cells(I, 1).Offset(0, 1).Value = oExUser.Name
                AliasRange.Cells(I, 1).Offset(0, 2).Value = oExUser.Department
                AliasRange.Cells(I, 1).Offset(0, 3).Value = oExUser.JobTitle
                AliasRange.Cells(I, 1).Offset(0, 4).Value = oExUser.OfficeLocation
                AliasRange.Cells(I, 1).Offset(0, 5).Value = oExUser.City
                AliasRange.Cells(I, 1).Offset(0, 6).Value = oExUser.StateOrProvince
                AliasRange.Cells(I, 1).Offset(0, 7).Value = oExUser.StreetAddress
               
                'Get Extended properties
                'Get the property accessor from the exchange user object
                Set oPA = oExUser.PropertyAccessor
                'Property identifiers from article at:http://support.microsoft.com/kb/178553/en-us?wa=wsignin1.0
               
                On Error GoTo PropertyFail
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x802E001E" 'Extended property 1
                ExtendedProperty = "Extended property 1"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 17).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x802D001E" 'Extended property 2
                ExtendedProperty = "Extended property 2"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 8).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x802F001E"  'Extended property 3
                ExtendedProperty = "Extended property 3"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 9).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8030001E" 'Extended property 4
                ExtendedProperty = "Extended property 4"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 10).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8031001E" 'Extended property 5
                ExtendedProperty = "Extended property 5"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 11).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8032001E" 'Extended property 6
                ExtendedProperty = "Extended property 6"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 12).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8033001E" 'Extended property 7
                ExtendedProperty = "Extended property 7"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 13).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8034001E" 'Extended property 8
                ExtendedProperty = "Extended property 8"
                oPA.GetProperty (PropertyIdentifier)
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 14).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8035001E" 'Extended property 9
                ExtendedProperty = "Extended property 9"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 15).Value = ExtendedPropertyValue
               
           
                Set oPA = Nothing
                Set oAE = Nothing
                Set oExUser = Nothing
              Else
                AliasRange.Cells(I, 1).Offset(0, 1).Value = "Alias not resolved"
            End If
            On Error GoTo 0
            'Remove the recipient from the e-mail
            ActivePersonRecipient.Delete
            Set ActivePersonRecipient = Nothing
    Next I
       
    ExitOutlookEmail:
        Set DummyEMail = Nothing
        Set ol = Nothing
        Set oPA = Nothing
        Set oAE = Nothing
        Set oExUser = Nothing
        Exit Sub
       
    PropertyFail:
        Debug.Print Err.Number, Err.Description
        Debug.Print ExtendedProperty, PropertyIdentifier
        'Set the value of the ExtendedPropertyValue variable to the ExtendedProperty variable set just before the GetProperty method is called
        'This results in the phrase "Extended property n" being written to the cell where the property value was supposed to be written
        ExtendedPropertyValue = ExtendedProperty
        Resume Next
    End Sub

    Monday, December 3, 2012 6:40 PM

All replies

  • You need an ExchangeUser object to read those details from the GAL - see here:

    GetExchangeUser Method [Outlook 2007 Developer Reference]:
    http://msdn.microsoft.com/en-us/library/office/bb175075(v=office.12).aspx


    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    Tuesday, November 27, 2012 8:44 PM
    Moderator
  • Eric,

    Thanks, but that seems to loop through all of the entries in in the Global Address book.  It seems like there should be a more efficient way to locate the 10-20 users I need info on without going through all 30,000.

    Wednesday, November 28, 2012 12:15 PM
  • You don't have to loop through everything - the key is to just get an AddressEntry object.  With just a name you can resolve each of your 10-20 users by using NameSpace.CreateRecient to get a Recipient object, then use Recipient.Resolve and check for Recipient.Resolved = True.  If so, use Recipient.AddressEntry as required for the GetExchangerUser method.

    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    Wednesday, November 28, 2012 3:17 PM
    Moderator
  • Thanks for the info.

    I was able to figure out how to get just about everything I need and developed the code below.  It successfully extracts the data as long as it is a native property of the ExchangeUser object.  Unfortunately there are two custom properties that have been added for Division and Mailstop (cleverly named customAttribute2 and customAttribute3).  I haven’t been able to figure out how to read these custom properties.

    Any Ideas?

    Sub GetOutlookInfo()

    Dim I As Integer

    Dim ToAddr As String

    Dim ActivePersonVerified As Boolean

    Dim ol As Outlook.Application

    Dim DummyEMail As MailItem

    Dim ActivePersonRecipient As Recipient

    Dim oAE As Outlook.AddressEntry

    Dim oExUser As Outlook.ExchangeUser

    Dim oPA As Outlook.propertyAccessor

    Dim AliasRange As Range

    Dim RowsInRange As Integer

    'Instantiate Outlook

    Set ol = CreateObject("Outlook.Application")

    'E-mail aliases are in a named range "aliasrange"

    'Assign the named range to a range object

    Set AliasRange = Range("aliasrange")

    'Create a dummy e-mail to add aliases to

    Set DummyEMail = ol.CreateItem(olMailItem)

    RowsInRange = AliasRange.Rows.Count

    'Loop through the aliases to retrieve the Exchange data

    For I = 1 To RowsInRange

            'Assign the current alias to a variable ToAddr

            ToAddr = AliasRange.Cells(I, 1)

            'Use the alias to create a recipient object and add it to the dummy e-mail

            Set ActivePersonRecipient = DummyEMail.Recipients.Add(ToAddr)

            ActivePersonRecipient.Type = olTo

            'Resolve the recipient to ensure it is valid

            ActivePersonVerified = ActivePersonRecipient.Resolve

            'If valid, use the  AddressEntry property of the recipient to return an AddressEntry object

            If ActivePersonVerified Then

                Set oAE = ActivePersonRecipient.AddressEntry

                'Use the GetExchangeUser method of the AddressEntry object to retrieve the ExchangeUser object for the recipient.

                Set oExUser = oAE.GetExchangeUser

                'Write the properties of the  ExchangeUser object to adjacent columns on the worksheet.

                AliasRange.Cells(I, 1).Offset(0, 1).Value = oExUser.Name

                AliasRange.Cells(I, 1).Offset(0, 2).Value = oExUser.Department

                AliasRange.Cells(I, 1).Offset(0, 3).Value = oExUser.JobTitle

                AliasRange.Cells(I, 1).Offset(0, 4).Value = oExUser.OfficeLocation

                AliasRange.Cells(I, 1).Offset(0, 5).Value = oExUser.City

                AliasRange.Cells(I, 1).Offset(0, 6).Value = oExUser.StateOrProvince

                AliasRange.Cells(I, 1).Offset(0, 7).Value = oExUser.StreetAddress

            End If

            'Remove the recipient from the e-mail

            ActivePersonRecipient.Delete

    Next I

    ExitOutlookEmail:

        Set DummyEMail = Nothing

        Set ol = Nothing

    End Sub

    Friday, November 30, 2012 5:51 PM
  • I don't have access to a GAL with custom attributes, but it looks like you can use AddressEntry.PropertyAccessor to access these fields by their index number, not name (e.g. PR_EMS_AB_EXTENSION_ATTRIBUTE_1, PR_EMS_AB_EXTENSION_ATTRIBUTE_2, etc.):

    Programmatically fetching Custom Attributes from Distribution List:
    http://social.msdn.microsoft.com/Forums/en-SG/outlookdev/thread/ea7fa078-ae66-40eb-befc-dcdb71a4b5b1


    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    Friday, November 30, 2012 5:58 PM
    Moderator
  • I figured from my research that the the GetProperty method of the propertyAccessor  of the ExchangeUser object might get to the custom properties.   But, as usual, the Microsoft documentation is obtuse and fragmented so it’s difficult to figure it all out (it appears that the GetProperty method requires the schema name for the ExchangeUser object as an argument, but I haven’t been able to find a reference that gives me the schema name).
    Friday, November 30, 2012 6:18 PM
  • If you read the thread via the link I provided you would have seen that the namespace is http://schemas.microsoft.com/mapi/proptag/0x802D001E.

    Also see:

    http://social.technet.microsoft.com/Forums/sr-Latn-CS/exchangesvrdevelopmentlegacy/thread/57bc7eb2-f0fb-4e5e-930c-a4a72969e1c3

    INFO: Accessing Custom Attributes of Recipients:
    http://support.microsoft.com/kb/178553/en-us


    Eric Legault MVP (Outlook)
    About me...
    Try Outlook Appins - affordable and easy to use!

    Friday, November 30, 2012 8:12 PM
    Moderator
  • Thanks for your patience on this. I did read your post and follow the link, but it turns out that using the extended attribute property identifiers for a recipient on an e-mail object doesn't work so well.

    Perhaps it’s just my inexperience with the Outlook object model, but this is what I find so frustrating about the Microsoft documentation:   Absolutely nowhere (that I have found) is there a single reference that puts all of the information in one place and shows you how to use it.

    The good news is, that after a good night’s sleep, I found and fixed the obvious error and finally got most everything working.  The exception is for extension attributes 1 and 2.  For some reason they return blanks even in cases where they are populated in the address entry (I wonder if the property identifiers have changed in Outlook 2010?).  I had to include an error trap because it seems that if an extended attribute has never been populated, it pops an error indicating that the property  “is unknown or cannot be found.”

    Just in case anyone else with the same problem runs across this thread, here’s the final code.  It's probably not the most elegant code, but it worked for me.

    Sub GetOutlookInfo()


    Dim I As Integer
    Dim ToAddr As String
    Dim ErrMsg As String
    Dim CRLF As String
    Dim DistOption As Integer
    Dim ClassID As Long
    Dim ManagerVerified As Boolean
    Dim ActivePersonVerified As Boolean
    Dim ol As Outlook.Application
    Dim DummyEMail As MailItem
    Dim myInspector As Inspector
    Dim ActivePersonRecipient As Recipient
    Dim oAE As Outlook.AddressEntry
    Dim oExUser As Outlook.ExchangeUser
    Dim oPA As Outlook.PropertyAccessor

    Dim AliasRange As Range
    Dim RowsInRange As Integer

    CRLF = Chr(13)
    ErrMsg = ""
    'Instantiate Outlook
    Set ol = CreateObject("Outlook.Application")
    'E-mail aliases are in a named range "aliasrange"
    'Assign the named range to a range object
    Set AliasRange = Range("aliasrange")
    'Create a dummy e-mail to add aliases to
    Set DummyEMail = ol.CreateItem(olMailItem)
    RowsInRange = AliasRange.Rows.Count

    'Loop through the aliases to retreive the Exchange data
    For I = 1 To RowsInRange
            'Assign the current alias to a variable ToAddr
            ToAddr = AliasRange.Cells(I, 1)
            'Use the alias to create a recipient object and add it to the dummy e-mail
            Set ActivePersonRecipient = DummyEMail.Recipients.Add(ToAddr)
            ActivePersonRecipient.Type = olTo
            'Resolve the recipient to ensure it is valid
            ActivePersonVerified = ActivePersonRecipient.Resolve
            'If valid, use the AddressEntry property of the recipient to return an AddressEntry object
            If ActivePersonVerified Then
                Set oAE = ActivePersonRecipient.AddressEntry
                'Use the GetExchangeUser method ot the AddressEntry object to retrieve the ExchangeUser object for the recipient
                Set oExUser = oAE.GetExchangeUser
                'Write the properties of the ExchangeUser object to adjacent columns on the worksheet
                AliasRange.Cells(I, 1).Offset(0, 1).Value = oExUser.Name
                AliasRange.Cells(I, 1).Offset(0, 2).Value = oExUser.Department
                AliasRange.Cells(I, 1).Offset(0, 3).Value = oExUser.JobTitle
                AliasRange.Cells(I, 1).Offset(0, 4).Value = oExUser.OfficeLocation
                AliasRange.Cells(I, 1).Offset(0, 5).Value = oExUser.City
                AliasRange.Cells(I, 1).Offset(0, 6).Value = oExUser.StateOrProvince
                AliasRange.Cells(I, 1).Offset(0, 7).Value = oExUser.StreetAddress
               
                'Get Extended properties
                'Get the property accessor from the exchange user object
                Set oPA = oExUser.PropertyAccessor
                'Property identifiers from article at:http://support.microsoft.com/kb/178553/en-us?wa=wsignin1.0
               
                On Error GoTo PropertyFail
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x802E001E" 'Extended property 1
                ExtendedProperty = "Extended property 1"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 17).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x802D001E" 'Extended property 2
                ExtendedProperty = "Extended property 2"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 8).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x802F001E"  'Extended property 3
                ExtendedProperty = "Extended property 3"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 9).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8030001E" 'Extended property 4
                ExtendedProperty = "Extended property 4"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 10).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8031001E" 'Extended property 5
                ExtendedProperty = "Extended property 5"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 11).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8032001E" 'Extended property 6
                ExtendedProperty = "Extended property 6"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 12).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8033001E" 'Extended property 7
                ExtendedProperty = "Extended property 7"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 13).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8034001E" 'Extended property 8
                ExtendedProperty = "Extended property 8"
                oPA.GetProperty (PropertyIdentifier)
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 14).Value = ExtendedPropertyValue
               
                PropertyIdentifier = "http://schemas.microsoft.com/mapi/proptag/0x8035001E" 'Extended property 9
                ExtendedProperty = "Extended property 9"
                ExtendedPropertyValue = oPA.GetProperty(PropertyIdentifier)
                AliasRange.Cells(I, 1).Offset(0, 15).Value = ExtendedPropertyValue
               
           
                Set oPA = Nothing
                Set oAE = Nothing
                Set oExUser = Nothing
              Else
                AliasRange.Cells(I, 1).Offset(0, 1).Value = "Alias not resolved"
            End If
            On Error GoTo 0
            'Remove the recipient from the e-mail
            ActivePersonRecipient.Delete
            Set ActivePersonRecipient = Nothing
    Next I
       
    ExitOutlookEmail:
        Set DummyEMail = Nothing
        Set ol = Nothing
        Set oPA = Nothing
        Set oAE = Nothing
        Set oExUser = Nothing
        Exit Sub
       
    PropertyFail:
        Debug.Print Err.Number, Err.Description
        Debug.Print ExtendedProperty, PropertyIdentifier
        'Set the value of the ExtendedPropertyValue variable to the ExtendedProperty variable set just before the GetProperty method is called
        'This results in the phrase "Extended property n" being written to the cell where the property value was supposed to be written
        ExtendedPropertyValue = ExtendedProperty
        Resume Next
    End Sub

    Monday, December 3, 2012 6:40 PM