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 _
(oPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x0C190102"))
'Obtain AddressEntry Object of the sender
Set oSender = Application.Session.GetAdd
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
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.GetAdd
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
ASKER
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
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
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.
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.
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.
ASKER
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
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
ASKER
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).SmtpAddr
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
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).SmtpAddr
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
which replaces:
Worksheets("Config").Range
- though I don't know why it makes a difference. Any info on this?
Thanks
Hopeful Kiwi
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
which replaces:
Worksheets("Config").Range
- though I don't know why it makes a difference. Any info on this?
Thanks
Hopeful Kiwi
ASKER
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
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
ASKER
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
Many thanks
Hopeful Kiwi
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
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")
I say all that before asking why you are trying to do this? What is your goal?