Extending Outlook Rules via Scripting: Microsoft, Office, Outlook, VBA, Macro, Rules

Scenario. Outlook rules are a great way to automate message processing. Using rules you can organize your messages, forward or reply to messages automatically, display custom message alerts, set reminders, and more. Unfortunately, rules have two severe limitations. First, if your mailbox is on an Exchange server, then there is a limit (32K) to the aggregate size of all your rules. If you reach that limit, then the only way to create another rule is to delete an existing one. Second, Outlook's built-in rules wizard offers a limited set of conditions and actions. If you want your rule to key on a condition that isn't offered, or perform and action that isn't in the list, then you are stuck.

Solution. The solution to both limitations is to employ Outlook's ability to have a rule run a script. By calling a script your rule can perform almost any action, or at least any task that you can script. Scripts give you access to all of the commands available in Microsoft's Visual Basic for Applications (VBA) language, which includes interacting with other Microsoft Office applications. It also provides for making calls to any COM component available to Visual Basic. Scripting enables you to combine multiple rules into a single rule (e.g. multiple rules that file messages by sender can be combined), create conditions that aren't possible via the rules wizard (e.g. the rule's wizard only supports AND conditions, not OR, and not NOT), and perform actions well beyond those available through the wizard.

Requirements. The ability to call a script from a rule is available to Outlook 2002 and later. Obviously you must be familiar enough with writing VBA code to be able to write the script. The only other requirement is that Outlook must be running for the rule and script to work. Rules that call scripts run at the client (i.e. Outlook), not the mail server (i.e. Exchange). If Outlook is not running, then the rule will not fire and run the code.

1: Creating the Script

For a script to be available to Outlook's rules engine it must be properly formatted. Here's an example of a properly formatted script that can be called from a rule. The key is the Item As Outlook.MailItem. That declaration tells the script to accept a lone parameter, the message triggering the script. Scripts without this declaration cannot be called from a rule.

    Sub MyRule(Item As Outlook.MailItem)
        'Your custom VBA code goes here'
    End Sub

1:
2:
3:

Select all Open in new window

To create a script,

a. Click Tools > Macro > Visual Basic Editor, or press ALT+F11.
b. If not already expanded, expand Project1 in the project pane.
c. If not already expanded, expand Modules in the project pane.
d. If a module already exists in modules, then select it and skip to step f.
e. Right-click on Modules and select Insert > Module. Select the newly created module.
f. Copy the code in the snippet above and paste it into the right-hand pane of the VB editor.g7. Change

MyRule

to the name you want to give the script.
g. Replace

'Your custom VBA code goes here'

with your code.

2: Creating the Rule

a. Click Tools > Rules and Alerts.
b. Click the New Rule button.
c. Select "Start from a blank rule" and click Next.
d. The first step to creating the rule is to set the condition that triggers the rule. You have a choice here. Create a condition, or leave the condition blank in which case the rule will fire for all messages. The latter is the option to choose if you want the script to control the condition. For example, if you want to create a single rule that will file messages for multiple senders, then leave the condition blank and write the script to handle multiple senders. If instead you want to perform an action that isn't available via the wizard for a single sender, then go ahead and set a condition. Click Next.
e. Select the "run a script" action.
f. In the lower panel click "a script" and select the script you created in step 1.
g. Click Finish.
h. Make sure the box next to the rule is checked to make it active.

3: Running the Rule

Each time a new message arrives that meets the condition set in the rule, or all messages if no condition was set, the rule fires the script and passes it a copy of the message that triggered the rule. (Note that rules only work for messages. They do not work from task requests, meeting requests, receipts (delivery or read), or non-delivery reports. Although these look like message they are not.) The script then runs and performs whatever actions you wrote it to take. You can also run these rules on demand just as you can with normal rules.

Examples.

1. This example demonstrates how to assign a category to messages based on the sender's name.

Sub CategorizeBySender(Item As Outlook.MailItem)
    Select Case LCase(Item.SenderName)
        Case "bob jones", "sam smith", "jane doe"
            Item.Categories = "Business"
        Case "tom davis", "mary wilson"
            Item.Categories = "Friends"
        Case Else
            Item.Categories = "Unknown"
    End Select
    Item.Save
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

Select all Open in new window

2. This example demonstrates saving all message attachments to a folder in the file system.

Sub SaveAttachmentsToFolder(Item As Outlook.MailItem)
    Dim objFSO As Object, _
        olkAttachment As Outlook.Attachment, _
        strFolderName As String
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    For Each olkAttachment In Item.Attachments
        'Change the root folder path on the following line as needed'
        strFolderName = "C:\RootFolder\" & objFSO.GetExtensionName(olkAttachment.FILENAME)
        If Not objFSO.FolderExists(strFolderName) Then
            objFSO.CreateFolder strFolderName
        End If
        olkAttachment.SaveAsFile strFolderName & "\" & olkAttachment.FILENAME
    Next
    Set objFSO = Nothing
    Set olkAttachment = Nothing
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Select all Open in new window

3. This example demonstrates creating a contact for the sender of all messages you receive.

Sub AutoCreateContact(Item As Outlook.MailItem)
    Dim olkContacts As Object, _
        olkContact As ContactItem, _
        strAddress As String, _
        strName As String
    Set olkContacts = Application.Session.GetDefaultFolder(olFolderContacts)
    strName = Item.SenderName
    strAddress = Item.SenderEmailAddress
    Set olkContact = olkContacts.Items.Find("[FullName] = " & Chr(34) & strName & Chr(34))
    If TypeName(olkContact) = "Nothing" Then
        If Item.SenderEmailType = "SMTP" Then
            Set olkContact = Application.CreateItem(olContactItem)
            With olkContact
                .Email1Address = strAddress
                .FullName = strName
                'Change or remove the following line as desired'
                .Body = "Record created automatically on " & Date & " at " & Time & " by BlueDevilFan's script."
                'Create a similar IF ... END IF sequence for each category you want to add'
                If InStr(1, Item.Body, "TechRepublic") Then
                    .Categories = AddCategory(.Categories, "TechRepublic")
                End If
                .Save
            End With
        End If
    End If
    Set olkContact = Nothing
    Set olkContacts = Nothing
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:

Select all Open in new window

Links to Other BlueDevilFan Articles

1. Creating Linked Notes in Outlook 2007
2. Importing and Exporting Outlook 2007 Categories
3. Outlook 2007 Corporate Categories System
4. Automatically Printing/Saving Emails/Attachments in Outlook
5. Avoiding Blank Subject Lines in Outlook
6. Never Again Forget to Add that Attachment to your Outlook Email
7. Enhancing Outlook 2007 Meeting Reminders

Extending Outlook Rules via Scripting

Creating the Script

Creating the Rule

Running the Rule

Microsoft

Office

Outlook

VBA

Macro

Rules

Outlook Groupware Software

About The Author

More Articles From BlueDevilFan

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Outlook Experts