Manitos de Plata

The rules explained

2012-02-06T18:31:00.000-08:00

OutlookRules Creator

Have you ever had to deal withfar too many emails, all unsorted? Is your inbox filled will all sorts ofimportant messages, but they are so disorganized you will never catch up? Haveyou ever wished there was a quick and easy way to programmatically create Outlookrules and folders in one fell swoop? If you now find yourself lookingfor a way to run a single script that will create your rules and folders on the Exchange server—which will then continue filtering your emails everydayas it would if you had manually added each individual rule—you have stumbledupon the right website, my friend. Please read on.

Why did I create this script?

The reason I created these rulesis that in my current job, I have noticed my co-workers experience issues withmail rules. The end result was always missing emails due to the wrong rulecatching them and moving them to the wrong folder. Email rules are no easytask; they are a pseudo programming language which processes the rulessequentially from top to bottom. Outlook does have a Rule Wizard, but it iscumbersome when it comes to sharing the rules with someone else. The biggestissue I found with the Wizard is that it does not create the folders needed,and the rules are not very easy to maintain.

In what situation would this script be most useful?

The current revision of the rules(v1.24b) is designed for use in an environment in which a pre-determined set ofkeywords is already established and widely used. For example, take any new employee with noprogramming experience in a company with multiple product lines specialized tothe client. The new employees usually are overwhelmed with hundreds of emailsthat flow in unsorted; once rules are in place, the process is simplified.Creating each rule manually, as I have explained, takes time to build and evenlonger to perfect. Using the script I wrote, an experienced employee within thedepartment who knows the keywords to use in the filters can fill in the columnson the spreadsheet and immediately create the filters and folders for the newemployee to use.

How fast are the rules created?

Using this method, creating about60 folders and 120 rules on the Exchange server took between three and 15minutes, depending on the computer and its location. I am not sure of thefactors at play when it comes to speed. The fastest time I measured was on a “cloud”remote desktop computer, and the longest (15 minutes), was on a not verypowerful desktop computer. Compared to the traditional method of adding rulesand folders as you go, which in my experience takes the employee several months,I would call this method a success.

How do the rules work?

It would be best that anexperienced employee, in the same department, who knows most of the keywords onewould need, fill in the Excel spreadsheet. The Excel file contains columns for:the name of the rule, key word or words to seek out, domain from which emails aresent, and words excluded from the subject line filter. In my company, we findit necessary to sort our emails into folders based on which client the emailpertains to. Thus, we benefit from rules that filter email messages thatmention the client in the subject line, and from rules that filter emailscoming from the client’s domain (e.g., if one of the clients is XYZ Industries,emails coming from anything@xyz.com). Ifrules must be created to filter by anything other than subject key words orsender domain, the basic concept here can be expanded, but these are thefilters needed at my company.

There are two rules that arecreated when the Outlook script is run: one is seeking words in the subjectline and the other, in the domain of the email’s sender. In other words, theprogram creates a “subject” rule and a “people” rule for each line that isentered onto the spreadsheet in Excel. Note that there must be two rulescreated per line because if we combined them into a single rule, both conditionswould have to be true in order for a message to be filtered. Outlook does notallow for rules that sort based on a match in the subject OR a match in thesender.

A few examples are listed as theywould appear in the columns of the Excel spreadsheet.

Name of Rule	Words in Subject	Domain From	Exclude the following words
Intel	pentium xeon intel	intel.com
Mercedes Benz	mercedes benz	mbusa.com	Benzene

The above view displays the final product of the example rule mentionedpreviously. The script adds an underscore plus a “p” or an “s” for people orsubject, respectively.

What if the rules do not sort as intended?

Once created, the same file canbe modified and tweaked as needed and shared again and again as new employeesare set up with email access. Each new employee is thus receiving theup-to-date version of a department-wide or company-wide email filtering system.If a new set of rules was to be implemented then the current rules could bedeleted and the script re-run. The folders will not be re-created, only therules.

Limitations and final comments

By design, the rules run in the order listed on the Excel spreadsheet. Ielected to sort them alphabetically for readability and ease of troubleshooting.There is a known size limitation on rules in Outlook* of 32kB, but this can beincreased to 256kB in Exchange 2007 and higher. One way to minimize the size ofrules is number rather than name the rules.

When running these rules for the first time, I would recommend setting upan alternate profile in Outlook on which you set up a “test” pop account. Thebenefits of this are clear: rules are created in a separate environment andthere is no lag time associated with updating the exchange server. Once yourrules are confirmed successful, run them on your regular account. I have usedthis script to create folders and filters successfully in Exchange 2007 usingOutlook 2007 and Outlook 2010 clients. I hope it serves you well.

*Per Microsoft KB886616

Automated outlook rules

2012-01-23T18:51:00.000-08:00

The attached file (see download link provided) can be used for creating rules in MS Outlook by filling in the information in the chart, which will filter your emails based on key words in the subject line OR the sender's email domain (@yahoo.com, @davgar.com, etc), and makes exceptions for keywords you want to avoid being filtered. To use this file, follow the steps below.

In Outlook, enable macros. Click Tools > Macro > Security > Warnings for all macros (radio button) and OK. *Note: These steps will be slightly different if you are using Outlook 2010.
In Outlook, open the Visual Basic Editor via Tools > Macro > Visual Basic Editor or ALT+F11.
Once in the Editor go to Tools > References and select Microsoft Excel 12 Object Library (office 2007 – It will be Microsoft Excel 14 Object Library if you are using MS Outlook 2010). Click OK.
Close the Editor and Outlook.
Open Outlook again, go back into the Editor. Right-click Project 1, select Create Module (if module is not already there). Copy and paste all the code inside the module. You will find the code you need below.
Make sure the .xls file is in C:/Outlook/ folder. If folder is not present, then create it.
In the Editor, go to Run > Run Sub/User form or Click on F5 to run code.

Thank you JPSoftware for folder creation code taken from here.

This code can be downloaded from the project page.

Microsoft Library bb206765

   1:  'Code written by David Gardner ------- davgar@gmail.com

   2:  'v1.24b 01/23/2012

   3:  '

   4:  'It is recommended that you backup your existing rules by using the export rules feature

   5:  '

   6:  ' In order for this code to work you must:

   7:  ' 1)In Outlook, enable macros. Click Tools > Macro > Security > Warnings for all macros (radio button) and OK.

   8:  ' *Note: These steps will be slightly different if you are using Outlook 2010.

   9:  ' 2) In Outlook, open the Visual Basic Editor via Tools > Macro > Visual Basic Editor or ALT+F11.

  10:  ' 3) Once in the Editor go to Tools > References and select Microsoft Excel 12 Object Library

  11:  ' (office 2007 – It will be Microsoft Excel 14 Object Library if you are using MS Outlook 2010).

  12:  ' Click OK.

  13:  ' 4) Close the Editor and Outlook.

  14:  ' 5) Open Outlook again, go back into the Editor. Right-click Project 1, select Create Module

  15:   ' (if module is not already there).  Copy and paste all the code inside the module.

  16:  ' 6) Make sure the .xls file is in C:/Outlook/ folder. If folder is not present, then create it.

  17:  ' 7) In the Editor, go to Run > Run Sub/User form or Click on F5 to run code.

18:

19:

20:

21:

  22:  Sub Main()

23:

  24:  Dim arrData As Variant

  25:  Dim xlApp As Excel.Application

  26:  Dim xlWorkbook As Excel.Workbook

27:

  28:  Dim x As Integer        'Used to cycle trough the rows of the spreadsheet

29:

  30:  Dim colRules As Outlook.Rules

  31:  Dim oRule As Outlook.Rule

  32:  Dim colRuleActions As Outlook.RuleActions

  33:  Dim oMoveRuleAction As Outlook.MoveOrCopyRuleAction

  34:  Dim oExceptSubject As Outlook.TextRuleCondition

  35:  Dim oIncludeSubject As Outlook.TextRuleCondition

  36:  Dim oSendersAddress As Outlook.AddressRuleCondition

37:

  38:  Dim oInbox As Outlook.Folder

  39:  Dim oMoveTarget As Outlook.Folder

  40:  Dim arrSubject() As String

  41:  Dim arrSendersAddress() As String

  42:  Dim arrExeptions() As String

43:

44:

  45:      ' Open up Excel but do not show it

  46:      Set xlApp = New Excel.Application

  47:      xlApp.Visible = False

48:

  49:      ' load the spreadsheet from the following location

  50:      Set xlWorkbook = xlApp.Workbooks.Open("c:\Outlook\rules.xls", ReadOnly = True)

51:

  52:      'Specify the data range to be used

  53:      arrData = xlApp.Sheets("Sheet1").Range("A6").CurrentRegion.Value

54:

  55:      'Dereference variables and Quit Excel

  56:      xlApp.Quit

  57:      Set xlWorkbook = Nothing

  58:      Set xlApp = Nothing

59:

  60:  'Start from high to low so that alphabetical order is preserved

  61:  For x = UBound(arrData) To LBound(arrData) +1 Step -1

62:

  63:      'remove any spaces from the beginning or the end of the string

  64:      arrData(x, 1) = Trim(arrData(x, 1))

  65:      arrData(x, 2) = Trim(arrData(x, 2))

  66:      arrData(x, 3) = Trim(arrData(x, 3))

  67:      arrData(x, 4) = Trim(arrData(x, 4))

68:

  69:      'If folder doesn't exist create it and continue

  70:      If CheckForFolder(arrData(x, 1)) = False Then

  71:          Set MyFolder = CreateSubFolder(arrData(x, 1))

  72:      End If

73:

  74:      'Specify target folder for rule move action

  75:      Set oInbox = Application.Session.GetDefaultFolder(olFolderInbox)

76:

  77:      'Set the target folder for the rule

  78:      Set oMoveTarget = oInbox.Folders(arrData(x, 1))

79:

  80:     'Get Rules from Session.DefaultStore object

  81:      Set colRules = Application.Session.DefaultStore.GetRules()

82:

83:

  84:     '***********************************************

  85:     'Create the Subjects Rule if both fields are not empty

  86:  If Len(arrData(x, 1)) > 0 Then

  87:  If Len(arrData(x, 2)) > 0 Then

88:

  89:     Set oRule = colRules.Create(arrData(x, 1) & "_S", olRuleReceive)

90:

  91:     'Specify condition of containing words in subject

  92:     'Obtain data in the second column of the spreadsheet and parse it

  93:      Set oIncludeSubject = oRule.Conditions.Subject

  94:      With oIncludeSubject

  95:          .Enabled = True

  96:          arrSubject = Split(arrData(x, 2))

  97:          .Text = arrSubject

  98:      End With

99:

 100:      'Specify the action in a MoveOrCopyRuleAction object

 101:      'Action is to move the message to the target folder

 102:      Set oMoveRuleAction = oRule.Actions.MoveToFolder

 103:      With oMoveRuleAction

 104:          .Enabled = True

 105:          .Folder = oMoveTarget

 106:      End With

 107:

 108:      'Specify the exception condition for the subject in a TextRuleCondition object

 109:      'If the data in column 4 is not empty then parse words into the exeption field

 110:      If Len(arrData(x, 4)) > 0 Then

 111:          Set oExceptSubject = oRule.Exceptions.Subject

 112:          With oExceptSubject

 113:              .Enabled = True

 114:              arrExeptions = Split(arrData(x, 4))

 115:              .Text = arrExeptions

 116:          End With

 117:      End If

 118:  End If

 119:  End If

 120:      '********************************************************

 121:      'Create the People rules if sendersAddress if present

 122:  If Len(arrData(x, 1)) > 0 Then

 123:  If Len(arrData(x, 3)) > 0 Then

 124:      Set oRule = colRules.Create(arrData(x, 1) & "_P", olRuleReceive)

 125:

 126:      'Specify the condition in a ToOrFromRuleCondition object

 127:      'Condition is if the message is from "Dan Wilson"

 128:          Set oSendersAddress = oRule.Conditions.SenderAddress

 129:          With oSendersAddress

 130:              .Enabled = True

 131:              arrSendersAddress = Split(arrData(x, 3))

 132:              .Address = arrSendersAddress

 133:          End With

 134:

 135:      'Specify the action in a MoveOrCopyRuleAction object

 136:      'Action is to move the message to the target folder

 137:      Set oMoveRuleAction = oRule.Actions.MoveToFolder

 138:      With oMoveRuleAction

 139:          .Enabled = True

 140:          .Folder = oMoveTarget

 141:      End With

 142:  End If

 143:  End If

 144:

 145:     'Update the server rules

 146:      colRules.Save

 147:

 148:      'Dereference the rest of the variables

 149:      Set oInbox = Nothing

 150:      Set oMoveTarget = Nothing

 151:

 152:      Set colRules = Nothing

 153:      Set oRule = Nothing

 154:      Set oIncludeSubject = Nothing

 155:      Set oMoveRuleAction = Nothing

 156:      Set oExceptSubject = Nothing

 157:      Set oSendersAddress = Nothing

 158:

 159:      Erase arrSubject

 160:      Erase arrSendersAddress

 161:

 162:      Next x

 163:

 164:      Erase arrData

 165:

 166:

 167:  End Sub

 168:

 169:  Function CheckForFolder(ByVal strFolder As String) As Boolean

 170:  ' looks for subfolder of specified folder, returns TRUE if folder exists.

 171:  Dim olApp As Outlook.Application

 172:  Dim olNS As Outlook.NameSpace

 173:  Dim olInbox As Outlook.MAPIFolder

 174:  Dim FolderToCheck As Outlook.MAPIFolder

 175:

 176:  Set olApp = Outlook.Application

 177:  Set olNS = olApp.GetNamespace("MAPI")

 178:  Set olInbox = olNS.GetDefaultFolder(olFolderInbox)

 179:

 180:  ' try to set an object reference to specified folder

 181:  On Error Resume Next

 182:  Set FolderToCheck = olInbox.Folders(strFolder)

 183:  On Error GoTo 0

 184:

 185:  If Not FolderToCheck Is Nothing Then

 186:    CheckForFolder = True

 187:  End If

 188:

 189:  ExitProc:

 190:  Set FolderToCheck = Nothing

 191:  Set olInbox = Nothing

 192:  Set olNS = Nothing

 193:  Set olApp = Nothing

 194:  End Function

 195:

 196:  Function CreateSubFolder(ByVal strFolder As String) As Outlook.MAPIFolder

 197:  ' assumes folder doesn't exist, so only call if calling sub knows that

 198:  ' the folder doesn't exist; returns a folder object to calling sub

 199:  Dim olApp As Outlook.Application

 200:  Dim olNS As Outlook.NameSpace

 201:  Dim olInbox As Outlook.MAPIFolder

 202:

 203:  Set olApp = Outlook.Application

 204:  Set olNS = olApp.GetNamespace("MAPI")

 205:  Set olInbox = olNS.GetDefaultFolder(olFolderInbox)

 206:

 207:  Set CreateSubFolder = olInbox.Folders.Add(strFolder)

 208:

 209:  ExitProc:

 210:  Set olInbox = Nothing

 211:  Set olNS = Nothing

 212:  Set olApp = Nothing

 213:  End Function