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