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

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.


LVL 93
Patrick MatthewsAsked:
Who is Participating?
David LeeCommented:
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

Patrick MatthewsAuthor Commented:

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.


Patrick MatthewsAuthor Commented:
And thank you, BTW :)

David LeeCommented:
NP.  Glad to help out.  I look forward to seeing the final version.
Patrick MatthewsAuthor Commented:

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.