Solved

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

Posted on 2013-06-27
11
3,245 Views
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 _
    (oPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C190102"))

    '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
0
Comment
Question by:kiwi_731
  • 6
  • 3
  • 2
11 Comments
 
LVL 82

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?
0
 

Author Comment

by:kiwi_731
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.

HTH

Hopeful Kiwi
0
 
LVL 82

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.
0
 
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.
0
 

Author Comment

by:kiwi_731
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kiwi_731
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
0
 
LVL 76

Accepted Solution

by:
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
    Else
        GetSMTPAddress = olkMsg.SenderEmailAddress
    End If
    Set olkSnd = Nothing
    Set olkExu = Nothing
End Function

Open in new window

0
 

Author Comment

by:kiwi_731
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?

Thanks

Hopeful Kiwi
0
 

Author Comment

by:kiwi_731
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
0
 

Author Closing Comment

by:kiwi_731
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
0
 
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("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")

Open in new window

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Create high volume marketing opportunities using email signatures with these top 10 DOs and DON'Ts of email signature marketing.
Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now