Outlook Macros

The rest of this chapter presents a selection of macros for automating tasks in Outlook.

Creating Advanced Rules for Handling Incoming Messages

Outlook's Rules Wizard is a powerful tool for processing incoming messages, but it has some unfortunate limitations. Here are two examples:

To learn how to use the Rules Wizard, see "Using Rules to Process Messages Automatically," p. 190

• Suppose you create a rule where you specify both an address in the "from people or distribution list" condition and a word in the "with specific words in the body" condition. Outlook applies this rule only to a message that satisfies both conditions. However, what if you want to apply the rule to messages that satisfy either condition?

• Suppose you create a rule where you enter two words in the "with specific words in the subject" condition. Outlook applies this rule to any message that contains either word. However, what if you want to apply the rule only to messages that contain both words in the subject?

To work around these limitations, you need to turn to VBA. Specifically, you need to set up the Inbox folder as a programmable object, which enables you to write code that examines every incoming message and applies your own rules.

Open Outlook's VBA Editor, open the default project (VbaProject.OTM), and then open the Microsoft Office Outlook Objects branch. Double-click ThisOutlookSession to open the module window you'll use to enter the code. At the top of the module, add the following statements to declare two global variables:

 Dim ns As NameSpace Private WithEvents inboxItems As Items 

Listing 12.16 shows two event handlers that you also need to add to the module. (An event handler is a Sub procedure that runs when a particular event fires. For example, Outlook's Application object has a Startup event that fires each time you launch Outlook.)

Listing 12.16. Event Handlers for Outlook's Startup and Quit Events

 Private Sub Application_Startup()     '     ' Use the MAPI namespace object     '     Set ns = Application.GetNamespace("MAPI")     '     ' Get the Inbox Items object     '     Set inboxItems = ns.GetDefaultFolder(olFolderInbox).Items End Sub Private Sub Application_Quit()     '     ' Clear the objects to save memory     '     Set inboxItems = Nothing     Set ns = Nothing End Sub 

The Application_Startup event handler runs automatically each time you start Outlook. The procedure initializes two variables: ns, which stores the MAPI NameSpace object that enables you to work with Outlook folders and items, and inboxItems, which stores the Item objects (messages) in the Inbox folder. The Application_Quit event handler runs when you shut down Outlook, and it sets the inboxItems and ns objects to Nothing to save memory.

Listing 12.17 shows a procedure that implements a couple of custom rules for handling incoming messages.

Listing 12.17. Event Handler for the Inbox Folder's ItemAdd EventUse the Event Handler to Specify Your Custom Rule

 Private Sub inboxItems_ItemAdd(ByVal Item As Object)     Dim topFolder As Outlook.MAPIFolder     Dim rule1Folder As Outlook.MAPIFolder     '     ' Store the Personal Folders folder     '     Set topFolder = ns.Folders("Personal Folders")     '     ' Custom Rule #1     ' Move messages from "[e-mail]"     ' OR with "politics" in the body     '     If Item.SenderEmailAddress = "[e-mail]" _        Or InStr(Item.Body, "politics") <> 0 Then         Set rule1Folder = topFolder.Folders("Politics")         Item.Move rule1Folder     End If     '     ' Custom Rule #2     ' Flag messages with "Conference" AND "2005" in the subject     '     If InStr(Item.Subject, "Conference") <> 0 _        And InStr(Item.Subject, "2005") <> 0 Then         Item.FlagStatus = olFlagMarked         Item.FlagRequest = "Review"         Item.FlagIcon = olBlueFlagIcon         Item.FlagDueBy = Now() + 7         Item.Save     End If End Sub 

This code is the event handler for the Inbox folder's ItemAdd event, which runs automatically each time a new message is added to the Inbox. The procedure is passed the Item object, which represents the message added to the Inbox. The procedure begins by storing the Folder object for Personal Folders in the topFolder variable. Now the code implements two custom rules:

• Custom Rule #1 This is an example of a rule that looks for messages that satisfy one condition or another. In this case, the If test checks to see if the Item object's SenderEmailAddress property equals "[e-mail]" or the Item object's Body property (the message body) contains the word "politics." If either condition is true, the message is moved to the "Politics" folder.

• Custom Rule #2 This is an example of a rule that uses two criteria in a single condition and looks for messages that satisfy both criteria. In this case, the If test checks to see if the Item object's Subject property includes the word "Conference" and the word "2005." If both criteria are true, the code applies a blue "Review" flag to the message and sets the flag to expire seven days from today.

Supplementing a Reminder with an Email Message

If you set up an appointment or task with a reminder, or if you set up a message or contact with a flag that has a due date, Outlook displays a Reminder window that tells you the item is due. That's a useful visual cue, unless you're out of the office or away from your desk, in which case the reminder becomes far less helpful.

If you have email access when you're away, one way to work around this problem is to have Outlook send you an email message when it processes the reminder. The procedure in Listing 12.18 shows you how to set this up.

Listing 12.18. A Procedure to Send an Email Message When Outlook Processes a Reminder

 Private Sub Application_Reminder(ByVal Item As Object)     Dim msg As MailItem     '     ' Create a new message     '     Set msg = Application.CreateItem(olMailItem)     '     ' Set up the message with your address and the reminder subject     '     msg.To = "[e-mail]"     msg.Subject = Item.Subject     msg.Body = "Reminder!" & vbCrLf & vbCrLf     '     ' Set up the message body using properties     ' appropriate to the different reminder types     '     Select Case Item.Class         Case olAppointment             msg.Body = "Appointment Reminder!" & vbCrLf & vbCrLf & _             "Start: " & Item.Start & vbCrLf & _             "End: " & Item.End & vbCrLf & _             "Location: " & Item.Location & vbCrLf & _             "Appointment Details: " & vbCrLf & Item.Body     Case olContact             msg.Body = "Contact Reminder!" & vbCrLf & vbCrLf & _             "Contact: " & Item.FullName & vbCrLf & _             "Company: " & Item.CompanyName & vbCrLf & _             "Phone: " & Item.BusinessTelephoneNumber & vbCrLf & _             "E-mail: " & Item.Email1Address & vbCrLf & _             "Contact Details: " & vbCrLf & Item.Body     Case olMail             msg.Body = "Message Reminder!" & vbCrLf & vbCrLf & _             "Sender: " & Item.SenderName & vbCrLf & _             "E-mail: " & Item.SenderEmailAddress & vbCrLf & _             "Due: " & Item.FlagDueBy & vbCrLf & _             "Flag: " & Item.FlagRequest & vbCrLf & _             "Message Body: " & vbCrLf & Item.Body     Case olTask             msg.Body = "Task Reminder!" & vbCrLf & vbCrLf & _             "Due: " & Item.DueDate & vbCrLf & _             "Status: " & Item.Status & vbCrLf & _             "Task Details: " & vbCrLf & Item.Body     End Select     '     ' Send the message     '     msg.Send     '     ' Release the msg object     '     Set msg = Nothing End Sub 

The Application_Reminder procedure is an event handler that runs whenever Outlook processes a reminder, and the Item variable that's passed to the procedure represents the underlying Outlook item: an appointment, contact, message, or task. The procedure declares a MailItem (message) variable named msg, uses it to store a new message, and then sets up the message's To, Subject, and initial Body properties. Then a Select Case statement processes the four possible Item classes: olAppointment, olContact, olMail, and olTask. In each case, the message body is extended to include data from the item. Finally, the message is sent using the Send method and the msg variable is released.

Adding More Recipients

If you want to send the email to multiple recipients, one option is to use the MailItem object's Cc or Bcc properties. If you prefer to place multiple addresses in the message's To field, use the Recipients.Add method as often as needed, like so:

 msg.Recipients.Add "[e-mail]" 

Prompting to Save Messages in the Sent Items Folder

By default, Outlook saves a copy of all of your outgoing messages in the Sent Items folder. That's a good idea for important messages that you may need to refer to later on. However, we all send out plenty of unimportant messages, too, and there's no sense in saving those messages to Sent Items because doing so not only increases the size of the message store but also makes it harder to find messages in Sent Items. Listing 12.19 shows a procedure that enables you to decide whether Outlook should save an outgoing message to Sent Items.

Listing 12.19. An Event Handler That Asks Whether Outlook Should Save a Copy of an Outgoing Message in the Sent Items Folder

 Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)     Dim result As Integer     '     ' Display the prompt     '     result = MsgBox("Save this message in Sent Items?", vbSystemModal+vbYesNo)     '     ' Check the result     '     If result = vbNo Then         '         ' If the user clicked No, don't save the message in Sent Items         '         Item.DeleteAfterSubmit = True     End If End Sub 

This procedure is an event handler for the ItemSend event, which fires each time you send a message. The message is passed to the handler as the Item object. The procedure uses MsgBox to display a message asking the user whether the message should be saved in Sent Items. If the user clicks No, the result is vbNo and the procedure sets the Item object's DeleteAfterSubmit property to true to bypass saving the message in Sent Items.

Setting Up a Password-Protected Folder

If you use Outlook to exchange messages containing confidential data such as trade secrets, budget plans, payroll details, or proprietary information, the data may be vulnerable to snooping if you leave your desk. You could prevent this by storing the confidential messages in a folder protected by a password, but, unfortunately, Outlook doesn't give you any way to do this. Fortunately, it's fairly easy to set up a password-protected folder using some VBA code.

You begin by adding a global variable to the top of the ThisOutlookSession module:

 Private WithEvents myExplorer As Explorer 

This enables you to trap events for the Explorer object, which represents the Outlook interface. In particular, you want to trap the BeforeFolderSwitch event. This event fires when the user tries to display a different folder. The idea is that because this event fires before the other folder is displayed, you can use the event handler to ask the user for a password. If the correct password isn't given, you cancel the folder switch.

To use the myExplorer object, set myExplorer to Outlook's active Explorer object:

 Set myExplorer = Application.ActiveExplorer 

Place this statement within the Application_Startup event handler (refer to Listing 12.16).

With that done, you can now set up the handler for the BeforeFolderSwitch event, as shown in Listing 12.20.

Listing 12.20. An Event Handler That Asks the User for a Password Before Switching to the "Confidential" Folder

 Private Sub myExplorer_BeforeFolderSwitch(ByVal NewFolder As Object, Cancel As Boolean)     Dim pwd as String     '     ' Are we switching to the "Confidential" folder?     '     If NewFolder.Name = "Confidential" Then         '         ' If so, ask the user for the password         '         pwd = InputBox("Please enter the password for this folder:")         '         ' Check the password         '         If pwd <> "password" Then             '             ' If the password doesn't match, cancel the switch             '             Cancel = True         End If     End If End Sub 

This procedure accepts an argument named NewFolder, which represents the folder to which the user is trying to switch. If this folder's name is "Confidential," the procedure asks the user to enter the password. If the password doesn't match, the Cancel argument is set to TRue, which means the folder isn't displayed.

Change the Password

The code in Listing 12.20 uses the word "password" as the folder password. If you use this code on your system, be sure to change the password to a less obvious word or phrase.

You're probably wondering just how secure this method is if the password appears right in the code. As things stand, it's not very secure at all because a savvy user would know to examine the VBA code. Therefore, you need to prevent others from viewing the project. Here are the steps to follow:

1.	In the VBA Editor, select Tools, Project Properties, where Project is the name of your VbaProject.OTM project (the default name is Project1).
2.	Display the Protection tab, as shown in Figure 12.6. Figure 12.6. Use the Protection tab to prevent unauthorized users from viewing your Outlook VBA code.
3.	Activate the Lock Project for Viewing check box.
4.	Type a password into the Password and Confirm Password text boxes.
5.	Click OK.

Shut down and then restart Outlook to put the password into effect.

From Here

• To learn how to set up a menu command to run a macro, see the section titled "Creating Custom Commands for Macros" in Chapter 10.

• You can also set up toolbar buttons to run macros. In Chapter 10, see the section titled "Creating Custom Toolbars."

• For some VBA basics, see Chapter 11, "Maximizing Office with VBA Macros"

• For other ways to secure Outlook, see Chapter 15, "Enhancing Outlook Email Security and Privacy."