Microsoft Outlook’s default behavior is to retain a copy of sent items in the Sent Items folder. But in a business environment, in which hundreds of email messages might be dispatched each day, such a generic setup might not be satisfactory. Many people want to organize email messages by topic or line of business (LOB), which isn't the same as sorting by conversation, as each LOB can contain numerous conversation threads. Although moving received messages to a specified folder is simple enough, doing so with sent messages is another matter. The primary stumbling block is that, although Outlook provides an option for moving a copy of a MailItem after sending, it does not offer a rule or option setting to move the sent item itself.
Related: Outlook 2010: Reply to Autoforwarded Emails
Many an Outlook user has attempted to come up with a way to move messages after sending, usually relying on complicated timed processes and bug-prone Windows API calls via a third-party DLL such as Outlook Redemption. After some thought and a little experimentation, I have come up with a couple of Visual Basic for Applications (VBA) macro solutions that should suit your needs nicely.
Limitations of the Rule-Based Approach
When confronted by behavior that doesn't conform to my expectations or preferences, I tend to be a little hasty in looking for programmatic solutions. (It's the developer in me!) But before jumping into VBA code with both feet, I try to check out the Outlook Email options and rules. Only after you've exhausted those avenues should you go looking for more complex fixes. I followed my own advice in this instance.
Related: Q: How can I have Microsoft Outlook 2010 ignore future messages in an email thread?
Let's see what we can do with the Outlook Rules Wizard. If you start with a blank rule, you can choose whether to apply the rule to incoming or outgoing messages, as Figure 1 shows. After you select the conditions to identify the messages that you're looking for, you can select a folder to which to copy those messages, as Figure 2 shows.
Figure 1: Using the Rules Wizard to apply a rule on sent items
Figure 2: The Rules Wizard Select Folder dialog box
However, note that this rule creates and moves a message copy; the original message is left in the Sent Items folder. The only way to avoid this is to clear the Save copies of messages in Sent Items folder check box (in the Message handling section of the E-mail Options dialog box), which Figure 3 shows. The downside to this approach is that it prevents Outlook from keeping copies of any sent messages, so you won't have access to sent messages that aren't picked up by your rule.
Figure 3: The E-mail Options dialog box
VBA Fix: Using the MailItem's Send Event
Dissatisfied with the rules-and-options route, I rolled up my sleeves, made a pot of coffee and got to work.
Like those who have tread these murky waters before me, I began with the MailItem_Send() event. The approach seems straightforward enough: You send an email, and then move it. Only one problem: The email isn't moved into the Sent Items folder until the Send event has completed. Hence, any attempt to find the message in the Sent Items folder proves fruitless. Start on second coffee. Think harder.
Where to Put the Code?
One key factor in event-driven programming is where to place the event-handling code. Make the wrong choice, and you could have a brittle and flaky application on your hands. There might be more than one candidate, but more often than not, one choice is better than the others.
One event that crossed my mind is my destination folder's ItemAdd() event. This event fires whenever one or more items are added to its Items collection. But upon further investigation, I realized that this event is faced with the same timing problems as the MailItem_Send() event. You'd also need to duplicate the same code for all your destination folders. Duplication of code isn’t considered good style, so forget about that idea.
It seemed that the MailItem_Send() event was still my best choice for the code, since I could apply my rule to all outgoing messages. I just needed to approach the problem from a different angle.
Filtering Messages by Criteria
In a perfect world, we could call our VBA macro from a rule. That way, the rule would perform the mail filtering and the macro would take care of moving the message. Outlook 2002 added an option to run a script on incoming messages, but unfortunately, there's no such option for outgoing messages. No reason to get upset, though; that's an inefficient way to launch script code anyway. I've tried it a few times and found it to be highly unreliable. Every now and then, it causes an error and the rule is deactivated. We'll do our filtering right in the oMsg_Send() event.
Let's say that I have several business contacts at RobGravelleAndCo.com and want to move all messages addressed to those contacts to an Outlook folder called FOSS Export (CR-035), which Figure 4 shows. The Recipients object contains a collection of Recipient items, each of which contains the properties and methods that relate to one recipient. One Recipient property is the AddressEntry object, which houses the recipient's address details, including email address. The Recipient has a property called Address for the email address. We'll examine that property in the oMsg_Send code at callout B in Listing 1.
Figure 4: Outlook Folders
Instead of trying to delete a message manually after sending it, we can set the MailItem's DeleteAfterSubmit flag to true so that Outlook does it for us. Just keep in mind that turning on the DeleteAfterSubmit flag via the MailItem Properties dialog box will delete all sent messages! That's a bit of a sledgehammer solution when all you want to do is move certain messages.
On the subject of moving messages, you can't move the message from within the MailItem_Send() event because Outlook isn't done with it yet. (Attempting to do so results in a nasty runtime error.) According to Microsoft, the preferred way to manage this delicate operation is first to use the Copy() function to clone the message, then to move the clone. Although this is not a true message move, the result is the same: After the clone is moved, the original message is deleted, thanks to the DeleteAfterSubmit flag.
Now we need a reference to our folder. Working with custom folders is a bit more work than using Outlook's default folders. You can't just use the folder name to call the GetFolder function (there isn't one). Instead, we need to navigate to the custom folder from one of the default folders. In our case, the FOSS Export (CR-035) folder is parallel to the Inbox, in the mailbox root. To obtain a reference to a default Outlook folder, simply call the Application.Session GetDefaultFolder() function with one of the olDefaultFolders Outlook Library enumeration values. For example, the following code retrieves the Inbox:
Set olInbox = Application.Session.GetDefaultFolder(olFolderInbox)
We can get to our folder by using this code:
Set oBusinessFolder = Application.Session.GetDefaultFolder(olFolderInbox).Parent.Folders(BUSINESS_FOLDER)
BUSINESS_FOLDER is a constant for our folder name. The oBusinessFolder can be passed directly to the MailItem.Move() sub, as it requires a MAPIFolder object. Similarly, we can get a folder's subfolder via its Folders collection property:
Set ObjFolder = Application.Session.GetDefaultFolder(olFolderInbox).Folders("")
The Visual Basic Editor
All Microsoft Office applications come with a full-featured IDE called the Visual Basic Editor. It 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 that's used to accomplish these goals is VBA, a specialized subset of the Visual Basic language.
A Developer tab is available on the Office Ribbon, to access the Visual Basic Editor and other developer tools. However, this tab is disabled by default to help protect against viruses and other malicious code. You need to perform the following steps before you can use this tab:
1. In Outlook, select Outlook Options from the File tab to open the Outlook Options dialog box.
2. In the Outlook Options dialog box, click Trust Center.
3. Click Trust Center Settings, and then choose the Macro Settings option on the left.
4. Select the Macro security level that suits your comfort level, keeping in mind that the setting pertains to other people's macros as well as 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 you want to let the macro run. That option is called Notifications for all macros.
5. Restart Outlook for the changes to take effect.
The Visual Basic button, which Figure 5 shows, will be on the far left in the Developer tab. Figure 6 shows the Visual Basic Editor.
Figure 5: Developer tab with Visual Basic button
Figure 6: Visual Basic Editor with Immediate window visible
The MailItem Send() Event
To make an object's events available in the Declarations drop-down list in the Visual Basic Editor (as Figure 7 shows), you need to use the WithEvents keyword to declare the object.
Figure 7: Declarations drop-down list in Visual Basic Editor
The two following object declarations allow us to access the MailItem Send() event:
Public WithEvents oInspectors As Outlook.Inspectors Public WithEvents oMsg As Outlook.MailItem
The Inspectors collection contains the Inspector objects for all open inspectors (i.e., a window that displays information about an Outlook item). The reference to the Inspectors collection is set in the Application_StartUp() event:
Private Sub Application_Startup() Set oInspectors = Application.Inspectors End Sub
Binding oMsg to the Current Inspector
By setting the MailItem reference in the Inspectors_NewInspector event, we specify that only new messages will be referenced. Opening an existing email message will not cause the Inspectors_NewInspector event to fire.
The Inspector, which is passed to the sub, has a CurrentItem property, which refers to the item that the user is viewing. We can check this item's Class property to determine whether it is a MailItem. We can use a constant named olMail 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 the store. Therefore, the EntryID property is set for an Outlook item only after the item is saved or sent. This check, which the code at callout A in Listing 1 shows, distinguishes new email items from existing ones. Setting the MailItem in this way causes its events, including the Send event, to fire.
The oMsg_Send Event in Action
I printed some output to the Immediate window (which the bottom pane in the Visual Basic Editor in Figure 6 shows) to test the process. Click View on the menu bar and then click Immediate Window if it isn't visible. Figure 8 shows some typical results when the message is addressed only to the host for which we're checking. This message contained a total of three recipients: one in the To field, one in the CC field, and one in the BCC field. All three recipients were contained within the MailItem's recipients collection. RobGravelleAndCo.com was the BCC address, as Figure 9 shows.
This final test run was a reply to that message, with the RobGravelleAndCo.com recipient removed. As expected, our rule did not move the sent item, as Figure 10 shows.
An Alternative Solution: Using the Sent Items Folder Items_ItemAdd Event
The oMsg_Send solution is a good choice if you're already processing new messages, and thus need to reference the new Item's Inspector. An alternative solution places the code in the Sent Items folder's Items_ItemAdd() event. (Listing 2 shows the ThisOutlookSession code for this solution.)
Placing the main logic in the Items_ItemAdd() event gives us a couple advantages. First, it results in less code. Second, it is highly efficient. All sent items land in the Sent Items folder, unless you have created rules that circumvent this behavior or have cleared the Save copies of messages in Sent Items check box in the E-mail Options dialog box. Note that both solutions presented here apply to one mailbox account. Therefore, if you wanted to apply similar processing to multiple mailboxes, you need to attach your processing code to each SentItems folder event, as Listing 3 shows.
Gaining Access to the Sent Items Folder's Items_ItemAdd() Event
The ItemAdd() event is a member of the Items collection object, so we need to use the WithEvents keyword at the top of the ThisOutlookSession module to declare an object of type Items:
Public WithEvents olSentItems As Items
The business folder information is also included here. If you expect a large volume of email related to a particular LOB, it's probably a good idea to create a global reference to its folder, as the code at callout A in Listing 2 shows. As before, the object references are set in the Application_StartUp() event. As the code at callout B in Listing 2 shows, this time I referred to the business folder in relation to the Sent Items folder (i.e., at the same level as the Inbox).
The Modified Rule Code
We no longer need to set the DeleteAfterSubmit flag to create a copy of the MailItem. However, we do need to check the item's Class type, as the Item parameter is a generic Object. Objects other than email messages, such as Meeting Items, can be placed in the Sent Items folder. I also took the extra step of storing the item in a proper MailItem object so that the IDE's auto-complete feature will kick in. If you know exactly which properties you need to access, you can dispense with this step.
The oSentItems_ItemAdd Event in Action
Again, I printed output to the Immediate window to test the Items_AddItem solution; everything worked. The example, in Figure 11, shows a message addressed only to the host for which we're checking. The message that Figure 12 shows was addressed to someone who isn't a member of the RobGravelleAndCo.com domain. As expected, only MailItems produced output.
Adding other Item types to the mix is easy; just change your If statement into a Select Case and include your target types as a comma-delimited list, as the code in Listing 4 shows.
Running the Move Sent MailItems Macro on Demand
After installing the Move Sent MailItems macro, you might want to run it on messages that were sent previously. To do so, use the Macros dialog box, which is available via the Macros button on the Ribbon. The only catch is that the macro provides access to public macros only, and our macros are not public. Even if we could see the SentItems folder’s ItemAdd event, it processes only the last sent message. Therefore, we need to add a public subroutine to loop through every item in the SentItems folder, as the code at callout C in Listing 2 shows. Now we can open the Macros dialog box, select our new public sub (if it isn't already selected), and click the Run button to execute it.
Safe and Simple
This article showed you how to use VBA code to extend the built-in rules and option settings in Outlook 2010. Specifically, you saw a couple ways to move a MailItem to a user folder after sending. Unlike many solutions that rely on complicated timed processes, bug-prone Windows API calls, or third-party DLLs, this one is much safer and simpler. As a client-side solution, it is independent of your mail server vendor and doesn't rely on your using Exchange Server. Moreover, it will work for any number of users, whether 50 or 5,000.
The only remaining question is how to best distribute the VBA code to users. There are a few ways to go about it, some of which require user cooperation and others that can be done remotely:
- Use the File | Export command in the Outlook VBA environment to export modules as .bas, .cls, or .frm files.
- Copy the VbaProject.otm file from the machine on which the macros were written to other users' machines, replacing any existing VbaProject.otm file.
- Use the Office Profile Wizard (Proflwiz.exe) to distribute the VBA project.
For more information on these techniques, see the article "To Distribute Microsoft Outlook VBA Code to Other Users."
Learning Path
Microsoft Outlook Programming: Jumpstart for Administrators, Developers, and Power Users, Sue Mosher
Listing 1: ThisOutlookSession Code for the oMsg_Send Solution
Option Explicit
Public WithEvents oInspectors As Outlook.Inspectors
Public WithEvents oMsg As Outlook.MailItem
Private Const BUSINESS_FOLDER = "FOSS Export (CR-035)"
Private Sub Application_Startup()
Set oInspectors = Application.Inspectors
End Sub
# BEGIN CALLOUT A
Private Sub oInspectors_NewInspector(ByVal Inspector As Inspector)
If Inspector.CurrentItem.Class = olMail Then
If Len(Inspector.CurrentItem.EntryID) = 0 Then
Set oMsg = Inspector.CurrentItem
End If
End If
End Sub
# END CALLOUT A
Private Sub oMsg_Send(Cancel As Boolean)
Dim oRecipient As Recipient, oBusinessFolder As MAPIFolder, oEmailCopy As MailItem
For Each oRecipient In oMsg.Recipients
# BEGIN CALLOUT B
If InStr(1, oRecipient.Address, "RobGravelleAndCo.com") Then
# END CALLOUT B
oMsg.DeleteAfterSubmit = True
Set oBusinessFolder = Application.Session.GetDefaultFolder(olFolderInbox).Parent.Folders(BUSINESS_FOLDER)
Set oEmailCopy = oMsg.Copy
oEmailCopy.Move oBusinessFolder
Exit For
End If
Next
End Sub
Listing 2: ThisOutlookSession Code for the Items_AddItem Solution
Option Explicit
Public WithEvents oSentItems As Items
Private oBusinessFolder As MAPIFolder
# BEGIN CALLOUT A
Private Const BUSINESS_FOLDER = "FOSS Export (CR-035)"
# END CALLOUT A
Private Const PARTNER_EMAIL_ADDRESS = "RobGravelleAndCo.com"
# BEGIN CALLOUT B
Private Sub Application_Startup()
Dim oSentItemsFolder As MAPIFolder
Set oSentItemsFolder = Application.Session.GetDefaultFolder(olFolderSentMail)
Set oSentItems = oSentItemsFolder.Items
Set oBusinessFolder = oSentItemsFolder.Parent.Folders(BUSINESS_FOLDER)
End Sub
# END CALLOUT B
Private Sub oSentItems_ItemAdd(ByVal Item As Object)
Dim oRecipient As Recipient, oMailItem As MailItem
If Item.Class = olMail Then
Set oMailItem = Item 'this will enable auto-complete for mailitems.
For Each oRecipient In oMailItem.Recipients
If InStr(1, oRecipient.Address, PARTNER_EMAIL_ADDRESS) Then
oMailItem.Move oBusinessFolder
Exit For
End If
Next
End If
End Sub
# BEGIN CALLOUT C
Public Sub runMoveSentItemsMacro()
Dim item As Object
For Each item In Application.Session.GetDefaultFolder(olFolderSentMail).Items
Call oSentItems_ItemAdd(item)
Next
End Sub
# END CALLOUT C
Listing 3: Code to Apply Processing to Multiple Mailboxes
Public WithEvents oAFSSentItems As Items Private oAFSBusinessFolder As MAPIFolder Public WithEvents oSTSSentItems As Items Private oSTSBusinessFolder As MAPIFolder Private Sub oAFSSentItems_ItemAdd(ByVal Item As Object) ... Private Sub oSTSSentItems_ItemAdd(ByVal Item As Object) ...
Listing 4: Code to Add Item Types
Private Sub oSentItems_ItemAdd(ByVal item As Object)
Dim oRecipient As Recipient
Select Case item.Class
Case olMail, olMeetingRequest
For Each oRecipient In item.Recipients
If InStr(1, oRecipient.Address, PARTNER_EMAIL_ADDRESS) Then
item.Move oBusinessFolder
Exit For
End If
Next
End Select
End Sub
