Create a list of color categories and merge or restore the list using VBA

Last reviewed on January 20, 2014   —  9 comments

Applies to: Microsoft Outlook 2013, Outlook 2010, Outlook 2007

The code samples on this page will get the category names, color, and shortcuts assigned. Once you have this list, you can use the second code sample to restore the list to a different computer. If you want a simple list of your categories and color names that are assigned, see Print a list of Outlook Categories and their Colors. For a list of utilities you can use to manage your color categories, see the Tools section at Outlook Categories and Color Categories.

Step 1: Get a formatted list of color categories.
Step 2: Add the formatted list to the AddCategory Macro and run the macro

Begin by running the GetCategoryNames macro. Press Ctrl+G to open the Immediate window then select all and copy the list. Paste the list into Notepad or a plain text email message.

If the categories names are not unique, the macro AddCategory macro will fail. You can use the DeleteCategories macro to remove all categories before adding your list of categories

Run the macro to create a formatted list of color categories


Private Sub GetCategoryNames()
    Dim objNS As NameSpace
    Dim objCat As Category
    Dim strOutput As String
    
    Set objNS = Application.GetNamespace("MAPI")
    
    If objNS.Categories.Count > 0 Then
        
        For Each objCat In objNS.Categories
           strOutput = strOutput & "AddCategory """ & objCat.Name & """, " _
           & objCat.Color & ", " & objCat.ShortcutKey & vbCrLf

' to remove categories as you make a list
' uncomment this line
'objNS.Categories.Remove (objCat.CategoryID)
        Next
    End If
    
    ' Print the list to the Immediate Window
    ' Press Ctrl+G to open it or use View > Immediate Window
   Debug.Print strOutput
    
    ' Clean up.
 Set objCat = Nothing
 Set objNS = Nothing
End Sub

 

Restore the color categories list

Copy the list created by the code above and paste it into this code sample, in place of
AddCategory "category", 17, 0

Add the formatted list to the AddCategories macro and run it

Then run the RestoreCategories macro. The On Error Resume Next line will allow you to add a category list that includes categories already in your master category list, however, the category color won't be updated if the old categories aren't removed.


Public Sub RestoreCategories()

   AddCategory "category", 17, 0

End Sub

Private Sub AddCategory(strCategoryName As String, intColor As Integer, intKey As Integer)
    Dim objNS As NameSpace
 
    Set objNS = Application.GetNamespace("MAPI")
       On Error Resume Next
    objNS.Categories.Add strCategoryName, intColor, intKey
    Set objNS = Nothing
End Sub

Using the GetCategoryNames and RestoreCategories Macros

The following video tutorial shows how to use the two macros above to create a list of your categories and their color then add those categories to Outlook. You can use these macros to share category names and colors with other Outlook users.


Delete color categories

To delete the existing categories and add the categories in your list, paste the following code into the VBA editor and add DeleteCategories as the first line of the RestoreCategories procedure:

Public Sub RestoreCategories()
   DeleteCategories
   AddCategory "category", 17, 0
End Sub

Warning: using the DeleteCategories procedure will delete all categories from the list.

Private Sub DeleteCategories()
    Dim objNS As NameSpace
    Dim objCat As Category
   
    Set objNS = Application.GetNamespace("MAPI")
   
    If objNS.Categories.Count > 0 Then
       
        For Each objCat In objNS.Categories
            objNS.Categories.Remove (objCat.CategoryID)
        Next
       
    End If
       
    Set objCat = Nothing
    Set objNS = Nothing
   
End Sub

While I recommend using the RestoreCategories code if you need to work with a lot of categories, if you want to add or remove a single category within a macro, you can do it with one line (for each action and category).

If you need to change the category color, you'd first remove it then add it back. The categories already assigned are not affected, the category is removed only from the master category list.

Sub AddRemoveCategories()
    Session.Categories.Remove ("Category2")
    Session.Categories.Add "Category2", 13, 0
End Sub

 

Category Colors by number

The following is a list of the category colors and their index number.

Color Index Color Index
Red 1 Dark Gray 14
Orange 2 Black 15
Peach 3 Dark Red 16
Yellow 4 Dark Orange 17
Green 5 Dark Peach 18
Teal 6 Dark Yellow 19
Olive 7 Dark Green 20
Blue 8 Dark Teal 21
Purple 9 Dark Olive 22
Maroon 10 Dark Blue 23
Steel 11 Dark Purple 24
Dark Steel 12 Dark Maroon 25
Gray 13

About Diane Poremsky

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 Outlook forums by Slipstick.com.

9 responses to “Create a list of color categories and merge or restore the list using VBA”

  1. Robert

    When I run the restore portion I get the error, compile error: Wrong number of arguments or invalid property assignment. Help please as the deleted portion has already worked properly and now I have no categories.

    1. Diane Poremsky

      Are any of the lines in red?

      Did you paste the list of categories in this code, each category is one per line.
      Public Sub RestoreCategories()
      AddCategory "category", 17, 0
      End Sub

  2. Diane Poremsky

    BTW, this "Wrong number of arguments or invalid property assignment" likely means this line: AddCategory "category", 17, 0 is the problem. You need 3 arguments - the category name in quotes, the color number, and the shortcut code (0 for no shortcut).

  3. oliver

    Private Sub AddCategory(strCategoryName As String, intColor As Integer, intKey As Integer)

  4. Sheryl

    The first subroutine, GetCategoryNames(), should be PUBLIC, not PRIVATE. That is the problem.

  5. Sheryl

    Sorry, that's a just a fix for the first bug. Here's the fix for the second bug. (Doesn't anyone check the code before uploading?)

    Change this line:

    Private Sub AddCategory()

    to this:

    Private Sub AddCategory(strCategoryName As String, intColor As Integer, intKey As Integer)

    This runs now without error, but it doesn't work as described. It seems to add categories okay (I think), but it doesn't reset the colors, which is why I wanted it. Oh well.

    1. Diane Poremsky

      The necessary code depends on how you are using it. Private means it's not listed in the VBA macro picker - you need ot run it using other code or from the VBA editor. This is useful with code that is only used occasionally so it doesn't clutter up the macro list.

      The colors are represented by the numbers and what is assigned to the category name should be used in outlook.
      AddCategory "work", 17, 0 'orange
      AddCategory "important", 3, 0 'peach
      AddCategory "personal", 8, 0 'blue

      would each use a different color.

  6. Sheryl

    I wasn't using the macro picker for this. I was not able to run GetCategoryNames from the Immediate Window until I changed the declaration to public. My understanding is that "private" subroutines only can be called by the object or class.

    My color specifications in the RestoreCategories subroutine were copied and pasted from the output of GetCategoryNames, as described in your article. They don't have the comment with the color name at the end (is there a list of numbers to colors somewhere?), but they are otherwise the same. Here is the first line:

    AddCategory "Holiday", 6, 0

    Perhaps the problem is in how I'm trying to use it. I created a Outlook.com "database" (I think it's an OST file) in Outlook, and copied my local calendar entries into it. I very much want to use the same colors in that calendar as the local calendar. The colors for each calendar are stored separately within that calendar's Registry entries (encrypted). There's nothing in the macro that specifies the calendar. Perhaps it's selecting the default calendar, and I haven't changed my default from the local calendar. I tried selecting the Outlook.com calendar then picking the RestoreCategories macro, but no joy.

    Speaking of the Outlook.com calendar (as long as I have your attention), the synchronization problems are driving me crazy. I was able to get it to synchronize JUST ONCE by adding a category called "temp" to all the items, synching, then removing the "temp" category and synching again. 15 minutes or so later, a miracle - the calendar was on the Web. But since then, NOTHING has synched. The changes I make in the local Outlook.com calendar never get synched to the Web. Any thoughts on that?

    Thanks for your help.

    1. Diane Poremsky

      There is a list of color # at http://msdn.microsoft.com/en-us/library/office/bb208064(v=office.12).aspx - I'm not sure it's 100% accurate though, they had a lot of mistakes in the color names previously. It'll match most colors though.

      Private subs can only be called from the module, not across modules. If you are pasting it in the VBA Editor then hitting run, private should work. You can call it from a macro in different module.

      The code works on the default data file (store) - i don't have a code sample that works with other stores, but I do have one that gets the list of colors from all data stores.

      Sub GetColorsAllDataFiles()
      Set objStores = Application.Session.Stores
      For Each objStore In objStores

      Debug.Print vbCrLf & "----------------------------------------" & vbCrLf & objStore.DisplayName
      Debug.Print "--------------Categories-----------------"

      Set objCategories = objStore.Categories
      For Each objCategory In objCategories
      Debug.Print objCategory.Name & ", " & objCategory.Color & ", " & objCategory.ShortcutKey
      Next
      Next

      End Sub

Leave a Reply

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

This site uses XenWord.