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 BooleanPrivate intCount As IntegerPrivate objFSO As ObjectPrivate objFile As ObjectPrivate olkCategory As ObjectPrivate strDefaultFilename As StringPrivate 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 IfEnd SubPrivate Sub Class_Terminate() Set objFSO = NothingEnd SubPublic 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 SubPublic 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 0End Sub
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 = NothingEnd SubSub 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 = NothingEnd Sub
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.
Probably a simple fix but i get a Compile Error: A module is not a Valid type for the second section of the code. It Yellows Sub CategoriesExport() and highlights in blue objCatProcessor As New CategoryProcessor
Comments (32)
Author
Commented:Commented:
Author
Commented:Commented:
Author
Commented:View More