Outlook 2013 VBA Getting required data from Global Address List

Question

I am using Outlook VBA codes to find a specific Email address by the unique job title in the Global Address List. I have the code below, but I am not sure how I can rope the identifying of specific Email address in. I am using this as a Function so that I can call it in the Subroutine.

I keep getting the error "Object variable or With block variable not set", but I don't know how I can edit in the code to remove the error. I get the error at this line: "Set olUser = olAddressEntry.GetExchangeUser".

Function GALEmail(specificTitle As String) As String

Dim olNs As Outlook.NameSpace
Dim olGAL As Outlook.AddressEntries
Dim olAddressEntry As Object
Dim olUser As Object
Dim sEmail As String
Dim i As Long
Dim GetCurrentItem As Object

Set olNs = Application.GetNamespace("MAPI")
Set olGAL = olNs.AddressLists("Global Address List").AddressEntries
Set GetCurrentItem = Application.ActiveInspector.currentItem
Set olUser = Nothing

'On Error Resume Next
With GetCurrentItem
    For i = 1 To olGAL.Count
        Set olAddressEntry = olGAL.Item(i)
        Set olUser = olAddressEntry.GetExchangeUser
        MsgBox olUser
        sEmail = olGAL.Item(i).Title

        If sEmail = specificTitle Then
            Set olUser = olAddressEntry.GetExchangeUser
            Debug.Print olUser.Email1Address
        End If
    Next i
End With

End Function

Any help would be greatly appreciated!!

Jing Yi · Accepted Answer · 2015-12-28T16:47:12.220

0

I have figured how to get the Email Address with the job title as shown below:

Function GALEmail(specificTitle As String) As String

Dim olNs As Outlook.NameSpace
Dim olGAL As Object
Dim olAddressEntry As Object
Dim olUser As Object
Dim sEmail As String
Dim i As Long
Dim GetCurrentItem As Object

Set olNs = Application.GetNamespace("MAPI")
Set olGAL = olNs.AddressLists("Global Address List").AddressEntries
Set GetCurrentItem = Application.ActiveInspector.currentItem

'On Error Resume Next
With GetCurrentItem
    For i = 1 To olGAL.Count
        Set olAddressEntry = olGAL.Item(i)

        If olAddressEntry.AddressEntryUserType = 0 Then
        Set olUser = olAddressEntry.GetExchangeUser
        sEmail = olUser.JobTitle

            If sEmail = specificTitle Then
                GALEmail = olUser.PrimarySmtpAddress
            End If
        End If
    Next i
End With

End Function

edited Dec 28 '15 at 16:47

answered Dec 28 '15 at 08:31

Jing Yi

203
4
15

Firstly, your function does not return a value. Secondly, looping through all items in GAL is not a good idea, especially if you are using an online profile. – Dmitry Streblechenko Dec 28 '15 at 16:21
Hi Dmitry, I have edited the codes to return a value now. How would you recommend I do, instead of looping through the items? – Jing Yi Dec 28 '15 at 16:48
There is not much you can do in OOM alone - you'd need to use Extended MAPI (C++ or Delphi only) or Redemption (any language) to use the MAPITable object of the GAL container. – Dmitry Streblechenko Dec 28 '15 at 17:28
I see. However, I am only using VBA for this function. But I can try to enhance this function in future. Nevertheless, thanks for your recommendations! – Jing Yi Dec 29 '15 at 00:38
Just keep in mind that your code will work for a hundred or so items in GAL. With more address entries (I have seen corporate GAL containers with > 100,000 entries), your code will run extremely slow if at all. – Dmitry Streblechenko Dec 29 '15 at 04:55

Outlook 2013 VBA Getting required data from Global Address List

1 Answers1