<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Outlook 2007 Corporate Categories System

Published on
20,143 Points
7,743 Views
4 Endorsements
Last Modified:
Awarded
Community Pick
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>

Open in new window

3. Add the Outlook Class Module


  a.  Start Outlook
  b.  Click ToolsMacroVisual Basic Editor
  c.  If not already expanded, expand Microsoft Office Outlook Objects
  d.  Right-click on Class Modules, select InsertClass 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

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

Open in new window

5. Configure Outlook 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.  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
4
Comment
Author:David Lee
7 Comments
LVL 23

Expert Comment

by:Stacy Spear
BlueDevilFan, this is very nice!

I'm thinking about implementing it here and if so it must be on either MSSQL or Oracle but that shouldn't be an issue.
0
LVL 76

Author Comment

by:David Lee
Thanks, darkstar3d!  It should be pretty simple to port the database to MSSQL.  I've no experience with Oracle, so I don't know how difficult porting it to that would be.
0
LVL 28

Expert Comment

by:sunnyc7
BlueDevilFan
This is an excellent article. i'd test it out and see how it works for me

thanks
sunny
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

LVL 76

Author Comment

by:David Lee
Thanks, sunny, I appreciate the comment.  Please let me know how it goes.
0

Expert Comment

by:carlzan
Will this work the same for Outlook 2010?
0
LVL 76

Author Comment

by:David Lee
It should.
0

Expert Comment

by:jiv
Hi David,

Your approach may just be to technical for some users, even here on experts-exchange.
I would like to leave a link to our software which is not free like yours but may still be a better fit for some.
thanks,
John
0

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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…
Watch this simple and effective video tutorial to extract attachments from Outlook 2007 and try this easy method by yourself. No need to go anywhere, just watch the video and export attachments from Outlook in few simple steps. To know more, click h…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month