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.