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

Posted on 2013-06-27
Last Modified: 2013-07-09
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("")).

Yours seeking enlightenment
Hopeful Kiwi
Question by:kiwi_731
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39283381
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?

Author Comment

ID: 39283688
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
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39283736
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.
Three Considerations for Containers

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read our article on Experts Exchange.

LVL 76

Expert Comment

by:David Lee
ID: 39283850
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.

Author Comment

ID: 39283860
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

Author Comment

ID: 39283880
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
LVL 76

Accepted Solution

David Lee earned 500 total points
ID: 39283894
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


Author Comment

ID: 39285018
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

Author Comment

ID: 39289528
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

Author Closing Comment

ID: 39306725
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
LVL 76

Expert Comment

by:David Lee
ID: 39309990
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("")

Open in new window


Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Changing a few Outlook Options can help keep you organized!
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question