A user came to me with a problem:
I need help. Is it possible to write macro to read the sender email address in the email attached to another message?
Yes, it is possible. You can save the attachment to the hard drive to get the message properties or you can use Redemption to read attachment properties, including addresses and other MAPI fields. I have a sample that uses Redemption at GetAttachmentInfo
' Install Redemption ' http://www.dimastr.com/redemption/download.htm Sub GetAttachmentInfo() Dim olApp As Outlook.Application Set olApp = CreateObject("Outlook.Application") Dim olNS As Outlook.NameSpace Set olNS = olApp.GetNamespace("MAPI") Dim oRDOSession As Redemption.RDOSession Set oRDOSession = CreateObject("Redemption.RDOSession") oRDOSession.MAPIOBJECT = olNS.Application.Session.MAPIOBJECT Set Mail = olApp.ActiveExplorer.Selection.Item(1) Debug.Print "EntryID: " & Mail.EntryID Set Mail = oRDOSession.GetMessageFromID(Mail.EntryID) For Each att In Mail.Attachments Debug.Print "Filename: " & att.FileName Debug.Print "Embedded Msg Subject: " & att.EmbeddedMsg Debug.Print "Sent To: " & att.EmbeddedMsg.To Debug.Print "Sender: " & att.EmbeddedMsg.SenderEmailAddress Debug.Print "SentOn: " & att.EmbeddedMsg.SentOn Next End Sub
How to use the macros on this page
First: You need to have macro security set to the lowest setting, Enable all macros during testing. The macros will not work with the top two options that disable all macros or unsigned macros. You could choose the option Notification for all macros, then accept it each time you restart Outlook, however, because it's somewhat hard to sneak macros into Outlook (unlike in Word and Excel), allowing all macros is safe, especially during the testing phase. You can sign the macro when it is finished and change the macro security to notify.
To check your macro security in Outlook 2010 and newer, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, look at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
The macros on this page should be placed in a module.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
Set a reference to other Object Libraries
If you receive a "User-defined type not defined" error, you need to set a reference to another object library, Redemption Outlook and MAPI COM Library for the macro above. Go to Tools, References menu. Locate the object library in the list and add a checkmark to it.
More information as well as screenshots are at How to use the VBA Editor
For more information and links to download Redemption, see RDOAttachment object