VBA UDF desired to return 'master category list' as an array

Posted on 2005-05-09
Medium Priority
Last Modified: 2008-02-01
Dear Experts,

With BlueDevilFan's excellent assistance, I was able to build a process that will automatically grab all the recipients of an
active Mail item and create new Contact items from them.

As it stands now, I can assign a Company to all of the new contacts; I am thinking about doing this for Categories as

As I understand it, the master category list for a user is stored in a Registry as a semi-colon delimited list (with the added
fun that it is stored as Unicode for Outlook XP and Outlook 2003).  Also, as I understand it, the categories assigned to any
given Outlook item are stored as a comma-delimited list in the item' Categories property.

What I am looking for is a VBA user-defined function that will look up the master category list for users of Outlook 2000
or later, and return a result that is an array of the string representations of the categories.  (Thus, I guess one of the first
things the function will have to do is ascertain whether it is being used with Outlook 2000, or with subsequent versions,
and parse the list accordingly.)  I am asking for multi-version support because I will probably want to distribute the final
product among my co-workers, who are on various versions of Office (but none earlier than Office 2000).

Once I have this array, it would be a relatively simple matter to use the array to populate a multi-select ListBox from
which I could choose what categories to apply to my newly-created contacts.

I have tried some Google searches, and I am sure that the sites I hit would have pointed me in the right directions.
I am just not able at present to devote the time needed to absorb the material.


Question by:Patrick Matthews
  • 3
  • 2
LVL 76

Accepted Solution

David Lee earned 2000 total points
ID: 13964264
Hello again, Patrick.

Here's a UDF that does what I believe you're looking for.  It passes back a semi-colon delimited string that is the master category list.  I don't have Outlook 2000 nor do I have access to a machine that has it, so you may need to adjust the registry key I used for it.  I tested this on both 2002 and 2003 and it correctly returned the MCL on both.  

arrMCL = Split(GetMCL(), ";")

Private Function GetMCL() As Variant
    Dim arrVersion As Variant, _
        objShell As Object, _
        arrTemp As Variant, _
        intCounter As Integer, _
        strBuffer As String
    Set objShell = CreateObject("Wscript.Shell")
    arrVersion = Split(Application.Version, ".")
    Select Case arrVersion(0)
        Case 9      '2000
            GetMCL = objShell.RegRead("HKCU\Software\Microsoft\Office\9.0\Outlook\Categories\MasterList")
        Case 10, 11 'XP/2003
            arrTemp = objShell.RegRead("HKCU\Software\Microsoft\Office\" & arrVersion(0) & ".0\Outlook\Categories\MasterList")
            For intCounter = LBound(arrTemp) To UBound(arrTemp)
                If arrTemp(intCounter) <> 0 Then
                    strBuffer = strBuffer & Chr(arrTemp(intCounter))
                End If
            GetMCL = strBuffer
        Case Else
            GetMCL = "Unknown Outlook Version"
    End Select
    Set objShell = Nothing
End Function

LVL 93

Author Comment

by:Patrick Matthews
ID: 13964737

Worked great!  I have updated the first UserForm now to include a multi-select ListBox of the available categories.  Once I have
cleaned up the project (and once I get a soon-to-be-posted new question answered) I will post an update.


LVL 93

Author Comment

by:Patrick Matthews
ID: 13964743
And thank you, BTW :)

LVL 76

Expert Comment

by:David Lee
ID: 13964946
NP.  Glad to help out.  I look forward to seeing the final version.
LVL 93

Author Comment

by:Patrick Matthews
ID: 13964968

In case you're interested, here is the follow-up I mentioned above:


Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article lists the top 5 trialware OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their Exchange server is no longer available or other critical issues with Exchange server or impo…
Take a look at these 6 Outlook Email management tools which can augment the working and performance of Microsoft Outlook to give you a more rewarding emailing experience.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question