<

Importing and Exporting Outlook 2007 Categories

Published on
36,505 Points
24,305 Views
12 Endorsements
Last Modified:
Approved
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 ToolsMacroVisual Basic Editor or press ALT+F11 to open Outlook's VB Editor window.
b.  From the menu click InsertClass 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

Open in new window

2. Add the Calling Code


a.  From the menu click ToolsMacroVisual 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 InsertModule.
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

Open in new window

3. Configure Security


a.  Click ToolsTrust 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
12
Comment
Author:David Lee
  • 14
  • 8
  • 3
  • +6
33 Comments
LVL 76

Author Comment

by:David Lee
Hi, guys.  

Thanks for the feedback.  I'm curious about your first point.

"The first section, "issue", Launches into Categories very quickly as if every reader has had the problems you relay from your EE experiences"

I wrote the article specifically for readers who are familiar with categories and have run into one of the two issues I mentioned.  In my first draft I did talk a bit more about categories.  I removed that as it seemed unnecessary, kind of like explaining what putting is or why it's important in article about how to improve that particular skill.  A reader who is completely unfamiliar with categories probably isn't going to be interested in importing/exporting or sharing them.  They aren't the target audience.  My concern is that explaining the basics of categories in an article targeted at the experienced user risks alienate them.  I know that I'm put off by that sort of writing.  What do you think?

I'll re-read the article and look for the grammatical errors.  I did run it through Word's spelling and grammar checker and thought I'd caught them all.
0
 

Administrative Comment

by:WaterStreet
BlueDevilFan:

I think you're doing fine; just a little rearrangement and explanation at the beginning, from my review.

Suggestions

1.  Insert the phrase "Outlook 2007" into the first sentence, as follows

How do I copy my Outlook 2007 categories to another computer?"

2.  The Page Editors have been told to make sure the articles can be understood by a wider audience than the author's primary focus.  So after the first sentence (or somewhere), I think you should provide a sentence that briefly explains what a category is.  For example:  Categories are the groupings of [...] within your Outlook program, such as..., etc."

3.  For clearer organization, I suggest splitting the current Issues section with a new section called "Background" starting with "Prior to 2007 Outlook stored..."  Format it bold like the others.
0

Expert Comment

by:GegH
I followed the instructions ran the macro, exported from my machine. Went to someone elses, ran the macro and imported. It worked like a charm, easy to follow instructions as well.

When i restarted Outlook on the second machine the new categories had disappeared and i had to re-import them. Is this supposed to happen? If so how can i get them to stay?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LVL 76

Author Comment

by:David Lee
Hi, GegH.

No, that's not what is supposed to happen.  Did you open the same Outlook profile?
0

Expert Comment

by:GegH
Yes, same profile. I tried it on a couple of other machines too, same thing.
The mailboxes are on exchange server (sbs 2003), 1 profile per person.
0
LVL 76

Author Comment

by:David Lee
Outlook 2007 SP2 introduces a bug that prevents categories created via code from saving.  Download and install this hotfix to correct the problem: http://support.microsoft.com/kb/970944  Once it's installed the categories should save.
0

Expert Comment

by:VLNunez
Fantastic article--thanks so much.  Your information took the work right out of the task!  
0
LVL 76

Author Comment

by:David Lee
Thanks!  Glad it was helpful.
0

Expert Comment

by:Joshallof
Is there a way to do a macro to export and import Custom views such as automatic Formating?
0
LVL 76

Author Comment

by:David Lee
This is just a comment to close the loop on Joshallof's post.  My answer is in this question: http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_26728301.html
0

Expert Comment

by:jasonburr
The export worked perfectly, but I receive an error when attempting to import (compile error: Syntax error).  The debugger highlights the line with "Sub CategoriesImport ().   Any ideas?  thanks!
0
LVL 76

Author Comment

by:David Lee
Hi, jasonburr.

Sorry to be slow to get back to you on this.  Can you post a screenshot of the editor window you see when the error occurs?
0

Expert Comment

by:topprops
I tried your solution and I get a message that says the list is imported, but it doesn't show up in category list.  I only see the ones I manually put in.  I thought that I was not formating the txt file correctly, so I tried various variations of formating the data.  I get the same result.  330 items imported, but non are on the list.

what am I doing wrong.

Jon
0

Expert Comment

by:VLNunez
Try this ... odd as it sounds.  Uncheck your default calendar (another calendar must be checked, though), close Outlook, reopen Outlook after a few seconds allowing for it to close fully, then re-enable your default calendar.  Apparently, the categories live in a hidden file in your default calendar.  This always brings mine back.  I hope Microsoft has fixed this issue in Outlook 2010. I'll find out soon.  Hope this helps, Topprops!  
0

Expert Comment

by:topprops
No that didn't help, but thanks

jon
0
LVL 76

Author Comment

by:David Lee
Categories do indeed live in a hidden folder, but not under the calendar.  Categories apply equally to contacts, messages, and tasks as well as to appointments.  

topprops - can you share the file you're trying to import?  If so, then I can test it here to ensure that there aren;t any problems with it.
0

Expert Comment

by:topprops
sure,
I tried it several different ways.  here are some
Outlook-Categories.TXT
contacts-categories-2.txt
contacts-categories-1.txt
0
LVL 76

Author Comment

by:David Lee
The format of these files are incorrect.  The format should be

CategoryName,CategoryColor,CategoryHotkey

where CategoryColor and CategoryHotkey are numbers.  CategoryColor will be an integer between 1 and 25.  CategoryHotkey will generally be 0 indicating no hotkey.  Each category will be on a line by itself.  Something like this:

Holiday,3,0
Business,2,0
Travel,16,0
0

Expert Comment

by:topprops
what number is for categorycolor none?

 I guessed that blank would represent none.

thanks

jon
0
LVL 76

Author Comment

by:David Lee
Use a 0 if you don't want a color.
0

Expert Comment

by:topprops
I'' try thatn thanks
0

Expert Comment

by:topprops
That worked thanks

jon
0

Expert Comment

by:topprops
so now outlook 2007 left the category field off of the input screen for new contacts.  very annoying because now you have to scroll through 330 categories to find the right one.  where as before all you had to do was type in the category or two.  I there any way to add the field to the input screen.  

I'm  being expremely hopeful.

jon
0
LVL 76

Author Comment

by:David Lee
Yes, that's possible.  It means creating a custom form and setting it to be the default contact form.  Here's a page that describes what's involved in doing that.

http://www.outlookcode.com/article.aspx?ID=35
0

Expert Comment

by:topprops
Thanks I'll take a look at that.  Never new you could create custom forms.

Jon
0

Expert Comment

by:altegrisit
I was wondering about the export and inport macro's mentioned in Step 4. Are those available as well? I am doing this to help a group of users and could be very helpful getting those.
0
LVL 76

Author Comment

by:David Lee
The macros are the subject of the article.  They're the code in the article.
0

Expert Comment

by:atranFFIT123
Is there a macro like this that works for outlook 2013?
0
LVL 76

Author Comment

by:David Lee
The code in this article should work for 2013 too.
0

Expert Comment

by:atranFFIT123
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
0
LVL 76

Author Comment

by:David Lee
Did you add the class module in part 1 and name it CategoryProcessor?
0

Expert Comment

by:atranFFIT123
I did add it.  but not as a class module.  Fixed it and everything works.  Thanks a bunch for the script.
0
LVL 76

Author Comment

by:David Lee
You're welcome.  I'm glad you found it useful.
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Discover the basics of using Outlook 2016 from office 365.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month