CODEWITHSUNDEEP
vbams-access
HotTomales
HotTomales

Reputation: 564

Query Outlook Global Address List From Access VBA

I am writing some Access VBA code to get a count of how many times a specific email address has been emailed. The issue that I am running into is that the first time the email is sent out, the email leaves our Exchange sever as 

[email protected]

But once the person replies to that email, then all subsequent messages are displayed as

'lastname, firstname'

I use the below VBA code to search for the [email protected] example, but how can I use access vba to get the name from the global address list?

Function Test()

Dim searchEmail As String: searchEmail = "'[email protected]'"
Dim olApp As Outlook.Application
Dim olNs As NameSpace
Dim Fldr As MAPIFolder
Dim olReply As Outlook.MailItem
Dim msg As Object
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderSentMail)

For Each msg In Fldr.Items
    If TypeName(msg) = "MailItem" Then
        If msg.To = searchEmail Then
            'now we start counting
        End If
    End If
Next msg

End Function

Upvotes: 1

Views: 699

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

Similar to the answer I posted here, instead of checking the To property of the MailItem object (which, per the linked documentation, contains the display names only), query the contents of the Recipients collection and, for each Recipient object, test the value held by the Address property against your searchEmail variable.

The Address property will consistently contain the email address of the recipient, never a display name.

That is, instead of:

For Each msg In Fldr.Items
    If TypeName(msg) = "MailItem" Then
        If msg.To = searchEmail Then
            'now we start counting
        End If
    End If
Next msg

You might use something like:

For Each msg In Fldr.Items
    If TypeName(msg) = "MailItem" Then
        For Each rcp In msg.Recipients
            If rcp.Address = searchEmail Then
                'now we start counting
            End If
        Next rcp
    End If
Next msg

Upvotes: 1

Related Questions