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

Posted on 2005-05-09
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
    LVL 76

    Accepted Solution

    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 92

    Author Comment

    by:Patrick Matthews

    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 92

    Author Comment

    by:Patrick Matthews
    And thank you, BTW :)

    LVL 76

    Expert Comment

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

    Author Comment

    by:Patrick Matthews

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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Outlook can send email in three different formats: HTML, Rich Text, and Plain Text. Rich Text format is problematic though as it automatically wraps all formatting into an attachment called Winmail.dat. It also tends to pack actual file attachments …
    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now