Having a list of categories names and their colors can be helpful when sharing categories with other users, since the color is per-user. Fortunately, MSDN published a sample macro that prints a list of categories and the name of the color assigned. Unfortunately, some of the color names in their macro are not valid. Below is the code from Category.Color Property (Outlook), with the corrected olCategoryColor names.
This macro can display a message box with a list of the category names and colors or print it to the VBA Editor's Immediate window.
To create a list of categories, including their colors and assigned shortcuts that can be restored or merged with the categories in a new profile, see Create a list of color categories and merge or restore the list using VBA.
Use MsgBox strOutput to display a message box or Debug.Print strOutput to print a list to the Immediate window (View > Immediate window or Ctrl+G). You can copy the list from the Immediate window or the MsgBox.
The printout and message box will look like the following:
Utility: Purple
Bookkeeping: Unknown
Business: Peach
Important: Dark Yellow
Test: Purple
Blue Category: Blue
Federal Holidays: Green
Needs Done: Dark Green
Old Projects that need finished: Dark Olive
Send Message: Yellow
Slipstick: Olive
Follow up soon: Dark Orange
The message box is copyable - click in the text and press Ctrl+C to copy it.
List Category Colors Macro
To use, Open the VBA editor using Alt+F11, right click on the Project folder and choose Insert > Module. Paste this code into the module then click the Run button or press F8. Press Ctrl+G to see the list of category names and colors in the immediate window. Comment out the MsgBox line to use only the Immediate window.
Public Sub ListCategoryNamesandColors()
Dim objNameSpace As NameSpace
Dim objCategory As Category
Dim strOutput As String
Set objNameSpace = Application.GetNamespace("MAPI")
If objNameSpace.Categories.Count > 0 Then
For Each objCategory In objNameSpace.Categories
strOutput = strOutput & objCategory.Name
Select Case objCategory.Color
Case OlCategoryColor.olCategoryColorNone
strOutput = strOutput & ": No color (white)" & vbCrLf
Case OlCategoryColor.olCategoryColorBlack
strOutput = strOutput & ": Black " & vbCrLf
Case OlCategoryColor.olCategoryColorBlue
strOutput = strOutput & ": Blue" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkBlue
strOutput = strOutput & ": Dark Blue" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkGreen
strOutput = strOutput & ": Dark Green" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkMaroon
strOutput = strOutput & ": Dark Maroon" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkOlive
strOutput = strOutput & ": Dark Olive" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkOrange
strOutput = strOutput & ": Dark Orange" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkPeach
strOutput = strOutput & ": Dark Peach" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkPurple
strOutput = strOutput & ": Dark Purple" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkRed
strOutput = strOutput & ": Dark Red" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkSteel
strOutput = strOutput & ": Dark Steel" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkTeal
strOutput = strOutput & ": Dark Teal" & vbCrLf
Case OlCategoryColor.olCategoryColorDarkYellow
strOutput = strOutput & ": Dark Yellow" & vbCrLf
Case OlCategoryColor.olCategoryColorGray
strOutput = strOutput & ": Gray" & vbCrLf
Case OlCategoryColor.olCategoryColorGreen
strOutput = strOutput & ": Green" & vbCrLf
Case OlCategoryColor.olCategoryColorMaroon
strOutput = strOutput & ": Maroon" & vbCrLf
Case OlCategoryColor.olCategoryColorOlive
strOutput = strOutput & ": Olive" & vbCrLf
Case OlCategoryColor.olCategoryColorOrange
strOutput = strOutput & ": Orange" & vbCrLf
Case OlCategoryColor.olCategoryColorPeach
strOutput = strOutput & ": Peach" & vbCrLf
Case OlCategoryColor.olCategoryColorPurple
strOutput = strOutput & ": Purple" & vbCrLf
Case OlCategoryColor.olCategoryColorRed
strOutput = strOutput & ": Red" & vbCrLf
Case OlCategoryColor.olCategoryColorSteel
strOutput = strOutput & ": Steel" & vbCrLf
Case OlCategoryColor.olCategoryColorTeal
strOutput = strOutput & ": Teal" & vbCrLf
Case OlCategoryColor.olCategoryColorYellow
strOutput = strOutput & ": Yellow" & vbCrLf
Case Else
strOutput = strOutput & ": Unknown" & vbCrLf
End Select
Next
End If
' Display the output string in a msgbox
' or in the immediate window
MsgBox strOutput
Debug.Print strOutput
Set objCategory = Nothing
Set objNameSpace = Nothing
End SubTo include the Color Category index number in the listing (useful in other macros), add objCategory.Color to each line, as shown in the sample below. The results will include the index number at the end of the results line: Edit Websites: Dark Steel 12
strOutput = strOutput & ": Dark Steel " & objCategory.Color & vbCrLf
Write the Category List to Excel or Notepad
While you can copy and paste the list from the Immediate window, you can send it directly to an email message, a text file, or Excel. Simply replace the last few lines of code, from Debug.Print strOutput to the end with one of the code snippets below.
To write the list to a new email message, use this code.
Debug.Print strOutput '### write to email message Dim objMsg As MailItem Set objMsg = Application.CreateItem(olMailItem) With objMsg .Body = strOutput .Display End With '### end email message Set objCategory = Nothing Set objNameSpace = Nothing End Sub
To write the list to a text file, use this code snippet.
Debug.Print strOutput
' ### write to a text file
Dim FSO As Object
Dim strFile As String
Dim strFolderpath As String
Set FSO = CreateObject("Scripting.FileSystemObject")
' save to documents
strFolderpath = CreateObject("WScript.Shell").SpecialFolders(16)
Debug.Print strFolderpath
strFile = strFolderpath & "\color-categories-list.txt"
Set objFile = FSO.CreateTextFile(strFile, True)
objFile.Write "" & strOutput
objFile.Close
' ### end write to text file
Set objCategory = Nothing
Set objNameSpace = Nothing
End Sub
To write the category names and colors to Excel, splitting the categories into rows and columns, use this code:
Debug.Print strOutput
' ### write to excel
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0
'Open a new workbook to input the data
Set xlWB = xlApp.Workbooks.Add
Set xlSheet = xlWB.Sheets("Sheet1")
xlSheet.Range("A1") = "Category Name"
xlSheet.Range("B1") = "Color"
xlSheet.Range("A2") = strOutput
' split strOutput into rows
Dim k() As String
Dim l As Long
Dim i As Long
k() = Split(xlSheet.Range("A2"), vbCrLf)
i = 2
For l = 0 To UBound(k)
xlSheet.Cells(i, 1) = k(l)
i = i + 1
Next l
Dim txt As String
Dim Categories As Variant
For Each cell In xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(xlSheet.UsedRange.Count, 1))
txt = cell.Value
Categories = Split(txt, ":")
For i = 0 To UBound(Categories)
cell.Offset(0, i).Value = Categories(i)
Next i
Next cell
xlApp.Visible = True
' ### end write to excel
Set objCategory = Nothing
Set objNameSpace = Nothing
End Sub
Category Color Codes
If you want to use the category color in the printout, the color codes are below. These colors are in the order they appear on the category color picker in Outlook 2016. The color names are as listed in the color picker.
RedolCategoryColorRedHEX : #F07D88 RGB : 240,125,136 HSB : 354,48,94 HSL : 354,79,72 | OrangeolCategoryColorOrangeHEX : #FF8C00 RGB : 255,140,0 HSB : 33,100,100 HSL : 33,100,50 | PeacholCategoryColorPeachHEX : #FECB6F RGB : 254,203,111 HSB : 39,56,100 HSL : 39,99,72 | YellowolCategoryColorYellowHEX : #FFF100 RGB : 255,241,0 HSB : 57,100,100 HSL : 57,100,50 | GreenolCategoryColorGreenHEX : #5FBE7D RGB : 95,190,125 HSB : 139,50,75 HSL : 139,42,56 |
TealolCategoryColorTealHEX : #33BAB1 RGB : 51,186,177 HSB : 176,73,73 HSL : 176,57,46 | OliveolCategoryColorOliveHEX : #A3B367RGB : 163,179,103 HSB : 73,42,70 HSL : 73,33,55 | BlueolCategoryColorBlueHEX : #55ABE5RGB : 85,171,229 HSB : 204,63,90 HSL : 204,73,62 | PurpleolCategoryColorPurpleHEX : #A895E2RGB : 168,149,226 HSB : 255,34,89 HSL : 255,57,74 | MaroonolCategoryColorMaroonHEX : #E48BB5RGB : 228,139,181 HSB : 332,39,89 HSL : 332,62,72 |
SteelolCategoryColorSteelHEX : #B9C0CBRGB : 185,192,203 HSB : 217,9,80 HSL : 217,15,76 | Dark SteelolCategoryColorDarkSteelHEX : #4C596ERGB : 76,89,110 HSB : 217,31,43 HSL : 217,18,36 | GrayolCategoryColorGrayHEX : #ABABABRGB : 171,171,171 HSB : 300,0,67 HSL : 300,0,67 | Dark GrayolCategoryColorDarkGrayHEX : #666666RGB : 102,102,102 HSB : 300,0,40 HSL : 300,0,40 | BlackolCategoryColorBlackHEX : #474747RGB : 71,71,71 HSB : 300,0,28 HSL : 300,0,28 |
Dark RedolCategoryColorDarkRedHEX : #910A19RGB : 145,10,25 HSB : 353,93,57 HSL : 353,87,30 | Dark OrangeolCategoryColorDarkOrangeHEX : #CE4B28RGB : 206,75,40 HSB : 13,81,81 HSL : 13,67,48 | Dark PeacholCategoryColorDarkPeachHEX : #A47332RGB : 164,115,50 HSB : 34,70,64 HSL : 34,53,42 | Dark YellowolCategoryColorDarkYellowHEX : #B0A923RGB : 176,169,35 HSB : 57,80,69 HSL : 57,67,41 | Dark GreenolCategoryColorDarkGreenHEX : #026802RGB : 2,104,2 HSB : 120,98,41 HSL : 120,96,21 |
Dark TealolCategoryColorDarkTealHEX : #1C6367RGB : 28,99,103 HSB : 183,73,40 HSL : 183,57,26 | Dark OliveolCategoryColorDarkOliveHEX : #5C6A22RGB : 92,106,34 HSB : 72,68,42 HSL : 72,51,27 | Dark BlueolCategoryColorDarkBlueHEX : #254069RGB : 37,64,105 HSB : 216,65,41 HSL : 216,48,28 | Dark PurpleolCategoryColorDarkPurpleHEX : #562685RGB : 86,38,133 HSB : 270,71,52 HSL : 270,56,34 | Dark MaroonolCategoryColorDarkMaroonHEX : #80275DRGB : 128,39,93 HSB : 324,70,50 HSL : 324,53,33 |
Outlook 365 and Outlook 2019 have updated color category names and colors.
RedolCategoryColorRedHEX : #DC626D RGB : 220,98,109 HSB : 355,55,86 HSL : 355,64,62 | OrangeolCategoryColorOrangeHEX : #E8825D RGB : 232,130,93 HSB : 16,60,91 HSL : 16,75,64 | PeacholCategoryColorPeachHEX : #FFCD8F RGB : 255,205,143 HSB : 33,44,100 HSL : 33,100,78 | YellowolCategoryColorYellowHEX : #FDEE65 RGB : 253,238,101 HSB : 54,60,99 HSL : 54,97,69 | Light GreenolCategoryColorGreenHEX : #52CE90 RGB : 82,206,144 HSB : 150,60,81 HSL : 150,56,56 |
Light TealolCategoryColorTealHEX : #57D2DA RGB : 87,210,218 HSB : 184,60,85 HSL : 184,64,60 | Lime GreenolCategoryColorOliveHEX : #B6D767 RGB : 182,215,103 HSB : 78,52,84 HSL : 78,58,62 | BlueolCategoryColorBlueHEX : #5CA9E5 RGB : 92,169,229 HSB : 206,60,90 HSL : 206,72,63 | LavenderolCategoryColorPurpleHEX : #B1AAEB RGB : 177,170,235 HSB : 246,28,92 HSL : 246,62,79 | MagentaolCategoryColorMaroonHEX : #EE5FB7 RGB : 238,95,183 HSB : 323,60,93 HSL : 323,81,65 |
Light GrayolCategoryColorSteelHEX : #C5CED1 RGB : 197,206,209 HSB : 195,6,82 HSL : 195,12,80 | SteelolCategoryColorDarkSteelHEX : #4497A9 RGB : 68,151,169 HSB : 191,60,66 HSL : 191,43,46 | Warm GrayolCategoryColorGrayHEX : #C3C5BB RGB : 195,197,187 HSB : 72,5,77 HSL : 72,8,75 | GrayolCategoryColorDarkGrayHEX : #9FADB1 RGB : 159,173,177 HSB : 193,10,69 HSL : 193,10,66 | Dark GrayolCategoryColorBlackHEX : #8F8F8F RGB : 143,143,143 HSB : 300,0,56 HSL : 300,0,56 |
Dark RedolCategoryColorDarkRedHEX : #AC4E5E RGB : 172,78,94 HSB : 350,55,67 HSL : 350,38,49 | Dark OrangeolCategoryColorDarkOrangeHEX : #DF8E64 RGB : 223,142,100 HSB : 20,55,87 HSL : 20,66,63 | BrownolCategoryColorDarkPeachHEX : #BC8F6F RGB : 188,143,111 HSB : 25,41,74 HSL : 25,36,59 | GoldolCategoryColorDarkYellowHEX : #DAC257 RGB : 218,194,87 HSB : 49,60,85 HSL : 49,64,60 | Dark GreenolCategoryColorDarkGreenHEX : #4CA64C RGB : 76,166,76 HSB : 120,54,65 HSL : 120,37,47 |
TealolCategoryColorDarkTealHEX : #4BB4B7 RGB : 75,180,183 HSB : 182,59,72 HSL : 182,43,51 | GreenolCategoryColorDarkOliveHEX : #85B44C RGB : 133,180,76 HSB : 87,58,71 HSL : 87,41,50 | Navy BlueolCategoryColorDarkBlueHEX : #4179A3 RGB : 65,121,163 HSB : 206,60,64 HSL : 206,43,45 | Dark PurpleolCategoryColorDarkPurpleHEX : #A589CB RGB : 165,137,203 HSB : 265,33,80 HSL : 265,39,67 | Dark PinkolCategoryColorDarkMaroonHEX : #C34E98 RGB : 195,78,152 HSB : 322,60,76 HSL : 322,49,54 |
Gary says
Thank you so much for this macro. I saved the list to a text file. Now is there a macro that can be used to import the list into outlook running on another computer? Or is there a macro that can be used to assign every category to a note? I've found instructions for exporting categories with a note and then importing the note as a msg file on another computer BUT I have lots of categories and manually selecting them all for the note is a pain.
Diane Poremsky says
There is a macro on this page - https://www.slipstick.com/developer/get-color-categories-and-restore-them-using-vba/ - to import a category list.
Marko says
That's a gret help, thanks a lot.
However, it just exports the categories of the standard mail account.
Any idea of how to go through all accounts or select one?
Rob says
This has been a massive time saver! Thanks for taking the time to develop and present this so clearly!
Rebecca Dunkley says
Thank you for this - it is very helpful. I would like to ask - is there a way of getting the exact colour to highlight the cells rather than just what colour and index number it is once it is put into Excel or in finding the correct index colour in the colour options for the cells so I can do it manually? I tried to look up the colours but I am unsure if I have the same colour as the index colour number.
Diane Poremsky says
You'll need to get the RGB color then fill the cell. I don't think i have a list of them, but will look.
Diane Poremsky says
You'll need to get the RGB color - I was hoping the enumeration would be the same between outlook and excel, but they're not. :(
https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.outlook.olcategorycolor?view=outlook-pia
Mary says
This is an add on to my last question.
Is there a way to 'get' the list into Excel so i can parse it?
Thanks
Diane Poremsky says
you can copy and paste it to excel or notepad... but yes, you could also write it to excel.
Diane Poremsky says
i added code samples to write to email, notepad, or excel to the end of the article.
Mary says
Hi and many thanks Diane, i can't belive this function is not available already.
I copied the code and ran the Macro and it printed the cat/colours list to screen Ok but how can i get the list to print?
Also is there any way to expand the colour categories box so you can see all categories/colours together on the screen?
Thank you
Mary
Diane Poremsky says
Print: luddite way: copy and paste into notepad :)
Expand the category box: No, sorry.
Jim Schwetz says
Great Post Diane,
I ...overuse categories in outlook. Problem is when I right click on an email to assign a category, the menu only shows some of the categories I use, so I have to click again to see more categories. Is there a way to change how many categories I can see while assigning one?
Diane Poremsky says
No, sorry. You'll see a MRU list of recent/frequently used categories. You can assign shortcuts to some, but it won't help a lot as there is a limited number of shortcuts available.
Laurie says
This is great but wondering if someone could help me with some VBA code so that I could get a list of categories, name with the count for a specified folder. I would like to output it to an excel file and show, Folder, category, count of items in said category. I've seen a few but can't get any to work too well.
Thanks
Diane Poremsky says
This macro is just a printout of the categories in the master list and the colors they are assigned.
If you want to count the number of items in each category, you need to go through and check each message and keep a running tally. This is more complicated. Do you want to get the count for one folder or for all folders in the mailbox?
Diane Poremsky says
BTW, it will be easier to write each message (just the fields you need, like only category) then use excel functions to get the counts by categories. This macro https://www.slipstick.com/developer/code-samples/macro-export-outlook-fields-excel/ (changed to export the category field) then let Excel do the work.
Steve Glykys says
Dear Dian, I run the program as advised by I get the message "The Macros in this Project are disabled. Please refer to the on line help or documentation of the host application to determine how to enable macros." Your advise will be very much appreciated. Kind regards Steve
Diane Poremsky says
You need to go to File, Options, Trust Center, Trust Center Settings, Macro security and enable macros (lowest setting). Then restart Outlook.
Niels Andersen says
I imported my contacts into O2010. I have got colours. And all my 1000+ contacts have got individual category NAMES. But I cannot add an existing category name to a new contact, I cannot add a NEW category name, and I cannot see the names in the drop down category list, which shows only the colours. Any ideas, pl?
Aaron M. says
Thank You! For a non-programmer I was able to use your directions to backup my Categories which I rely heavily on. I feel much better now that I have a list of what they are. Thanks again!!
Frances.r.buie.civ@army.mil says
I need to change the names of the colors (in the existing categories) in order for the entire office to be aware of what each color stands for. Can you provide me with the neccessary steps?
Diane Poremsky says
You are doing this in everyone's mailbox? You'll need to use a utility. See the tools list here.
Dawn Lambert says
Thank you for trying, Diane :)
Justin Freebourn says
I'd like to know how to make this work with a shared calendar, too.
Thanks,
Justin
Diane Poremsky says
It's a lot more complicated to work with categories in Shared folders and I don't have any code samples that can do it. Sorry.
Dawn Lambert says
The marco works on the default calendar but I would like to run it on a shared calendar. Where do I enter in the name of the calendar I want to get the categories from?
Thank you!
Diane Poremsky says
I'll check on it and update the code.
Kathie Paintner says
Just an EA looking for a simple list of all of our categories, opened the VBA editor and pasted the above code, the F8 to run. Then Ctrl+G. Nothing displays in the Immediate Window.
Diane Poremsky says
Do you get any error messages? What is your macro security set to? You should see a dialog box come up with a list of categories as soon as you hit Run and it is repeated in the immediate window.
Greg Simonis says
I copied the code to VBA, deleted the comments at the bottom, and upon running I get an error msg. 'Compile error: User-defined type not defined and it highlights the last part of the second line "Dim 'objNameSpace As Namespace'". The section highlighted I put single quotes around so you see what text was highlighted.
Diane Poremsky says
User-defined type not defined means it thinks there is a custom variable. And that it stops on "Dim objNameSpace As Namespace" means it thinks something in this line is the user-defined type. I don't know why it thinks that, I'm not able to repro it.
Kevin says
Since this is so old, my update is only for future people that may receive the same error:
I had a similar problem, it turned out the web page had the letters coded with some hidden thing I couldn't see. When I copied and pasted into VBA the hidden coding came with it. I replaced the characters by manually typing them and it fixed the problem.
Tim Chambers says
Thank you, Diane! The page that referred me to your helpful macro is in the Website field.
– Tim Chambers 1E4AF729D5CEFFD0
With apologies to C.S. Lewis: You must picture me alone in front of my Windows computer, day after day, feeling, whenever my mind lifted even for a second from my work, the steady, unrelenting approach of the abominable language that I so earnestly desired not to use. That which I greatly feared had at last come upon me. During a lunch of Rosemary & Olive Oil Triscuits on July 11th, 2012 I gave in, and admitted that Microsoft VBA existed, and opened the VBA editor and ran my very first module: perhaps, that day, the most dejected and reluctant convert in all of cyberspace.
Michael Doncaster says
Hi,
I can get this macro to run in the VBA editor, but it will not run from the ribbon.
I had to change it to a 'public sub' for it to visible in the macro list in the Ribbon customisation pane.
Any idea why?
Regards
Michael
Diane Poremsky says
Make sure the msgbox is uncommented near the bottom. It prints to the immediate window by default, which is why it's private. (You could tweak it to print to a new message or text file. )
Actually... the msgbox is copyable, so you can get a text list without writing to a file.