Outlook 2007 Corporate Categories System

Issue.  Microsoft Outlook lacks a means of creating and enforcing categories across a team, workgroup, or enterprise.  Each team member, group member, and employee is free to create categories as they see fit.  The result is category chaos.  Teams need a means of establishing a managed set of categories that are enforced for all team members regardless of the team’s size.

Background.  Categories offer Outlook users a means of organizing items (e.g. messages, contacts, appointments, etc.) into logical groupings.  In a team or enterprise setting categories could be used to group items by project or operating division (e.g. sales, marketing), or to color code a vacation calendar making it easy to see who is out when.  Unfortunately, Outlook categories are strictly personal to each user.  Outlook 2003 and earlier stores each user’s list of categories in the registry while Outlook 2007 stores them in a hidden folder in the user’s mailbox.  Outlook does not have a means of sharing categories among a group other than to create and share a list manually (e.g. an email or handwritten list of categories).  On creating a master list the author then must send the list to each group member along with a request that they each create the categories on the list.  When the team needs a new category or a category needs to be retired the category manager would have to send another message to all the team members asking them to add/remove the new/unneeded category.  If a new employee joins the team, then the category manager has to remember to send them the list of categories.  This  is both inefficient and impossible to enforce since team members could simply ignore the messages.

Solution.  Since Outlook has no built-in means of creating and enforcing a uniform set of categories (i.e. all team members have the same exact set of categories and category colors, no deviations or exceptions from one team member to another) the solution is to develop one using readily available tools and Outlook’s native macro facility .  The solution I’ve devised is composed of three components: a database, a web service, and some Visual Basic for Applications (VBA) code.  Here are the details of each.

  a.  Database.  For this solution I’ve used an Access 2007 database.  If you don’t have Access 2007, then you can replace it with another version of Access, SQL, or any other database provider that can be accessed using Microsoft Active Data Objects (ADO).  This solution uses a single database table with each row describing a category.

  b.  Web Service.  The web service is a web page built using Microsoft Active Server Page (ASP) technology.  Although I am calling it a web service it is not a true web service that uses standards like SOAP, WSDL, etc.  I’m calling it a web service because it performs the same function and is used in the same fashion as an actual web service.  If you cannot or do not want to use ASP, then you can replace it with PHP, ColdFusion, or any other technology that serves up dynamic web pages.

  c.  VBA.  The solution uses two blocks of VBA macro code.  The first block is code that runs each time Outlook is launched.  The second is a class containing the code that handles synchronizing categories with the central database.

Once in place here’s how the solution works.

  a.  Each time Outlook is launched it fires the VBA code.
  b.  The VBA code calls the web service.
  c.  The web service reads the database and returns a list of categories in XML format.
  d.  The VBA code erases all existing categories thereby eliminating any unauthorized categories the user may have created.
  e.  The VBA code creates the categories returned by the web service.

Once this solution is implemented adding or removing a category is as simple as editing the database.  Any changes will appear the next time each user launches Outlook.

Requirements.  This solution requires

  a.  Microsoft Access.
  b.  Outlook 2007.
  c.  A web server that supports ASP web pages.

Instructions.

1. Prepare the Database

  a.  Download the attached file named CorporateCategories.txt.  This the Access 2007 database.
  b.  Rename the file by changing the extension .txt to .accdb.
  c.  Place the database in a folder on the web server.  The folder must be accessible by the account the web server runs under.  Because of the variety of web servers I cannot provide details on how to do that.
  d.  Open the database and edit the Items table.  Enter your list of categories along with the corresponding colors and optional shortcut keys.

2. Implement the Web Service

  a.  Download the attached file named CorpCat.txt.
  b.  Rename the file by changing the extension from .txt to .asp.
  c.  Move the .asp page to your web server.  Because of the variety of web servers I cannot provide details on where the file should go.
  c.  Using Notepad edit the .asp page per the comments I included in the file.

Test the web service by launching your browser and navigating to the web service page.  If your browser is Internet Explorer, then you should see something like this:

  <?xml version="1.0" encoding="ISO-8859-1" ?> 
                      - <Categories>
                      - <Item>
                        <Name>Project Blue</Name> 
                        <Color>8</Color> 
                        <ShortCut>0</ShortCut> 
                        </Item>
                      - <Item>
                        <Name>Project Green</Name> 
                        <Color>5</Color> 
                        <ShortCut>0</ShortCut> 
                        </Item>
                        </Categories>

Select all Open in new window

3. Add the Outlook Class Module

  a.  Start Outlook
  b.  Click Tools > Macro > Visual Basic Editor
  c.  If not already expanded, expand Microsoft Office Outlook Objects
  d.  Right-click on Class Modules, select Insert > Class Module
  e.  In the Properties panel click on Name and enter CorpCategories
  f.  Copy the code from the snippet box below and paste it into the right-hand pane of Outlook's VB Editor window
  g.  Edit the code as needed.  I included comments wherever something needs to or can change
  h.  Click the diskette icon on the toolbar to save the changes
  i.  Leave the VB Editor open

'Edit the URL on the next line to that of your URL'
                      Const WEB_SERVICE_URL = "http://company.com/CorpCat.asp"
                      
                      Public Function SyncCategories() As Boolean
                          Dim objXMLHTTP As Object, _
                              objXMLDoc As Object, _
                              objCats As Object, _
                              objCat As Object, _
                              objValue As Object
                          Dim strResponse As String, _
                              strName As String, _
                              intColor As Integer, _
                              intShortcut As Integer, _
                              intState As Integer
                          Dim olkCategory As Outlook.Category
                          'Create an HTTP object and connect to the web service'
                          Set objXMLHTTP = CreateObject("Microsoft.XMLHTTP")
                          With objXMLHTTP
                              .Open "GET", WEB_SERVICE_URL, False
                              .Send
                              Do Until .readyState = 4
                                  intState = .readyState
                              Loop
                              If .Status = 200 Then
                                  strResponse = objXMLHTTP.responseText
                                  SyncCat2 = True
                              Else
                                  SyncCat2 = False
                              End If
                          End With
                          
                          'Load the data returned by the web service and add the categories to Outlook'
                          If SyncCat2 Then
                              ClearCategories
                              Set objXMLDoc = CreateObject("Microsoft.XMLDOM")
                              If objXMLDoc.LoadXML(strResponse) Then
                                  Set objCats = objXMLDoc.DocumentElement
                                  For Each objCat In objCats.ChildNodes
                                      strName = objCat.ChildNodes(0).Text
                                      intColor = objCat.ChildNodes(1).Text
                                      intShortcut = objCat.ChildNodes(2).Text
                                      Set olkCategory = Outlook.Application.Session.Categories.Add(strName, intColor, intShortcut)
                                  Next
                                  SyncCat2 = True
                              Else
                                  SyncCat2 = False
                              End If
                          End If
                          
                          'Cleanup
                          Set objXMLHTTP = Nothing
                          Set objXMLDoc = Nothing
                          Set objCats = Nothing
                          Set objCat = Nothing
                          Set objValue = Nothing
                      End Function
                      
                      Private Sub ClearCategories()
                          Dim intIndex As Integer
                          For intIndex = Outlook.Session.Categories.count To 1 Step -1
                              Outlook.Session.Categories.Remove (intIndex)
                          Next
                      End Sub

Select all Open in new window

4. Add the Outlook Event Procedure

  a.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  b.  Copy the code from the Code snippet box below and paste it into the right-hand pane of Outlook's VB Editor window
  c.  Click the diskette icon on the toolbar to save the changes
  d.  Close the VB Editor

Private Sub Application_Startup()
                          Dim objCorpCat As New CorpCategories
                          objCorpCat.SyncCategories
                          Set objCorpCat = Nothing
                      End Sub

Select all Open in new window

5. Configure Outlook 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.  Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.

6. Using this Solution

  a.  Adding a New Category.  Edit the database and add the new category.  
  b.  Changing a Category.  Edit the database and change the category as needed.
  c.  Deleting a Category.  Edit the database and remove the unwanted category.

The next time each user launches Outlook the changes will be synchronized to their computer.
References.

  a.  Category Colors:  http://msdn.microsoft.com/en-us/library/bb208064.aspx
  b.  Category Shortcuts: http://msdn.microsoft.com/en-us/library/bb208065.aspx

Known Problems.  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 

Notes.  Steps 3, 4, and 5 must be performed at each computer.

Enhancements.  Here are some thoughts on enhancements that would add functionality to this solution.

  a.  Add a timer.  Right now the solution only synchronizes the categories when Outlooks starts.  Adding a timer would allow the solution to synchronize at set intervals, every 15 minutes for example.  Syncing more often would ensure that new/old categories are added/removed faster.

  b.  Add a key.  The current solution flatly erases all existing categories before each sync.  This precludes staff from having any personal categories.  Implementing a key (e.g. prefixing corporate categories with a @) would allow staff to have personal categories while ensuring that shared, corporate, categories are uniform.  The solution would do this by only synchronizing categories whose name begins with a @ (e.g. @Project 1).

  c.  Groups.  This solution implements the same categories for all staff.  In an enterprise it is likely that different groups need different categories.  A simple solution would be to modify the database to include a group indicator and modify the code to return just those categories that go with the group the user belongs to.

Disclaimer.  This solution is a proof of concept.  Please test the code thoroughly before attempting to use it in a production environment.

Links to Other BlueDevilFan Articles

1. Creating Linked Notes in Outlook 2007
2. Extending Outlook Rules via Scripting
3. Importing and Exporting Outlook 2007 Categories
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

CorporateCategories.txt
CorpCat.txt