My company has workstations connected to two separate networks. I have extremely active email accounts on both, but I work on one machine—and one network—far more than the other. I can’t be bothered to log back on to my other workstation, which locks after only 5 minutes of inactivity, to check email. Because I’m a programmer, I created a macro to forward email messages to my preferred machine—which wasn’t a bad idea, but I then found myself replying to my other account instead of to the person who originated the email!
After catching some flack from people who were waiting for my response, I decided that I should come up with a macro to insert the correct email address in the To field, rather than my own. This turned out to be more of a challenge than I’d anticipated, so I’d like to share my solution, to help you circumnavigate the gotchas that I ran into.
Intercepting Reply Mail Items
Outlook, like all Microsoft applications, is highly event driven. The code fires in response to certain user actions, such as clicking a button, tabbing onto a control, or pressing keys. Other actions are the result of application life-cycle events, such startup and shutdown. Finally, there are specific events such as adding an appointment, setting a message’s importance, or receiving a new email message. One thing to keep in mind about event-driven applications is that where you place the code is half the battle. If you choose the wrong event, you’ll likely encounter all sorts of nasty side effects, including events not firing, firing too many times, and firing at the wrong times.
Our goal here is to capture email messages that are a reply to certain forwarded emails—in particular, those that were formatted from one of our other accounts. This task seems simple, but as I said, events can be tricky to pin down.
The most logical candidate is the MailItem_Reply event. What could be simpler? We want to run code when we hit the Reply button. Unfortunately, the Reply event only occurs in response to open MailItems. Thus, if you’re replying to an item that’s selected in the Inbox Explorer pane but not open, the Reply event won’t fire.
Another logical place to check for a reply action is the Reply button itself. You can trap the Click event of a toolbar button as follows:
Dim WithEvents objReplyButton As Office.CommandBarButton Set objReplyButton = ActiveExplorer.CommandBars.FindControl(, 354)
However, this also turns out to be the wrong place, because it runs after the MailItem’s Reply event, so you can’t get a handle to it from there.
I could go on, but let’s end the suspense. The best place for changing message properties turns out to be the MailItem_Open event. Although it’s more generic than what we want, there are ways to narrow the scope to what we’re looking for.
Visual Basic Editor
All Microsoft Office applications come with a full-featured IDE, called Visual Basic Editor, that provides an interface for accessing application object models through code so that you can call object methods, set object properties, and respond to object events. The code used to accomplish these goals is a specialized subset of the Visual Basic (VB) language, called Visual Basic for Applications (VBA).
A Developer tab on the Outlook ribbon lets you access Visual Basic Editor and other developer tools. However, this tab is disabled by default to protect you against viruses and other malicious code.
Therefore, you need to perform the following steps before you can use it:
- Select Outlook Options from the File tab to open the Outlook Options dialog box, and click Trust Center.
- Click Trust Center Settings, then select the Macro Settings option on the left.
- Select the macro security level that suits your comfort level, keeping in mind that this setting also pertains to other people’s macros and not just your own. If you don’t want to give all macros carte blanche, you can have Outlook display a prompt each time a macro is about to run. That way, you can decide whether or not you want to let the macro run. This option is called Notifications for all macros.
- Restart Outlook for the changes to take effect.
The Visual Basic button will appear on the far left, as Figure 1 shows.
Figure 1: Outlook 2010’s Developer tab
Accessing the MailItem_Open Event
The secret to accessing an object’s event in Outlook is to include the WithEvents keyword in the object declaration. The following code should be placed at the top of the ThisOutlookSession module:
Public WithEvents myMsg As Outlook.MailItem
After you add the object declaration, you can access it and its events from the Object and Procedure drop-downs. Note in Figure 2 that my Close and Open events are in bold because I added those events to my code. To add an event, you simply have to select it from the list; Outlook will add an empty sub to the module:
Private Sub myMsg_Open(Cancel As Boolean) End Sub
Binding myMsg to the Inspectors_NewInspector Event
At this point we’ve declared a MailItem object and created an event for it, but we still need to set it somewhere. The place to do so is in the Inspectors_NewInspector event. The Inspectors object is actually a collection that contains the Inspector objects representing all open inspectors. Any time you open a window in which an Outlook item is displayed, that item is an inspector. Again, we’re scattering our shots all over the place because an inspector can contain anything from a new appointment to a new task item. The good news is that we’ve narrowed down the field to items that are new. Therefore, opening an existing email message won’t cause the Inspectors_NewInspector event to fire.
We can get at the Inspectors events the same way as we did with the MailItem. First, we use WithEvents to declare it, as follows:
Public WithEvents myOlInspectors As Outlook.Inspectors
Then we can access the newInspector() sub:
Private Sub myOlInspectors_NewInspector(ByVal Inspector As Inspector) End Sub
Before we set our myMsg MailItem, we have to perform a couple of checks to accept only the inspectors that we want. The first test is whether the item is in fact an email message. The last thing we’d want to do is try to set a MailItem to another type. The inspector, which is passed to the sub, has a CurrentItem property that refers to the item the user is currently viewing. We can check its Class property to determine whether it’s a MailItem. In fact, there’s a constant named olMail that can be used for this purpose.
Another necessary check is for the unique ID string that the Messaging API (MAPI) store provider assigns when an item is created in its store. Listing 1 contains the code to perform this check. Therefore, the EntryID property isn’t set for an Outlook item until it’s saved or sent. This will separate our replies from those of other people. Setting the MailItem as in Listing 1 will cause its Open event to fire.
The myMsg_Open Event
The MailItem_Open event is the ideal place to set message values because it hasn’t yet appeared on the screen. After that happens, good luck changing its values! The following sections provide a step-by-step walkthrough of how to set the To, Subject, and Body values to match those of the original email.
After you set the myMsg MailItem object in the Insepctors_newInpector() event, every new email message will trigger the MailItem’s Open event, whether it’s a reply, a forwarded message, or a brand-new message.
Identifying forwarded emails. We can rely on the RE: prefix that Outlook adds to the subject to identify our replies. Moreover, our forwarded email messages have a subject line in the following format: From
Finally, the sender should be yourself. The MailItem.To field is the place to find that information:
Private Sub myMsg_Open(Cancel As Boolean)
If myMsg.subject Like "RE: From*" _ and myMsg.To Like "Gravelle*Robert*") Then End If End Sub
Retrieving the original sender from the message subject. The subject will contain either the sender’s display name or email address, depending on whether the sender is a member of the originating network. In either case, we need to parse it from between the “RE: From” and colon (:) subject text. The following code achieves this action:
Dim sender As String, pos As Integer pos = InStr(9, myMsg. Subject, ":") - 9 sender = Trim(Mid(myMsg.Subject, 9, pos))
Setting the To field to the original sender. Replacing your email address with the original sender’s won’t ensure that the mail server recognizes the sender. Therefore, applying the Recipient.Resolve() function will help. A failure to resolve the address is most likely caused by the display name being used instead of a full email address. It’s actually not that difficult to fix, because we know the originating network’s host name. In my case, converting the display name (formatted as Lastname, Firstname) into a proper email address (formatted as Firstname.Lastname@hostname.com) requires nothing more than reversing the name order, inserting a period between them, and appending the email address. A second call to Resolve() will confirm that this action did the trick. If not, I just leave the To field empty. However, I’ve never encountered this condition yet. Listing 2 contains the code to set the To field to the original sender.
Setting the subject. As in all forwarded email messages, the original subject line begins immediately after the “FW:” prefix. InStr() is used to find the original subject line’s position in the string. The text that follows is appended to the “RE:” reply identifier; thus, “REMINDER: Network Maintenance” would be parsed from “From Smith, Bob: FW: REMINDER: Network Maintenance,” as follows:
pos = InStr(9, subject, ":") - 9 'start search after the "RE: From" pos = InStr(pos + 1, myMsg. Subject, "FW:") myMsg.Subject = Left(myMsg. Subject, 4) & _ Trim(Mid(myMsg. Subject, pos + 3))
Setting the message body to the original text. As you know, every time you reply to or forward an email message, Outlook appends some text to the body, such as your signature and the originating message’s properties. Although not essential, it’s possible to remove the extra section from the email and revert the message body back to that of the original message. There are two ways to do this: You can either parse the message to remove the extra text, or you can replace the entire message body with that of the original one. The latter is my preferred solution because different body formats can make parsing a nightmare.
It’s best to take care of the message body first, before manipulating the subject line. As you’ll see, the code that finds the originating message, called the parent, uses the ConversationTopic property. Changing the message subject alters this property.
Finding the parent is a two-step process. First, the code checks the currently selected message in the active Explorer window. The currently selected item in the Explorer window is likely to be the parent. We can confirm this by comparing the ConversationIndex of our reply to the message. When you reply to a message, Outlook removes 10 characters (5 bytes) from the ConversationIndex. Hence, the parent email’s ConversationIndex minus the last 10 characters will match the reply’s ConversationIndex.
To set the message body, we need to check the body format, because it could be HTML, RTF, or plain text. A Select Case statement, such as that in Listing 3, is used to set the appropriate body property.
As I said, the currently selected message in the active Explorer window is likely the parent of the reply. However, it’s also possible that it isn’t. For instance, if you use the button on the MailItem Inspector to reply, you might have selected any number of other messages since opening the forwarded email message. (You might even be in another folder altogether.) Assuming that you’re still in the same folder that the parent originated from, you can use the MAPIFolder.Items.Restrict() function to find the parent. This function accepts a specially formatted string that contains the property to search and its value. The function returns a collection of items. The ConversationIndex is then checked against these items to locate the parent. Listing 4 contains the code that calls the MAPIFolder.Items.Restrict() function.
Circumventing Outlook’s Infamous Warning Dialog
Because it’s such a popular product, Outlook has long been the target of hackers. To help thwart the attempts of attackers, Microsoft implemented numerous security features into Outlook. I’m all for security, but I wish Outlook’s security police wouldn’t intercept my own code. I’m not trying to bring down my own machine—at least not on purpose!
Microsoft Office 2010, 2007, 2003, 2000, and 98 all include this Outlook security patch in SP2. When a macro tries to read any email properties, you’ll see a warning dialog box such as the one in Figure 3. You can’t do much about these annoying dialog boxes; even setting the security level to low (which I don’t recommend) won’t affect them.
Figure 3: Security warning in Outlook 2010
Luckily, there are a few workarounds. My personal favorite is to use the Outlook Redemption feature. Redemption is a regular COM library; after it’s registered on the system, it’s accessible to any programming language (e.g., VB, VBA, VC++, Delphi). Redemption uses extended MAPI (which isn’t affected by the security patch because it isn’t accessible to the scripting languages) to duplicate the functionality blocked by the security patch. All Safe*Item Redemption objects have an Item property that must be set to an Outlook item. Through the Item property, you can access any MailItem properties and methods, both blocked and not blocked. For the blocked properties and functions, Redemption objects completely bypass the Outlook object model and behave exactly like Outlook objects with no security patch applied.
Using Redemption in the myMsg_Open() Event
Making the MailItem.Open() event code work with Outlook Redemption requires replacing all read references to the MailItem’s sender and recipients with Redemption’s SafeMailItem. One caveat to using the SafeMailItem is that you can’t access recipient information until the message has been saved. Therefore, you can’t retrieve information about a message’s recipient list for new messages. However, this problem is easy to remedy: Just call the Save() method on the original MailItem before assigning it to the Redemption SafeMailItem. This action adds the SafeMailItem to the Drafts folder. After you assign the SafeMailItem’s Item property to the original mail message, you can access both the MailItem and additional Redemption properties.
Other than the addition of a Redemption.SafeRecipient object to handle resolving the email address, the rest of the code is largely identical to the original Open event. Listing 5 contains the code to set the sender and subject line using Redemption. It doesn’t contain the optional code to set the body.
Grab Your Fork and Dig In
Although replying to a forwarded email message isn’t as simple as setting a rule, Outlook does provide the capability to do so, as long as you’re willing to venture into the world of Outlook events and VBA code. Many people steer clear of this part of Outlook for fear of introducing bugs into their beloved email application. However, all you need to do is take a little time to consider the best event(s) in which to place your code. Everything after that is a piece of cake!
Listing 1: Setting the myMsg MailItem Object
Private Sub myOlInspectors_NewInspector(ByVal Inspector As Inspector) If Inspector.CurrentItem.Class = olMail Then If Len(Inspector.CurrentItem.EntryID) = 0 Then Set myMsg = Inspector.CurrentItem End If End If End Sub)
Listing 2: Replacing Your Email Address With That of the Original Sender(s)
With myMsg.recipients .Remove 1 .Add sender .Item(1).Resolve If Not .Item(1).Resolved Then 'could be using "lastname, firstname" display format 'used for known users on originating network If InStr(1, sender, ", ") Then Dim senderNames() As String .Item(1).Delete senderNames = Split(sender, ", ", 2) 'reverse name order and convert to 'firstname.lastname@networkaddress format sender = senderNames(1) & "." & senderNames(0) & "@microsoft.com" .Add sender .Item(1).Resolve End If 'didn't work. Leave it empty. If Not .Item(1).Resolved Then myMsg.To = "" End If End With
Listing 3: Formatting the Message Body the Same as the Original Message
If safemsg.subject Like "RE: From*" _ And safemsg.To Like "Gravelle*Robert" Then 'set the body to the original email Set myOlSel = Application.ActiveExplorer.Selection If myOlSel.Count = 1 Then If myOlSel.Item(1).Class = OlObjectClass.olMail Then Set oOriginalEmail = myOlSel.Item(1) Dim strParentConversationIndex As String strParentConversationIndex = Left(oOriginalEmail.ConversationIndex, _ Len(oOriginalEmail.ConversationIndex) - 10) If strParentConversationIndex <> myMsg.ConversationIndex Then _ Set oOriginalEmail = FindParentMessage(myMsg) If Not oOriginalEmail Is Nothing Then Select Case oOriginalEmail.BodyFormat Case olFormatHTML myMsg.HTMLBody = oOriginalEmail.HTMLBody Case olFormatPlain, olFormatRichText myMsg.Body = oOriginalEmail.Body End Select End If End If End If ...
Listing 4: Using the ConversationIndex and ConversationTopic Properties to Locate the Original Message
Function FindParentMessage(msg As Outlook.MailItem) As Outlook.MailItem Dim strFind As String Dim strIndex As String Dim fld As Outlook.MAPIFolder Dim itms As Outlook.Items Dim itm As Outlook.MailItem On Error Resume Next strIndex = Left(msg.ConversationIndex, _ Len(msg.ConversationIndex) - 10) Set fld = Application.Session.GetDefaultFolder(olFolderInbox) strFind = "[ConversationTopic] = " & _ Chr(34) & msg.ConversationTopic & Chr(34) Set itms = fld.Items.Restrict(strFind) For Each itm In itms If itm.ConversationIndex = strIndex Then Set FindParentMessage = itm Exit For End If Next End Function
Listing 5: The Complete MailItem_Open() Subroutine
Private Sub myMsg_Open(Cancel As Boolean) Dim safemsg As New SafeMailItem myMsg.Save safemsg.Item = myMsg If safemsg.subject Like "RE: From*" _ and safemsg.To Like "Gravelle*Robert*" Then Dim sender As String, subject As String, _ pos As Integer, sendTo As Redemption.SafeRecipient subject = safemsg.subject pos = InStr(9, subject, ":") 9 'start search after the "RE: From" sender = Trim(Replace(Mid(subject, 9, pos), vbTab, "")) safemsg.recipients.Remove 1 safemsg.recipients.Add sender Set sendTo = safemsg.recipients(1) sendTo.Resolve If Not sendTo.Resolved Then 'could be using "lastname, firstname" display format 'used for known users on originating network If InStr(1, sender, ", ") Then Dim senderNames() As String sendTo.Delete senderNames = Split(sender, ", ", 2) sender = senderNames(1) & "." & senderNames(0) & _ "@cbsa-asfc.gc.ca" safemsg.recipients.Add sender Set sendTo = safemsg.recipients(1) sendTo.Resolve End If End If myMsg.To = IIf(sendTo.Resolved, sendTo.Address, "") 'set the subject pos = InStr(pos + 1, subject, "FW:") myMsg.subject = Left(subject, 4) & Trim(Mid(subject, pos + 3)) End If End Sub