Print - Showing the Sender's Email Address

In this sixth installment of Outlook VBA on Demand, I want to deal with an annoyance—specifically, how Outlook doesn't always display a sender's full email address on a message in your Inbox folder. You'll learn two new skills: how to add a new field to Outlook items and folders and how to use Collaboration Data Objects (CDO) to determine the sender's email address.

CDO is an alternative object model that you can use to access Microsoft Outlook and Exchange Server data. Most CDO applications are server-based—either Exchange Server folder scripts or Active Server Pages (ASP) for Outlook Web Access (OWA). However, situations (such as the focus of this column) exist in which you'll want to use CDO for client applications. Although the Outlook object model exposes a SenderName property for the MailItem object that represents a message, it doesn't provide any way to obtain the sender's email address. (This fact might seem peculiar, because you can see the From address if you open the item, but that is the way the Outlook object model works.)

Because CDO isn't part of the default Outlook 2000 installation, you face a potential stumbling block. Before you use this column's sample code, open the Control Panel Add/Remove Programs applet and check your Outlook components to make sure CDO is installed. Add it if necessary. You must also add CDO to the references for Outlook VBA so that you can use CDO's objects, properties, and methods. Choose Tools, References, and check the Microsoft CDO 1.21 Library check box.

Now, put the code you see in Listing 1 in the Outlook VBA window's ThisOutlookSession module. As I've shown in earlier installments of this column, this code sets up the Inbox so that VBA can monitor it for new items. Next, put the code for the ItemAdd event handler (which you see in Listing 2) in ThisOutlookSession. This procedure runs when a new item enters the Inbox. If you have other code that you want to run against new messages in the Inbox, include it inside the If Item.Class = olMail Then...End If block.

The code in Listing 2 uses the Add method on the item's UserProperties collection of custom fields to add a FromAddress property to each incoming item. To add a property, you supply the name and data type. In this area, Outlook and Exchange Server are different from a standard database. In a database, custom fields apply to all records in a table. Outlook and Exchange Server, however, let you have fields that appear in certain items in a folder but not in others. (You can also add a field to a folder so that all items in the folder can use it.) This flexibility to handle semistructured databases gives an Outlook and Exchange Server implementation much of its power.

Choose Insert, Module to create a new module in the Outlook VBA window. By default, Outlook names the module Module1, but you can change the name in the Properties window (to the left of the VBA screen). Call the module OutlookFunctions, and use it to store functions that you want to reuse within your Outlook VBA routines. Copy the code you see in Listing 3 into the OutlookFunctions module.

Outlook and CDO are different object models that you can use to access Outlook and Exchange Server data, but they have one common feature: You can obtain any item if you know the item's EntryID—a unique identifier within each message store (i.e., a set of folders such as those in your mailbox or the Public Folders hierarchy)—and StoreID. This capability lets you pass to the CDO model an item that the Outlook model returned, and vice versa. Therefore, the SenderFromAddress() function first obtains the EntryID for the item and the StoreID from its parent folder as the String variables strEntryID and strStoreID.

Next, the code starts a CDO session and performs the necessary logon. CDO supports different types of logons depending on the type of application. If you're using CDO in an Outlook VBA application, the type of logon you see in Listing 3 is typically appropriate because it causes CDO to share the same Messaging API (MAPI) profile that Outlook uses.

Finally, the GetMessage method uses the strEntryID and strStoreID data to return the item as a CDO Message object. Using the Sender.Address property of the Message object to get the sender's email address is then almost mundane.

Note that the declarations section of the SenderFromAddress() function uses the following line to declare a Message object from the CDO model:

Dim objCDOItem As MAPI.Message

where MAPI indicates that the Message object is part of the CDO model. If you press F2 to use the object browser in VBA, you'll see the CDO components under the MAPI library. Including the explicit MAPI prefix when you declare CDO objects is a good habit to form. Seeing the MAPI prefix reminds you that you need to use CDO properties and methods—not those from the Outlook model.

After you enter the code, close Outlook and restart it so that the olInboxItems object declared WithEvents (in ThisOutlookSession's declarations section) can initialize and prepare for the ItemAdd event to fire. The next time you receive a new email message, Outlook will add the FromAddress property to the item and fill the FromAddress field with the sender's email address.

How do you view the email addresses that reside in the FromAddress property? When you use UserProperties.Add (which Listing 2 includes), Outlook—by default—adds the field to the folder in which the item resides. After the code runs against one or more items, you can use the Field Chooser to add the FromAddress field to the folder. Right-click the Inbox's column headings and select Field Chooser. Switch to the User-defined fields in Inbox list and drag the FromAddress field to the view. Messages from the Internet will show an SMTP address under FromAddress, whereas messages from other Exchange Server users will show an X.400 address.

This method has one catch: Messages from senders that the Global Address List (GAL) lists as custom recipients will show an X.400 address (just as internal recipients do), not an SMTP address. This shortcoming occurs because Exchange Server automatically matches the sender with its GAL listing and uses the GAL entry to handle the sender properties. In a future column, I'll enhance the SenderFromAddress function so that it reports the SMTP address of external senders, even if they reside in the GAL.

I'm new to development in Outlook and have found these columns really useful, I have been trying to adapt Listing 1 & 2 that you have in the June 2000 issue. I would like to handle the Open/Read Event for Appointment Items in a Public Calendar. I have changed your first line to Private WithEvents olAppointmentItems as AppointmentItem I can then open the Sub Open Event but nothing seems to happen. I cannot quite work out what to include in the Application_Startup procedure and what SET statements to include, I'm sure I'm missing something really obvious but can't see for looking. can you help ? Cheers

Sir/Madam, I have used your Listing 3 to find the from address of the sender. Thank you. I ve written one macro in Micrsoft Outlook.The program returning sender's from address for some mail and not for some other mails. i dont know why ?, can u please help me out regarding this?

Every time I access the Sender.Address or anything else about the sender from my VB app, my app crashes and Win 2000 generates an error log.

What is the message i have to pass in this string Function SenderFromAddress(objMsg As MailItem) As String please reply regards

I tried to find this information on Microsoft's support site and got absolutely nowhere. A quick search on google.com and I found this article which did exactly what I wanted. Cheers!

You can not believe how much i have been looking for a solution to this problem, but here it is and it works perfectly... Thanks a bunch :)

I believe I've followed the instructions, but I keep getting an "Invalid Outside Procedure" error... help! :)

Please remember that in the visual basic editor program you must select Tools>References then be sure Microsoft CDO 1.whatever is checked. This threw me off for a while because I can't interpret code very well. My mind explodes. The best way for me to do something with code is a laymans step-by-step guide. 1. do this. 2. do this, then this. ALL IN ALL THIS HELPED ME SO MUCH. THANK YOU Sue Mosher!

The code in Listing 3 will trigger a security prompt in versions of Outlook with the Email Security Update. To avoid the prompt, you might want to use the third-party Redemption library instead of CDO. Sample code at http://www.slipstick.com/dev/code/getsenderaddy.htm#redemption

Suresh, the code in Listing 2 calls the SenderFromAddress() Function, passing in the new message added to the Inbox.

This code works. Pay careful attention to all the details, but it worked for me and I am joyous. THANK YOU.

After doing this, I don't get a FromAddress field in the "User-defined fields in Inbox" list. What could be the problem? I have OL2000 IMO using POP server (not Exchange), with all the latest patches.

If "nothing" happens, make sure you set the security-settings under "Tools - Macro - Security" at least to "medium".

Hmm... Maybe I messed up, but when I put Listings 1 and 2 into the ThisOutlookSession Module, and I put Listing 3 into a new module named OutlookFunctions, yet I receive an error. When I shut down Outlook after this, and reopen it, and receive a new email, I get an error statement on the line in Listing 2 Dim objCDO As MAPI.Session saying user-defined type not defined. I am a little perplexed as to why. Any ideas?

Nick, it sounds like you either have not installed CDO as part of your Outlook installation or did not add a reference to CDO 1.21 using the Tools | References command, as described in paragraph 3 of the article.

For samples that show how to obtain the SMTP address for an Exchange sender, see http://www.slipstick.com/dev/code/getsenderaddy.htm#redemption and http://www.cdolive.com/cdo5.htm#EMailAddressOfSender

Nick, here is what you forgot to do: You must add CDO to the references for Outlook VBA so that you can use CDO's objects, properties, and methods. Choose Tools, References, and check the Microsoft CDO 1.21 Library check box. Does anyone know if Sue (or anyone else) ever wrote how to attain the SMTP address of external senders, even if they reside in the GAL. At the end of the article, she says she intends to write about that.

Hi! I really hope you can help me. I've used the code EXACTLY like it is in Listing 1, 2, 3 but I always keep getting stuck at this line: Dim objCDO as MAPI.Session. I get this error: User-defined type not defined I have already referenced Microsoft CDO for NTS 1.2 Library, along with the office and outlook libraries. I still get that error. I have run the code on several separate machines, with the same error each time. Please help, as I would really like to get over this hurdle before I can really customize the code for what I need. Thank you!!

I am running into the same problem as well... a user-defined type error with the MAPI session object. All of the necessary references are included, any thoughts on where to go next?

Fay, wrong library. CDO for NTS is not the CDO used in Outlook client applications. You need a reference to CDO 1.21. If it is not available, rerun Outlook setup to add that component.

This is very good work indeed. I know absolutely nothing about programming, but managed to get this working! Fantastic! Thanks Sue.

When running the code of Listing 2 I get an error SenderFromAddress Sub or Function not declared. I have an Windows2000 professional box and run with Office 2003 Redemption also installed as well as all cdo references. This is the line where the error occurs. objProp.Value = SenderFromAddress(Item) Any help will be appreciated.

Bas, did you add all listings to your project? The SenderFrom() function is in Listing 3.

The program works great when receiving less then 7 to 10 e-mails at one time. I noticed that the process will stall and Outlook must be restarted. I've tried this code on several other machines that run OL2K and the same result. Seems to work better (maybe 15) on OL2K3 but the security prompt is not appealing. Maybe the process can't keep up with the numerous volumes of emails at one time? Thxs

I have installed CDO. Where can I find Outlook VBA? I'm a new user for Ms Outlook. Please help

Suzie -- You can start Outlook VBA with Alt+F11. If you're new to Outlook VBA macros, these web pages should help you get started: http://www.winnetmag.com/Articles/Index.cfm?ArticleID=21522&pg=1 http://www.outlookcode.com/d/vb.htm

Mike -- The ItemAdd event is known to be unable to handle a large number of messages coming in at once.

I am trying to extract the SMTP address from an email address that originates from an exchange server. I tried some of the options mentioned earlier but with no success. Can someone please help me! Thanks PS. The code works perfect for regular SMTP addresses!

I kinda found an answer to my own question (see July 06, 2004 post). Instead of using the code on those sites, I did some string and array manipulation to extract the Microsoft Exchange address. It works for now but it may not be the best way to do this. Here's the code - you can modify it to suit your needs but if you do something interesting please post it here for myself and others. Function SenderFromAddress(objMsg As MailItem) As String ' Dim strEntryID As String Dim strStoreID As String Dim objSession As MAPI.Session Dim objCDOItem As MAPI.Message ' get EntryID and StoreID for message strEntryID = objMsg.EntryID strStoreID = objMsg.Parent.StoreID ' start CDO session Set objSession = CreateObject("MAPI.Session") objSession.Logon , , False, False ' pass item to CDO and get sender address On Error Resume Next Set objCDOItem = objSession.GetMessage(strEntryID, strStoreID) SenderFromAddress = objCDOItem.Sender.Address ' Check to see if the address is a Microsoft Exchange email address If Left(SenderFromAddress, 3) = "/o=" Then ' Local variables Dim senderAddress() As String Dim strLength As Integer Dim i As Integer Dim username() As String Dim temp As Integer Dim sendersEmail As String ' Get length of MS Exchange string strLength = Len(SenderFromAddress) ' Parse thru the string and insert into array For i = 0 To strLength ReDim Preserve senderAddress(i) senderAddress(i) = Mid$(SenderFromAddress, i, 1) Next i ' Get to the username at the end of the string. ' I started from 55 because that is where the first letter of the username ' in my string started, yours might be different. ' Ubound(senderAddress) gets you to the index of the last letter in the username ' Finally extract the username and concatenate it into a string literal For i = 55 To UBound(senderAddress) Dim j As Integer sendersEmail = sendersEmail + senderAddress(i) Next i ' Create the full email address for display SenderFromAddress = sendersEmail + "@yourdomain.com" Set objSession = Nothing Set objCDOItem = Nothing End If End Function

Hi, I used ur code but am not getting the email id, instead am getting a big string as below "/O=WIPRO/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=DHEVEND" Please help me in finding out a way to extract sender email id.

I have plugged in your code, and there is no error, but I don't see any changes. Is there any way you can obtein fromaddress and date received as an export file or something. if you export file from outlook as an excel file you get from address, if you export outlook as an access mdb file you've got from as a name and received date. so is there any way you can get these two informations - from address and received date in one shot? thanks in advance

Question - Is there a way to trigger the code to run against mail you have already recieved? I notice that if I (Exchange Server) recieve mail with Outlook closed the field is blank. I want to be able to step thru my inbox on demand. Thanks.

Is there an equivalent of this code but to ensure that the recipient's address is printed, for example if I print out an email I have sent (rather than one I have received) I want it to show not only the name of the recipient but also their email address.