How to get the email address an email was sent to?

[Rob M]

Is there a way, using VBA or otherwise, to get the email address a email was sent to?

I have multiple email addresses that all arrive in the same account, and sometimes I would like to know which address was used.
(This is particularly relevant where I have been BCC'd, but there are other circumstances also.)

Does anyone know a way to do this?

(Just to make it more interesting, I would particularly like to do this from Excel, so I can list all emails sent to me using a particular address.
Also, I occasionally get duplicate emails where more than one of my addresses is in the distribution list, so I would like to be able to distinguish which one was sent to which address.)

[Crocus Crow]

This should get you started. Paste the following code into a standard module in an Excel workbook VB project:

 'References Microsoft Outlook 11.0 Object Library and Microsoft CDO 1.21 Library
 
Option Explicit 
 
Sub CDO_Get_Email_Details() 
     
    Dim olApp As Outlook.Application 
    Dim olNs As Outlook.Namespace 
    Dim olInbox As Outlook.MAPIFolder 
    Dim olItem As Object 
    Dim olMailItem As Outlook.MailItem 
    Dim ws As Worksheet 
    Dim row As Long 
    Dim i As Integer 
    Dim CDOsession As Session 
    Dim CDOmessage As Message 
    Dim CDOmessageFields As Fields 
    Dim CDOfield As field 
     
    Set ws = Worksheets("Sheet1") 
    With ws 
        .Cells.ClearContents 
        .Activate 
        .Range("A1:F1").Value = Array("Sender", "Received Time", "Subject", "Received by Email Address", "Display To", "Original Display BCC") 
    End With 
     
    row = 2 
     
    Set olApp = New Outlook.Application 
    Set olNs = olApp.GetNamespace("MAPI") 
    Set olInbox = olNs.GetDefaultFolder(olFolderInbox) 
     
     'Set CDOsession = CreateObject("MAPI.Session") 'Late binding
    Set CDOsession = New MAPI.Session 
    CDOsession.Logon "", "", False, False 
     
    For Each olItem In olInbox.Items 
        If olItem.Class = olMail Then 
            Set olMailItem = olItem 
            Set CDOmessage = CDOsession.GetMessage(olMailItem.EntryID, olMailItem.Parent.StoreID) 
            Set CDOmessageFields = CDOmessage.Fields 
             
            If HasCDOfield(CDOmessageFields, CdoPropTags.CdoPR_SENDER_EMAIL_ADDRESS) Then 
                Set CDOfield = CDOmessageFields.Item(CdoPropTags.CdoPR_SENDER_EMAIL_ADDRESS) 
                ws.Cells(row, "A").Value = CDOfield.Value 
            End If 
             
            If HasCDOfield(CDOmessageFields, CdoPropTags.CdoPR_CREATION_TIME) Then 
                Set CDOfield = CDOmessageFields.Item(CdoPropTags.CdoPR_CREATION_TIME) 
                ws.Cells(row, "B").Value = CDOfield.Value 
            End If 
             
            Set CDOfield = CDOmessageFields.Item(CdoPropTags.CdoPR_SUBJECT) 
            ws.Cells(row, "C").Value = CDOfield.Value 
             
            Set CDOfield = CDOmessageFields.Item(CdoPropTags.CdoPR_RECEIVED_BY_EMAIL_ADDRESS) 
            ws.Cells(row, "D").Value = CDOfield.Value 
             
            Set CDOfield = CDOmessageFields.Item(CdoPropTags.CdoPR_DISPLAY_TO) 
            ws.Cells(row, "E").Value = CDOfield.Value 
             
            If HasCDOfield(CDOmessageFields, CdoPropTags.CdoPR_ORIGINAL_DISPLAY_BCC) Then 
                Set CDOfield = CDOmessageFields.Item(CdoPropTags.CdoPR_ORIGINAL_DISPLAY_BCC) 
                ws.Cells(row, "F").Value = CDOfield.Value 
            End If 
             
            row = row + 1 
        End If 
    Next 
End Sub 
 
Private Function HasCDOfield(CDOmessageFields As Fields, CDOproperty As CdoPropTags) As Boolean 
    Dim i As Integer 
     
    HasCDOfield = False 
    i = 1 
    While i <= CDOmessageFields.Count And Not HasCDOfield 
        If CDOmessageFields.Item(i).ID = CDOproperty Then HasCDOfield = True 
        i = i + 1 
    Wend 
     
End Function 

VBA tags courtesy of www.thecodenet.com

The code uses early binding so you need the references noted in the comment at the top of the code. Set these via Tools - References in the VBA project.

The message field containing the recipient email address is the CdoPR_RECEIVED_BY_EMAIL_ADDRESS field (column D in the code). In my tests, this contains the recipient email address whether the sender sent the email to that address as the 'To:' address or the 'Bcc:' address. I've added a few more columns/CDO fields such as Sender and Subject to the Excel output to give more details for each email.

[Rob M]

Thank you for this code - it look likes what I want, but when I tried it I get a compilation error.

I have referenced to Microsoft Outlook 14.0 Object Library and Microsoft CDO for Windows 2000 Library (the only Microsoft CDO in the list, location C:\Windows\SysWOW64\cdosys.dll).

When I try to run the code, it goes to the top line of the function and gives the message "Compile Error: User-defined type not defined". Any ideas?

It does not seem to like the following types: CdoPropTags, Fields, Field or Session

[Crocus Crow]

The code uses CDO 1.21 (CDO.DLL), which you can download from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=3671

[Rob M]

When I try to install this, it says that it cannot be installed unless Microsoft Office Outlook 2007 is also installed.

I have Outlook 2010. Is there anything I can do?

[Crocus Crow]

Try installing the Office Compatibility Pack.

[Rob M]

The Compatability Pack is to allow you to use later versions, not earlier versions, isn't it?

I found on the MS website "CDO 1.2.1 is not supported for use with Outlook 2010. Most of the CDO 1.2.1 functionality has been incorporated into the Outlook 2010 object model", but no clue as to how to do this!

[Crocus Crow]

Quote:   Originally Posted by: Rob M The Compatability Pack is to allow you to use later versions, not earlier versions, isn't it?

Ah yes, I think you are correct.

When I was researching answers to your question 3 weeks ago I came across code on 2 web pages which might work for you. I didn't try them because I have Office 2003 and didn't know you have 2010 at that time. Luckily the 2 pages are still in my browser history:

Outlook VBA Script that gets info on currently selected email using various Property Syntaxes (DASL)

Outlook VBA Script that gets info on currently selected email using Property Tag Syntax

The key to both scripts is the use of Outlook.PropertyAccessor, which is only available in Outlook 2007 and later, so I can't test it. The line in the 2nd piece of code which is relevant to your question is:

report = report & AddToReportIfNotBlank("PR_RECEIVED_BY_EMAIL_ADDRESS", propertyAccessor.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0076" & PT_STRING8)) & vbCrLf 

VBA tags courtesy of www.thecodenet.com

which you could adapt for your purposes.