Different ways of retrieving the sender of an email in Outlook VBA

Hello Experts

Let me say say straight away that although I am good at Access/Excel VBA I am pretty new to Outlook VBA.

I need to be able to retrieve the email address of the sender of an arbitrary email, and have come across two ways of doing it. One is obvious, the other is written by Microsoft (slightly adapted). They both return the same result at least some of the time. I want a reliable method, and suspect MS knows somwthing I don't...

My simple and obvious approach is:

Function SenderEmail_old(oM As Outlook.MailItem)
   SenderEmail_old = oM.SenderEmailAddress
End Function

However, I admit to having problems with the above, which sometimes returns what looks like an obscure X.400 type of string, or part of one. (Maybe it had something to do with the email being forwarded.)

Microsoft's piece of contorted obfuscation is (adapted from the Otlook 2013 help):

Function SenderEmail(oM As Outlook.MailItem) As String
' return sender of email object oM
    Dim oPA As Outlook.PropertyAccessor
    Dim oContact As Outlook.ContactItem
    Dim oSender As Outlook.AddressEntry
    Dim SenderID As String
    'Create an instance of PropertyAccessor
    Set oPA = oM.PropertyAccessor

    'Obtain PidTagSenderEntryId and convert to string
    SenderID = oPA.BinaryToString _

    'Obtain AddressEntry Object of the sender
    Set oSender = Application.Session.GetAddressEntryFromID(SenderID)

    SenderEmail = oSender.Address
End Function

Can anyone inform what is so great about the second approach?? Is it more reliable? Consistent? How does it work?

Final question: Are there any problems with using either of these in Outlook 2003?

I am amazed that something like this can be so un-straightforward, and utterly mystified by what is going on with the BinaryToString argument (oPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C190102")).

Yours seeking enlightenment
Hopeful Kiwi
Who is Participating?
David LeeConnect With a Mentor Commented:
Ok.  Exchange uses X.400 addressing internally.  That's way you see one when you retrieve SenderEmailAddress for senders who are in your Exchange organization.  For messages sent from people outside of your Exchange organization SenderEmailAddress should always return an SMTP address.  Since you have Outlook 2010, I don't believe it makes any difference what version of Outlook the sender is using, getting the sender's SMTP address is pretty simple.  The function below takes a single parameter, an email, and returns the SMTP address of the sender.  To do that it first gets the Sender property which is an AddressEntry object.  Next, it tests to see if that object's user type is a Exchange address entry for a user.  If it is, then it fetches the sender's information from Exchange and gets and returns the primary SMTP address for that user.  If the sender is not an Exchange user (i.e. someone outside of your organization), then the function returns the SenderEmailAddress.  

Function GetSMTPAddress(olkMsg As Outlook.MailItem) As String
    Dim olkSnd As Outlook.AddressEntry, olkExu As Outlook.ExchangeUser
    Set olkSnd = olkMsg.Sender
    If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
        Set olkExu = olkSnd.GetExchangeUser
        GetSMTPAddress = olkExu.PrimarySmtpAddress
        GetSMTPAddress = olkMsg.SenderEmailAddress
    End If
    Set olkSnd = Nothing
    Set olkExu = Nothing
End Function

Open in new window

Dave BaldwinFixer of ProblemsCommented:
Email frankly is a mess.  No two clients send it out the same way and the headers are different depending on who knows what.  In addition, the actual sender is known only in the 'envelope' that passes between the servers.  Everything you can see in the headers can be faked.  

I say all that before asking why you are trying to do this?  What is your goal?
kiwi_731Author Commented:
Hello Dave

My app has the job of going through a mail folder and processing the emails, which are expected to have timesheets attached (Excel spreadsheets actually).

Obviously, the timesheets *should* contain the name of the sender in a certain place, but the whole thing is a bit too free-texty for comfort. For the avoidance of doubt therefore, I want to be able to get the sending user's email address, which will be unique and not subject to arbitrary change.

I don't want spurious "users" proliferating because of user typos.

PS I do save their Windows login ID from the environment, but this isn't quite enough as it is a multi-site situation and the same login name may refer to different people on different sites.


Hopeful Kiwi
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Dave BaldwinFixer of ProblemsCommented:
Ok, that makes sense.  I can't help you with the code but I wanted to make sure you weren't going off on a spam catching adventure cause I'm not sure that you could do it from that viewpoint.  I suggest you click on "Request Attention" and see if they can add some better zones to your question that will get the attention of people who can help you.
David LeeCommented:
Hi, kiwi_731.

I may be able to help with this but I need to know what version of Outlook you're using.  There's different ways of accomplishing this based on the Outlook version.
kiwi_731Author Commented:
Hi BlueDevilFan

I have just upgraded to Outlook 2010. However, the Outlook version of the users who send the emails may be either Outlook 2010, Outlook 2007 or Outlook 2003.

Hopeful Kiwi
kiwi_731Author Commented:
Hi BlueDevilFan and anyone else

Basically, I want a good way of inserting the sender's email in a suitable place in the spreadsheet when they click a button to email it.

I have been poking round the object model, and it seems that

olkNS.accounts(1).SmtpAddress     ' olkNS is the logged-in Outlook namespace

has what I want - at least, I can see it from the Locals window when I step through the code. But when I try to retrieve it in the code or in the immediate window, I get a run-time error 450: Wtrong number of arguments or invalid property assignment. I can retrieve olkNS.Accounts.Count just fine, but as soon as I introduce a subscript to get down to where I want to be, it causes an error 450.

Note that I am using late binding of Outlook to Excel, since I don't know which version of Outlook is being used, so olkNS is actually defined as an Object, not Outlook.NameSpace. Might this be the reason why it doesn't like subscripts?

Hopeful Kiwi
kiwi_731Author Commented:
Hi BlueDevilFan

That looks to be just the ticket for what I am after. I haven't yet had a chance to try it out though. I was hoping to get to it today (hence the delay) but I shan't get to it now before next week.

BTW I have solved the above problem with the error 450 and subscripts! The corrected line reads:

Worksheets("Config").Range("USER_EMAIL").Value = olkNS.Accounts.Item(1).smtpaddress

which replaces:

Worksheets("Config").Range("USER_EMAIL").Value = olkNS.Accounts(1).smtpaddress

 - though I don't know why it makes a difference. Any info on this?


Hopeful Kiwi
kiwi_731Author Commented:
Hi BlueDevilFan

1. Your function works superbly, solves my immediate problem and is generally illuminating, in stark contrast to the MS version.

2. Re my query in 39285018 above, I think I have come to the answer by a bit of reflection: If you are usiing late binding, you cannot use default properties in objects - everything has to be explicitly spelled out. Is this true?

3. Re my original question about how the MS version works, I am still curious in a theoretical way, but it is not a show stopper. I note from looking here that contrary to appearances, the obscure string seems to be unrelated to the MS website. Hmm.

Hopeful Kiwi
kiwi_731Author Commented:
This answers my main practical concerns clearly. I would award an A if I also had an answer to the original question about the code snippet from the Microsoft Help. Never mind - if I really need to know it, I will start another thread.

Many thanks

Hopeful Kiwi
David LeeCommented:
Hopeful Kiwi,

Sorry to be slow to get back to you.  In answer to your questions,

2.  No, that is not true.  You can access any object property without regard to the type of binding you used.  I typically write code with early binding turned on so intellisense works and lets me choose property names, then switch to late binding when sharing the code.

3.  If you want to go the PropertyAccessor route to get the address, then the correct comnmand is

SenderID = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.