Using VBA to run Outlook Rules for one or more accounts

• Download source code - 2.7 KB

Introduction

A small VBA project that runs Outlook local and server-side rules for all active iMap and Exchange accounts. Rules are run on both Read and Unread messages.

Background

I started investigating this when, several months ago, my Exchange rules were no longer running on my Inbox with Outlook 2007. When it reoccured under a cleanly-installed (new profiles and recreated rules) Outlook 2016, I was far from content to be required to launch the Rules & Alerts dialog, select my Exchange account, click Run Rules Now..., place a check next to every rule, then click on Run Now. It was frustrating that, though others had reported this issue, neither Microsoft nor my Hosted Exchange provider could offer guidance.

After much searching, I came across OutlookCode.com, where Sue Mosher had posted an article in 2006 with the title Run all rules against inbox. This was exactly what I'd been looking for. Now I could run my rules with the click of a button I added to the Developer ribbon, or from a similar one added to the Quick Access Toolbar.

After a short while, I realized I wanted this macro to do more. I have eight (yes, really) different email accounts in my standard Outlook profile. One Exchange (the problematic one for rules, oddly enough), a couple of POP3 (no rules on these) and five iMap accounts (most have rules). As an aside, Outlook 2016 is so much better for iMap than was Outlook 2007. I just wish that was also true for running non-server rules.

Why did I want the macro to work with the iMap accounts too? Because I often check email on one or more of my tablets or phone. Once I've read a message, if I forget to mark it as unread, then the rules will not be applied, unless I run them manually and include unread messages (this is also true for the Exchange Inbox). Up until this time, I'd resorted to either manually sorting read messages or manually applying the rules for read messages. Now I had the chance to make it all happen with a single toolbar button.

It wasn't hard to update the code to be more robust (e.g. check for Offline or Cached mode, which leads to errors when you try to enumerate rules) and add multiple account processing. What was tricky was figuring out how to force the rules to run against each account's default inbox (see Points of Interest below for more on this).

One might think, after reviewing the sparse documentation on Outlook.Rule.Execute, that simply enumerating the rules from the DeliveryStore for each account would ensure that the rules were run against that account's Inbox. It took a while for me to understand that one is wrong for thinking that. Worse, I could not find anything on the Web that explained how this should work. Figuring out how the Execute method chose the default folder was the trickiest part of this project.

Using the Code

Download the MyOutlookVBA.zip and extract the MyOutlookVBA.bas file. Open Outlook and press Alt-F11 to load the VBA editor. The default project name is often Project1; the important thing is that next to that, in parenthesis, you should see VbaProject.OTM. Right Click the Modules folder, and select Import File... from the menu. When the Import File Dialog (really File Open, repurporsed) comes up, browse to the location where you extracted MyOutlookVBA.bas, and import it. Then click on the Save File icon in the VBA Editor toolbar (or select from the menu: File/Save VbaProject.otm).

Next, you will need to enable Macro Execution in Outlook's Trust Center. Once that is done, you have a few options. The first thing you will probably want to do, if you haven't already, is to show the Developer Tab on Outlook's Ribbon Bar. To run this macro, you can press the Macros button at the left side of the Developer ribbon and select RunAllInboxRules, customize the ribbon to add a section to the Developer ribbon and create a button to execute RunAllInboxRules, add RunAllInboxRules to the Quick Access Toolbar, or some combination of these. If you don't want the results of RunAllInboxRules output to the directory or file I've chosen, change that in the call to LogInfo (see Additional Subroutines and Tracking Rule Exceptions below). I strongly suggest checking the log file now and again. It will tell you if any rules are failing, something that Outlook's automatic execution of rules (or manual execution via Run all rules against Inbox) does not do. This makes it a simple debugging tool for rules. Placing a breakpoint on the line with rl.Execute will allow one to examine the details of the rule, to try to determine where things are going wrong... good luck with that ;-).

Points of Interest

The critical piece of getting this macro to work was understanding how Rules.Execute works. In the end, it was manual operation of Outlook's rules via the Rules & Alerts dialog that gave me the needed clue. Here is an image of Run Rules Now from Outlook 2016:

Notice the options at the bottom of the dialog: Run in Folder, Include subfolders, Apply rules to. Each of these corresponds to 3 of the 4 parameters one can pass into Rule.Execute. That led me to try the following code:

rl.Execute RuleExecuteOption:=OlRuleExecuteOption.olRuleExecuteAllMessages, _
           Folder:=st.GetDefaultFolder(olFolderInbox)

Note that rl is an Outlook.Rule object, and st is the Outlook.Store object for the current account (the DeliveryStore to be precise). Using GetDefaultFolder for each Store was the key to pointing rl.Execute at the correct Inbox. Otherwise, it seems to access the default Inbox for the active Outlook Profile (Exchange, in my case).

Additional Subroutines and Tracking Rule Execution

The original macro called MsgBox to display the list of executed rules (that code is still present, albeit commented out). However, once I added multiple accounts (and with the number of rules I have defined), the limits of MsgBox's text display cropped too much data. Plus, I did not like a MsgBox popping up each time I ran my rules. I did want to know if there were any rule execution problems (or to note if any rules are disabled). That led me to add the LogInfo subroutine. This simply outputs the ruleList text to a text file (the folder location is passed as an argument (I use "%USERPROFILE%\My Documents\Email\"). Note that the trailing backslash is required. LogFile will create the final directory in the path, if required. All parent directories must exist, else LogFile will fail with an error.

Important Notes

It is very important that when you import the VBA code into Outlook, you put the code in the Modules folder under VbaProject.otm. You will also need to go to the Tools/Trust center in Outlook and either enable macros with Warnings for All Macros, or use No Security Check (the latter option is dangerous).

You must ensure that you reference the Microsoft Office Object library for your Office version, as well as the Outlook Object library before you run this code. From the VBA Editor, click on Tools/References. For Outlook 2016, the required libraries are:

• Microsoft Outlook 16.0 Object library
• Microsoft Office 16.0 Object library

For my configuration, I also have these references:

• OLE Automation // needed for the LogInfo sub
• Visual Basic for Applications

History

• 2^nd March, 2017: Initial release of the article/code

Change Log

• 2^nd March 2017
  • Fixed mangled HTML that showed the HREF text vs. an actual link
  • Typo & minor text formatting fix
• 3rd March 2017 - fixed more typos