Click here to Skip to main content
15,039,359 members
Articles / Productivity Apps and Services / Microsoft Office / Office Automation
Article
Posted 2 Mar 2017

Stats

12.9K views
265 downloads
6 bookmarked

Using VBA to run Outlook Rules for one or more accounts

Rate me:
Please Sign up or sign in to vote.
5.00/5 (7 votes)
3 Mar 2017CPOL6 min read
Run all rules for all accounts on all messages; also useful as Outlook Rule debug tool.

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:

Outlook's Run Rules Now dialog

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:

VB.NET
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

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

Change Log

  • 2nd 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Dracula
United States United States
I've been writing code as a hobby or for a living since 1970, working in machine code (octal input on a Compucorp programmable calculator), Assembler, Fortran (various flavors), Basic, APL, C, C++, Java, C#, PL/1 and others over the years. OS's used (sometimes as little as possible) include none (when writing machine code), IBM System/360 & 370 (with keypunch and TSO), OS/360 (really don't miss JCL), VM/CMS, DG RDOS, Unix, VAX/VMS, PC-DOS, MS-DOS and the many flavors of Windows.

Comments and Discussions

 
QuestionProblems with rules collection object Pin
Member 147934696-Apr-20 8:54
MemberMember 147934696-Apr-20 8:54 
I'm trying to create a macro that creates a rule named off of the currently selected message (but won't create duplicate) and if a new rule created, optionally runs the rule. I'm experiencing 2 different problems with the rules collection object as follows:

1) While looping through rules collection (to check if the rule already exists), it chokes on the <rules collection="">.item(x).name property on rules that it "doesn't like" (whether valid or not). I'm assuming they're valid, since Rules Manager is able to run these rules when selected. I've kludged the error handler to skip over this occurrence, but can't prevent the next one (the err.number is different when I test this on another computer with a slightly different version of Outlook).

2) Even after either getting around that rule in the rules collection it doesn't like (either via error handler or by deleting the rule via Rules Manager), and the rule is saved, it still fails to run the rule.execute method on a search folder (i.e 'Unread Mail' and randomly, failing to run off of a 'search' result). It only seems to work properly off of a named folder from the folder list. Again, as I stated earlier, Outlook's built-in Rules Manager has no problem running that rule. I even tried different parameters for rules.execute (like adding IncludeSubfolders:=true), although that is meaningless, since the default setting when running the rule via Rules Manager is IncludeSubfolders:=false.

Here's the code:
Sub CreateRuleFromSelectedSender()
 
Dim colRules As Outlook.rules
Dim oRule As Outlook.Rule
Dim colRuleActions As Outlook.RuleActions
Dim oMoveRuleAction As Outlook.MoveOrCopyRuleAction
Dim oFromCondition As Outlook.ToOrFromRuleCondition
'Dim oExceptSubject As Outlook.TextRuleCondition
'Dim olSession As Outlook.NameSpace
Dim oMoveTarget, oCurrFolder As Outlook.folder
Dim oMsg As Outlook.MailItem
Dim strSenderEmail As String
Dim i, iMsgResponse As Integer
Dim bRuleFound As Boolean
  
On Error GoTo ErrHandler

Set olApp = Outlook.Application
Set olSession = olApp.GetNamespace("MAPI")
Set oMoveTarget = olSession.PickFolder
If oMoveTarget Is Nothing Then
    Set olApp = Nothing
    Set olSession = Nothing
    Exit Sub
End If

'Retrieve currently selected email message (or opened message)
Set oMsg = GetCurrentItem()

'Get Rules from Session.DefaultStore object
Set colRules = Application.Session.DefaultStore.GetRules()              'retrieve rules collection
Set oCurrFolder = Outlook.Application.ActiveExplorer.CurrentFolder      'retrieve currently selected folder (used when executing rule)
strSenderEmail = oMsg.SenderEmailAddress

'Determine if rule already exists for the sender's email address (don't want to create a duplicate)
bRuleFound = False
i = 1
Do While Not (bRuleFound) And i <= colRules.Count
        'Look for rule named after strSenderEmail
'        Debug.Print colRules.Item(i).Name
        If colRules.Item(i).Name = strSenderEmail Then
            'If Rule name found, mark it as found and exit
            bRuleFound = True
            colRules.Item(i).Execute True, oCurrFolder, True
        Else
Skip:            i = i + 1
        End If
Loop

'Create the rule by adding a Receive Rule to Rules collection, only if this doesn't already exist for this email address
If Not (bRuleFound) Then
    Set oRule = colRules.Create(strSenderEmail, olRuleReceive)
    Set oFromCondition = oRule.Conditions.From
    With oFromCondition
       .Enabled = True
       .Recipients.Add (strSenderEmail)
       .Recipients.ResolveAll
    End With
    'Specify the action in a MoveOrCopyRuleAction object - Action is to move the message to the target folder
    Set oMoveRuleAction = oRule.Actions.MoveToFolder
    With oMoveRuleAction
       .Enabled = True
       .folder = oMoveTarget
    End With
    'Update the rules collection
    colRules.Save
    'add code here to offer the option to run the rule now (and show progress)
    If MsgBox("Run Rules Now?", vbYesNo) = vbYes Then oRule.Execute True, oCurrFolder, True
    'oRule.Execute ShowProgress:=True, folder:=oCurrFolder
End If

Set colRules = Nothing
Set oRule = Nothing
Set oMoveTarget = Nothing
Set oMsg = Nothing
Exit Sub

ErrHandler:
    Debug.Print Err.Number & vbTab & Err.Description & "strSenderEmail = " & strSenderEmail
    If Err.Number <> -2147221233 Then
        iMsgResponse = MsgBox("Fatal error: Error: " & Err.Number & " Description: " & Err.Description & vbCrLf & vbCrLf & "Rule has been created, but not run", vbOKOnly)
        Set colRules = Nothing
        Set oRule = Nothing
        Set oMoveTarget = Nothing
        Set oMsg = Nothing
        Exit Sub
    End If
        
    Resume Skip

End Sub


modified 11-Apr-20 2:29am.

GeneralMy vote of 5 Pin
Member 123643906-Mar-17 1:30
MemberMember 123643906-Mar-17 1:30 
GeneralRe: My vote of 5 Pin
Dracula21-Mar-17 12:41
MemberDracula21-Mar-17 12:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.