Importing and Exporting Outlook 2007 Categories

Issue.  "How do I copy my categories to another computer?" and "How can I share a set of categories with a group?" are two of the more frequent category related questions I see here on Experts-Exchange.  The answer is that Microsoft, unfortunately, has not provided a built-in means of backing up or restoring the master category list (MCL), or of sharing an MCL with others.  If you change computers or rebuild your computer, then you are forced to recreate your MCL from scratch.  

Background. Prior to 2007 Outlook stored categories in the registry.  A savvy user could export the registry key containing the MCL to a .reg file.  They could then import that file as needed on a new or rebuilt computer, or share the file with another user.  This provided a rudimentary means of creating a somewhat consistent set of categories within a group.  Microsoft killed this ability in Outlook 2007 when they moved the MCL out of the registry and into a hidden folder in the default mail store (the mailbox or .pst file containing the default inbox).  Fortunately for us the Outlook development team introduced new programming objects that enable building a more effective and efficient solution.  

Solution.  Outlook 2007 introduces the Category and Categories objects to Outlook's object model.  The Category object describes a single category.  The Categories object represents a collection of Category objects.  The Categories object is the MCL.  Using these objects we can build a simple VBA solution that solves both the issue of backing up and restoring, and sharing the MCL. The class module is the heart of this solution.  It provides two methods, one for importing categories and another for exporting them.

The Import method is very simple.  It accepts a single, optional, parameter containing the name and path of the file to import.  If a file name isn't passed, then the code imports from a file named Outlook Categories.txt which it expects to find in you're My Documents folder.  After initializing variables, the code opens the input file and processes the entries it contains one line at a time.  Each entry in the file is composed of a simple list of comma-separated values.  The values are Name, Color, and Shortcut Key.  The code checks each category it reads from the file to see if it already exists.  If it doesn't, then the code adds the category to the Outlook's MCL.

The Export method is even simpler.  It too accepts a single, optional, parameter containing the name and path of the file to use.  As with the Import method, the code defaults to Outlook Categories.txt in you're My Documents folder if a file name isn't passed.  The export is very straightforward.  It simply writes all defined categories to the text file.  The categories are written one per line in the same comma-separated format described above.

Requirements.    Outlook 2007.

Instructions.  Follow these instructions to implement this solution.

1. Add the Class Module

a.  From the menu click Tools >  Macro >  Visual Basic Editor or press ALT+F11 to open Outlook's VB Editor window.
b.  From the menu click Insert >  Class Module.
c.  In the Properties pane (lower left-hand corner) click in the Name field and enter CategoryProcessor.
d.  Copy the code in the snippet below and paste it into the right-hand pane of the VB editor.
e.  Click the diskette icon on the toolbar to save your changes.

Option Explicit
                      
                      '*** Constants'
                      Const CB_APPNAME = "CategoryProcessor"
                      Const CB_VERSION = "1.0"
                      Const ForReading = 1
                      
                      '*** Class Variables'
                      Private bolInitialized As Boolean
                      Private intCount As Integer
                      Private objFSO As Object
                      Private objFile As Object
                      Private olkCategory As Object
                      Private strDefaultFilename As String
                          
                      Private Sub Class_Initialize()
                          Dim arrVersion As Variant
                          arrVersion = Split(Outlook.Application.VERSION, ".")
                          If arrVersion(0) < 12 Then
                              MsgBox "This object only works with Outlook 2007 and higher.", vbCritical + vbOKOnly, CB_APPNAME
                          Else
                              Set objFSO = CreateObject("Scripting.FileSystemObject")
                              strDefaultFilename = Environ("USERPROFILE") & "\My Documents\Outlook Categories.txt"
                              bolInitialized = True
                          End If
                      End Sub
                      
                      Private Sub Class_Terminate()
                          Set objFSO = Nothing
                      End Sub
                      
                      Public Sub Export(Optional strFilename As String)
                          Dim strFileUsed As String
                          If bolInitialized Then
                              intCount = 0
                              strFileUsed = IIf(strFilename = "", strDefaultFilename, strFilename)
                              Set objFile = objFSO.CreateTextFile(strFileUsed, True)
                              For Each olkCategory In Outlook.Session.Categories
                                  objFile.WriteLine olkCategory.Name & "," & olkCategory.Color & "," & olkCategory.ShortcutKey
                                  intCount = intCount + 1
                              Next
                              objFile.Close
                          End If
                          MsgBox "Exported " & intCount & " categories to " & vbCrLf & strFileUsed, vbInformation + vbOKOnly, CB_APPNAME & " - Export"
                      End Sub
                      
                      Public Sub Import(Optional strFilename As String)
                          Dim strBuffer As String, strFileUsed As String, arrValues As Variant, olkCategory As Object, intRead As Integer
                          On Error Resume Next
                          If bolInitialized Then
                              intCount = 0
                              intRead = 0
                              strFileUsed = IIf(strFilename = "", strDefaultFilename, strFilename)
                              If objFSO.FileExists(strFileUsed) Then
                                  Set objFile = objFSO.OpenTextFile(strFileUsed, ForReading)
                                  Do Until objFile.AtEndOfStream
                                      strBuffer = objFile.ReadLine
                                      arrValues = Split(strBuffer, ",")
                                      Set olkCategory = Outlook.Session.Categories.Item(arrValues(0))
                                      If TypeName(olkCategory) = "Nothing" Then
                                          Outlook.Session.Categories.Add arrValues(0), arrValues(1), arrValues(2)
                                          intCount = intCount + 1
                                      End If
                                      Set olkCategory = Nothing
                                      intRead = intRead + 1
                                  Loop
                                  objFile.Close
                                  MsgBox "Imported " & intCount & " of " & intRead & " categories read from " & vbCrLf & strFileUsed, vbInformation + vbOKOnly, CB_APPNAME & " - Import"
                              Else
                                  MsgBox "The file " & strFileUsed & " does not exist.  Import aborted.", vbCritical + vbOKOnly, CB_APPNAME & " - Import"
                              End If
                          End If
                          On Error GoTo 0
                      End Sub
                      

Select all Open in new window

2. Add the Calling Code

a.  From the menu click Tools >  Macro >  Visual Basic Editor or press ALT+F11 to open Outlook's Visual Basic editor.
b.  If not already expanded, expand Microsoft Office Outlook Objects.
c.  If not already expanded, expand Modules
d.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert >  Module.
e.  Copy the code from the snippet below and paste it into the right-hand pane of Outlook's VB Editor window

Sub CategoriesExport()
                          Dim objCatProcessor As New CategoryProcessor
                          With objCatProcessor
                              .Export InputBox("Enter the name of the file, including the path, you want to export to.", "Get Export Filename")
                          End With
                          Set objCatProcessor = Nothing
                      End Sub
                      
                      Sub CategoriesImport()
                          Dim objCatProcessor As New CategoryProcessor
                          With objCatProcessor
                              .Import InputBox("Enter the name of the file, including the path, you want to import from.", "Get Import Filename")
                          End With
                          Set objCatProcessor = Nothing
                      End Sub
                      

Select all Open in new window

3. Configure Security

a.  Click Tools >  Trust Center.
b.  Click Macro Security.
c.  Set Macro Security to Warnings for all macros.
d.  Click OK.
e.  Close Outlook.
f.  Start Outlook.

4. Using This Solution

a.  Backing-up Your MCL.  Run the CategoriesExport macro.
b.  Restoring Your MCL.  Run the CategoriesImport macro.
c.  Sharing Your MCL.  Create the categories.  Run CategoriesExport to export them to a file.  Distribute the file to your group.  Each group member runs CategoriesImport to import the categories on their computer.

Links to Other BlueDevilFan Articles

1. Creating Linked Notes in Outlook 2007
2. Extending Outlook Rules via Scripting
3. Outlook 2007 Corporate Categories System
4. Automatically Printing/Saving Emails/Attachments in Outlook
5. Avoiding Blank Subject Lines in Outlook
6. Never Again Forget to Add that Attachment to your Outlook Email
7. Enhancing Outlook 2007 Meeting Reminders