Beyond the Rules Wizard

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Aitken on Office

Automating Outlook E-mail Handling with VBA

By Peter G. Aitken

Like many of you, I'm highly dependent on e-mail. It's difficult to imagine trying to get by without using e-mail to communicate quickly and easily with clients, editors, friends, and colleagues. Yet e-mail has its downside too. This is most evident when I log on to find dozens, if not hundreds, of messages waiting for me. Of course, I can't ignore them. There are certainly some important missives that I need to respond to immediately, if not sooner. But these vital messages are hidden, like wheat among the chaff, amidst everything from pure spam to a note from my mother. How can I deal with this mass of messages in an efficient manner?

One possibility for managing e-mails is to use Outlook's Rules Wizard. This tool permits you to define rules, or actions, that are applied to messages as they arrive. You could, for example, specify that any message with the words "WIN FREE MONEY" in the subject line should be deleted immediately. FIGURE 1 shows the Rules Wizard dialog box, with a list of defined rules at the top and the content of the selected rule at the bottom. While the Rules Wizard is reasonably flexible, it does have some limitations and can be rather cumbersome to use. Is there a better way?


FIGURE 1: Outlook's Rules Wizard dialog box.

Yes! And as you've probably guessed, it involves writing a macro with Visual Basic for Applications (VBA). With the power of a full-fledged programming language at your disposal, there are essentially no limitations as to how you can manage e-mail messages. You can implement whatever management scheme is dictated by your needs, or the needs of your client.

Another advantage is that VBA projects provide some security options that aren't available with the Rules Wizard. This doesn't mean that the Rules Wizard is useless and should be abandoned altogether, but if you know how to filter and handle your incoming e-mail with VBA, then you can choose the approach that best fits your circumstances.

The Outlook Object Model

To work with mail messages, you need to understand Outlook's object model. Actually, you only need to understand part of it. Remember, Outlook does many things besides e-mail; it has the Calendar, Journal, Contacts, Notes, and Tasks. However, these parts of Outlook aren't relevant to the present discussion.

Like all other Office applications, the Outlook object model has the Application object at the top of the object hierarchy. To do anything programmatically with Outlook, you must have a reference to this object. If you are creating e-mail management code inside Outlook, the Application object is always available as an implicit reference. You don't need to create a reference to this object or refer to it in your code. If you're creating code outside Outlook - in Word, for example - you'll have to explicitly create a reference to the Outlook Application object, as shown here:

Dim myOLApp As Outlook.Application
Set myOLApp = New Outlook.Application

You'll also need to explicitly include this reference in your code. For this article, however, I'll assume your code is being written within Outlook, so these steps won't be necessary.

FIGURE 2 diagrams the parts of Outlook's object model that you need for the current task. You can see the Application object at the top, with other objects and collections below it. These other objects will be explained in due course, but first I'll address the details of writing VBA code in Outlook.


FIGURE 2: The parts of the Outlook object model used in this article.

If you have no experience writing VBA code in Outlook, you may be wondering where exactly this code should be placed. Press [Alt][F11] to open Outlook's VB Editor (VBE), which is shown in FIGURE 3. In the upper left pane, open the project by clicking the adjacent + sign, and then open Microsoft Outlook Objects. Finally, click on ThisOutlookSession to display its code in the pane on the right. Your project may be empty, or it may already contain code. Finally, click on the right pane to make it active; this is where you'll enter and edit your code.


FIGURE 3: The Outlook VB Editor.

Outlook handles VBA projects differently from other Office applications. Word, Excel, and PowerPoint associate a VBA project with a specific document or template. Outlook doesn't have documents, so projects are associated with the Outlook application itself. There can be only one Outlook VBA project per user, and it's stored in the file, VbaProject.OTM. When only a single user is set up with Windows 95/98/00/ME, this file is kept in c:\Windows\Application Data\Microsoft\Outlook. When multiple users are set up, the file for each user, username, is kept in c:\Windows\Profiles\username\Application Data\Microsoft\Outlook. Windows NT keeps the file in c:\Winnt\Profiles\username\Application Data\Microsoft\Outlook.

The next step is to create a procedure to contain your code. Decide on a name for the procedure; a name that describes what the procedure does, such as "ProcessEmail." Then follow these steps:

1)        Select Procedure from the Insert menu. The Add Procedure dialog box will be displayed.

2)        Enter the procedure name in the Name textbox.

3)        Make sure that the Sub and Public options are both selected, and that the All Local variables as Statics option is not selected.

4)        Click OK.

The editor will insert the first and last lines of the procedure into the editing window, as shown in FIGURE 3. You'll place your code between these two lines.

Now that you understand the mechanics of creating a VBA procedure in Outlook, let's look at the object model in more detail. First, however, read over the following outline of the specific tasks that this procedure will perform:

1)        Obtain a reference to the Inbox folder, where Outlook places newly arrived messages.

2)        Examine each message in the Inbox and compare its subject and sender to certain criteria.

3)        If a message meets certain criteria, delete it.

4)        If a message meets certain other criteria, move it to the folder named "Immediate Attention."

5)        Otherwise, do nothing with the message (leave it in the Inbox).

This list constitutes a fairly simple approach to e-mail processing, but it will serve to demonstrate the programming techniques we're interested in. Once you understand the fundamental techniques, you can expand your processing criteria to include the message importance, its text, its send date, its recipients, and so on.

Obtaining Folder References

To read a message or to move a message to another folder, you need to obtain a reference to the folder that the message is in, or the folder where it will be moved. Almost anything you do programmatically in Outlook, including obtaining folder references, involves the Namespace object. Outlook is designed to handle more than one namespace, with each namespace representing a different source of data. At present, however, only a single data source, named MAPI, is supported. To work with the MAPI namespace, you obtain a reference to the MAPI Namespace object, as follows:

Dim myNS As Namespace
Set myNS = GetNamespace("MAPI") 

Once you have a reference to the Namespace object, you're ready to obtain your folder references. For the Inbox folder, this is a simple process; because the Inbox is one of Outlook's default folders, you can use the GetDefaultFolder function. Its syntax is:

GetDefaultFolder(folder)

where folder is a constant identifying a specific default folder. The constants for the e-mail-related default folders are as follows (with the constant's current value in parentheses):

  • olFolderDeletedItems (3)
  • olFolderDrafts (16)
  • olFolderInbox (6)
  • olFolderOutbox (4)
  • olFolderSentMail (5)

The following code, for example, obtains a reference to the Inbox folder:

Dim myInbox As MAPIFolder
Set myInbox = myNS.GetDefaultFolder(olFolderInbox) 

For non-default folders - ones you've created in Outlook - the process isn't so simple. You must look through the existing folders until you find one with the desired name. This is done using Outlook's Folders collections. Here's how things are organized:

  • The Namespace object has a Folders collection that contains all of the namespace's top-level folders. Each folder is represented by a MAPIFolder object. Typically, there's only one top-level folder in Outlook, named Personal Folders.
  • Each top-level folder has its own Folders collection, which contains one MAPIFolder object for each subfolder. Typically, the Personal Folders folder contains the Inbox folder, the Deleted Items folder, the Sent Items folder, as well as any custom folders you've created.
  • Each subfolder has its own Folders collection, containing subfolders. There's no limit on the number of levels of nesting of subfolders.
  • If a folder doesn't contain any subfolders, its Folders collection will be empty.

To locate and obtain a reference to a non-default folder, you need to look through the Namespace object's Folders collection, and then through the Folders collection of each of those folders until all levels of subfolders have been examined. For each folder, look at the MAPIFolder.Name property to determine the folder name. I don't have the space in this article to explain the details of using collections, but the following code shows how it's done (you can refer to the VBA online help for more information on collections):

Dim folder1 As MAPIFolder
Dim folder2 As MAPIFolder
Dim FoundFolder As MAPIFolder
  
Set FoundFolder = Nothing
For Each folder1 In myNS.Folder
   For Each folder2 In folder1.Folders
     If folder2.Name = "Immediate Attention" Then
       Set FoundFolder = folder2
     End If
   Next
Next

This code looks through all the first-level subfolders (that is, those folders at the same level as the Inbox) for a folder named "Immediate Attention." If the folder is found, the variable FoundFolder is set to refer to it. Otherwise, this variable will hold the special value Nothing. The code assumes that myNS is a reference to the MAPI Namespace object.

Once you have a reference to a non-default folder, you can access the messages it contains. More important to the current task, you can also move messages to the folder. You'll see how this is done soon.

Accessing Messages

Every folder has an Items collection associated with it. For folders that contain mail messages, this collection contains one MailItem object for each mail message in the folder. By looping through this collection, you can access every message in the folder. You use the MailItem object's properties to obtain information about the message. The properties relevant to the current discussion are described in FIGURE 4.

Property

Description

Body

The message content (if the message is in plain text format).

HTMLBody

The message content (if the message is in HTML format).

Importance

The message importance as a constant value: olImportanceLow, olImportanceNormal, or olImportanceHigh.

SenderName

The display name of the message sender.

SentOn

A property of type Date specifying when the message was sent.

Subject

The message subject.

Unread

True, if the message has not been opened; False, otherwise.

FIGURE 4: Relevant MailItem object properties.

While the Inbox usually contains only mail messages, it can also contain a schedule item, or meeting request, that was sent to you. A real-world program will have to take this possibility into account, as you will see in the sample program presented later. For now, however, I will assume that the Inbox contains only mail messages.

Let's look at a quick example. Suppose you wanted to look through all the messages in the Inbox and see if there are any unread messages from Linda Cooper. The following code fragment shows how this is done (assuming that myNS is a reference to the MAPI Namespace):

Dim msg As MailItem
For Each msg In myNS.GetDefaultFolder(olFolderInbox).Items
   If msg.Unread = True And _
     msg.SenderName = "Linda Cooper" Then
     ' Message meets criteria.
   Else
     ' Message doesn't meet criteria.
   End If
Next

Moving and Deleting Messages

When you have a reference to a specific e-mail message, you can use its methods to perform various actions with the message. The two actions you're concerned with are moving a message to another folder, and deleting a message. The table in FIGURE 5 describes the methods for moving and deleting messages, as well as some other methods that are available with the MailItem object. Please refer to the online documentation for a full list of MailItem object methods.

Method

Description

Delete

Deletes the message.

Display

Displays the message.

Move (dest)

Moves the message to another folder where dest is a reference to the destination folder.

PrintOut

Prints the message.

FIGURE 5: Some methods of the MailItem object.

When deleting or moving messages, there's one important consideration to keep in mind. If you're using a For Each loop to iterate through the messages in the Inbox (and that's the best way to do it), you can't delete or move any messages before the loop has gone through all the messages. To understand why, let's look at a code example that deletes messages before the loop is finished:

For Each msg In myNS.GetDefaultFolder(olFolderInbox).Items
   If msg.Subject = "Win Free Money" Then
     msg.Delete
   End If
Next

This code looks like it will work, but deleting or moving messages before the For Each loop is finished can cause the loop to miss messages, leading to incorrect and unpredictable results. Let's say the third message encountered in the loop is a "Win Free Money" message. The Delete method will remove the message as expected. What you might not expect is that what was once the fourth message is now the third message (because of the deletion). Meanwhile the loop moves on to the fourth message (formerly the fifth message). As you've probably noticed, the "new" third message will not be checked by this For Each loop. So, even if it were a "Win Free Money" message, it won't be deleted.

The problem is avoided by using an array to keep track of the messages to be deleted or moved, and then performing the actual delete or move operations after the For Each loop has iterated through all the messages in the Inbox. You can see how this can be implemented in Listing One.

Putting It All Together

You now have all the tools necessary to create an e-mail processing procedure in VBA. The procedure that I created is shown in Listing One. As I mentioned previously, this is a relatively simple example that does only two things: 1) Deletes any message with the word "free" in the subject; and 2) Moves any messages from "Linda Cooper" to the "Immediate Attention" folder. All other messages are ignored, as are any schedule items that may be present in the Inbox.

I'm sure that you can think of many ways to expand and improve this procedure. One possibility is to display any message whose importance is set to high, so it will immediately be brought to the user's attention. Or, messages that are more than a week old could be moved to an "Old Mail" folder. The possibilities are endless.

Running the Procedure Automatically

To do its job, the ProcessEmail procedure must be executed. This can be done manually by the user, but that wouldn't be very convenient. After all, the goal is to automate e-mail handling!

The answer lies in making use of one of Outlook's events, the NewMail event. This event is triggered automatically every time new mail messages arrive, and each time the event is triggered the associated event procedure is automatically executed. By placing a call to the ProcessEmail procedure in the NewMail event procedure, you can ensure that new e-mail is automatically processed as soon as it arrives. Here are the required steps:

1)        At the top of the code editing window in the Outlook VBE, there are two drop-down lists. Open the left list and select Application.

2)        Open the right list and select NewMail. The editor will display the NewMail event procedure. It will be empty, consisting only of the first and last lines.

3)        Add a call to the ProcessEmail procedure to the event procedure. This call consists simply of the procedure name. When you're done, the event procedure will look like this:

Private Sub Application_NewMail()
  ProcessEmail
End Sub

That's all there is to it. When new mail arrives, your procedure will be executed, and the e-mail will be processed.

Conclusion

Can you use an e-mail processing VBA procedure along with the Rules Wizard? You sure can. In this situation, the Rules Wizard takes precedence; its rules will be applied to new messages before the VBA procedure runs.

Also remember that Outlook's security settings affect the running of macros. You can view and change the macro security setting in the Security dialog box (select Tools | Macro | Security). To maintain security while permitting your code to run, the preferred approach is to digitally sign your macros. You can find further information about this in the online help.

Peter Aitken has been writing about computers and programming for over 10 years, with some 30 books and hundreds of magazine and trade publication articles to his credit. Recent titles include Developing Office Solutions with Office 2000 Components and VBA (Prentice Hall, 2000) and Teach Yourself Internet Programming with Visual Basic in 21 Days (SAMS, 1998). Peter is the proprietor of PGA Consulting (http://www.pgacon.com), providing custom application and Internet development to business, academia, and government since 1994. You can reach him at mailto: peter@pgacon.com.

Begin Listing One - Automating e-mail

Public Sub ProcessEmail()
   Dim myNS            As NameSpace
   Dim myInbox         As MAPIFolder
   Dim f1              As MAPIFolder
   Dim f2              As MAPIFolder
   Dim dest            As MAPIFolder
   Dim msg             As Object
   Dim ToBeDeleted()   As MailItem
   Dim ToBeMoved()     As MailItem
   Dim NumberToDelete As Integer
   Dim NumberToMove    As Integer
   Dim idx             As Integer
  
   ' Initialize variables.
  NumberToDelete = 0
  NumberToMove = 0
   ' Get a reference to the Inbox. 
   Set myNS = GetNamespace("MAPI") 
   Set myInbox = myNS.GetDefaultFolder(olFolderInbox) 
   ' Get a reference to the destination folder. 
   Set dest = Nothing
   For Each f1 In myNS.Folders
     For Each f2 In f1.Folders
       If f2.Name = "Immediate Attention" Then
         Set dest = f2
       End If
     Next
   Next
   ' If the destination folder wasn' t found, display
   ' a message and then clean up and exit. 
   If dest Is Nothing Then
    MsgBox "The destination folder does not exist." 
     Set f1 = Nothing
     Set f2 = Nothing
     Set myNS = Nothing
     Set myInbox = Nothing
     Exit Sub
   End If
  
   ' Loop through all messages in the Inbox. 
   For Each msg In myInbox.Items
     ' Process only mail messages. 
   If TypeOf msg Is MailItem Then
     ' If the subject contains "free", mark the
     ' message for deletion. 
     If InStr(1, msg.Subject, "free", _
             vbTextCompare) > 0 Then
      NumberToDelete = NumberToDelete + 1
       ReDim Preserve ToBeDeleted(NumberToDelete) 
       Set ToBeDeleted(NumberToDelete) = msg
     ' If the message is from "Linda Cooper",
     ' mark it to be moved. 
     ElseIf msg.SenderName = "Linda Cooper" Then
      NumberToMove = NumberToMove + 1
       ReDim Preserve ToBeMoved(NumberToMove) 
       Set ToBeMoved(NumberToMove) = msg
     End If
  End If
   Next
   ' Delete messages marked for deletion (if any).
   If NumberToDelete > 0 Then
     For idx = 1 To NumberToDelete
      ToBeDeleted(idx).Delete
     Next
   End If
   ' Move messages marked to be moved (if any). 
   If NumberToMove > 0 Then
     For idx = 1 To NumberToMove
      ToBeMoved(idx).Move dest
     Next
   End If
   ' Clean up. 
   Set myNS = Nothing
   Set myInbox = Nothing
   Set f1 = Nothing
   Set f2 = Nothing
   Set dest = Nothing
   Set msg = Nothing
  
End Sub

End Listing One