Importing and Exporting Outlook 2007 Categories
Published on
36,968 Points
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.
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.
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
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.
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
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
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
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
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.