Use a macro to create a rule to move messages

This code sample is in response to ASIF, who posted a question at OutlookForums:

I wanted to create a rule which will create "BLUE-July 20 - 26" sub-folder under SKY folder on every Sunday 1 AM . This should be created every Sunday. I want mails to transfer to the above folder to the week they belong to as soon as they arrive to inbox.

This is doable using VBA, however, one requirement is that Outlook needs to be running Sunday at 1 AM.

Create an appointment with the subject "Create new Blue folder" and a color category called "Update Sky". Set appointment time and add a reminder that fires at 1 AM every Sunday (I'm using a 0 minute reminder.) If you use a different subject or category, you'll need to update the macro. Don't forget, it's case sensitive.

In ThisOutlookSession, create the application reminder rule to run the CreateFolderRule macro. The BeforeReminderShow macro dismisses the reminder in this occurrence.

Private WithEvents olRemind As Outlook.Reminders
Private Sub Application_Reminder(ByVal Item As Object)
 Set olRemind = Outlook.Reminders

If Item.MessageClass <> "IPM.Appointment" Then
  Exit Sub
End If
 
If Item.Categories <> "Update Sky" Then
  Exit Sub
End If
  
  CreateFolderRule
    
End Sub

Private Sub olRemind_BeforeReminderShow(Cancel As Boolean)

    For Each objRem In olRemind
            If objRem.Caption = "Create new Blue folder" Then
                If objRem.IsVisible Then
                    objRem.Dismiss
                    Cancel = True
                End If
                Exit For
            End If
        Next objRem
End Sub

Right click on ThisOutlookSession and choose Insert > Module. Paste the following code into the new module.

Sub CreateFolderRule()
 
 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 oFolder As Outlook.Folder
 Dim oMoveTarget As Outlook.Folder
 

 Dim BlueDate As String
' Create the folder name
BlueDate = "Blue " & Format(Date, "MMMM dd") & "-" & Format(Date + 7, "dd")

 'Specify target folder for rule move action
 'Set oFolder = Application.Session.GetDefaultFolder(olFolderInbox)
 
 Set oFolder = Application.Session.GetDefaultFolder(olFolderInbox).Parent.Folders("Sky")
 
' Set the folder, if it doesn't exist, create it
 On Error Resume Next
 Set oMoveTarget = oFolder.Folders(BlueDate)
 
If oMoveTarget Is Nothing Then
    Set oMoveTarget = oFolder.Folders.Add(BlueDate)
End If

 'Get Rules from Session.DefaultStore object
 Set colRules = Application.Session.DefaultStore.GetRules()
 
 'Create the rule by adding a Receive Rule to Rules collection
 Set oRule = colRules.Create("Move to Sky", olRuleReceive)
 
' We're moving all messages; if you wanted to set conditions, 
' do it here. 
' Set oFromCondition = oRule.Conditions.From
' With oFromCondition
' .Enabled = True
' .Recipients.Add ("Diane Poremsky")
' .Recipients.ResolveAll
' End With
 

'Action is to move the message to the target folder
 Set oMoveRuleAction = oRule.Actions.MoveToFolder
 With oMoveRuleAction
 .Enabled = True
 .Folder = oMoveTarget
 End With
 

' We aren't using exceptions, but this is where exceptions would be set
' Set oExceptSubject = oRule.Exceptions.Subject
' With oExceptSubject
' .Enabled = True
' .Text = Array("fun", "chat")
' End With
 
 'Update the server
 colRules.Save
 
End Sub

How to use macros

First: You will need macro security set to low during testing.

To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.

After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.

Open the VBA Editor by pressing Alt+F11 on your keyboard.

To use the macro code in ThisOutlookSession:

1. Expand Project1 and double click on ThisOutlookSession.
2. Copy then paste the macro into ThisOutlookSession. (Click within the code, Select All using Ctrl+A, Ctrl+C to copy, Ctrl+V to paste.)

To put the code in a module:

1. Right click on Project1 and choose Insert > Module
2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor