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.

ColorIndexColorIndex
Red1Dark Gray14
Orange2Black15
Peach3Dark Red16
Yellow4Dark Orange17
Green5Dark Peach18
Teal6Dark Yellow19
Olive7Dark Green20
Blue8Dark Teal21
Purple9Dark Olive22
Maroon10Dark Blue23
Steel11Dark Purple24
Dark Steel12Dark Maroon25
Gray13

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.

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.

  2. oliver

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

  3. Sheryl

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

  4. 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.

  5. 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.

Leave a Reply

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