Using Arrays in Outlook macros

Last reviewed on October 16, 2012   —  No comments yet

Carol asked how to add a category to tasks she is creating with the macro at Create a Task from an Email using a Rule.

Using that task macro as the example, it's easy to add a category if you want the same category for all tasks - just place this line before the objTask.Save line:

objTask.Categories = "keyword" 

To assign different categories based on different keywords in the subject, you can use If Statements, repeated as needed for each keyword:

If InStr(Item.Subject, "keyword") Then objTask.Categories = "keyword"

While that works OK for a few keywords, if you are using more than a few keywords, it's much more efficient to use an Array or Case statements.

You can use arrays with any field (not just categories!). To use, you need to assign values to the variable that represents the array, then run the it through the If statement looking for a match.

Note: the Array is case sensitive; you need to use LCase on the field name and lower case letters in the arrays to make it case insensitive.

Dim strCat As String
Dim arrCat As Variant

' Set up the array
arrCat = Array("key1", "key2", "key3", "key4", "key5", "key6", "key7", "key8", "key9")

' Go through the array and look for a match, then do something
For i = LBound(arrCat) To UBound(arrCat)
    If InStr(LCase(Item.Subject), arrCat(i)) Then strCat = arrCat(i)
Next i

' Use the result of the If statement:
 objTask.Categories = strCat

To use two arrays, you need to create a second array, with the same number of entries as the first array. This code sample checks the subject for a word in the array and assigns the category that is in the same position in the second array.

Dim strCat As String
Dim arrSubject As Variant
Dim arrCat As Variant

' Set up the array
arrSubject = Array("key1", "key2", "key3", "key4", "key5", "key6", "key7", "key8", "key9")
arrCat = Array("1", "2", "3", "4", "5", "6", "7", "8", "9")

' Go through the array and look for a match, then do something
For i = LBound(arrSubject) To UBound(arrSubject)
    If InStr(LCase(Item.Subject), arrSubject(i)) Then strCat = arrCat(i)
Next i

Using Case Statements

Instead of using arrays, you can use Case statements. These would be more appropriate when applying one category to multiple values of a field, such as the same category to messages from several different people.

    Select Case LCase(Item.SenderName)
        Case "mary contrary", "john smith", "jane doe"
            Item.Categories = "Business"
        Case "sally sue", "bo peep"
            Item.Categories = "Friends"
        Case Else
            Item.Categories = "Unknown"
    End Select

Array Examples

This macro adds attachments to messages based on the first 4 letters of the file name. All files beginning with 2012 are added to one message, all files with 2013 are added to another.

Written by

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlookforums.

Leave a Reply

If the Post Coment button disappears, press your Tab key.