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
For more information, see Rules.Create Method (Outlook)
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:
- Expand Project1 and double click on ThisOutlookSession.
- 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:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor