Traversing Categories

[Shmuel Gershon]

Hi All.

In my Outlook system I have the emails categorized in categories. Just like
everybody else does, any email can belong to "zero or more" categories, and
categories overlap. Categories are dynamic, as I add and remove categories
all the time.

I want to be able to traverse throught these categories and sign the used
and the not-used ones.
(list all the existing categories and mark the ones that do not have any
email "assigned" to it).

How is the way to go?
I programmed VBA for excell, but never for Outlook.
I understand that in Outlook, instead of going through all the emails one
by one (think about a large quantity of emails!), I can use SQL Selects to
get the information in an practical way.

Can you point me any direction/website to get me started?

Thanks!
Shmuel

 

[Guest]

Hi Shmuel. This is possible, but not altogether practical. The main reason
is that Outlook's categories are stored in the registry in an undocumented
binary format. You'd spend hours if not days just writing the code to
extract them all (although there may be some sample code somewhere to start
with, but I can't think of any off the top of my head).

Secondly, categories can exist independently without being present in the
Master Categories list; any searching you do would exclude these "one-off"
categories from being returned.

Otherise, if you were able to loop through a list of existing Categories,
you can use the AdvancedSearch method to build a search scope through some or
all of your folders using a DASL query against the
urn:schemas-microsoft-comfficeffice#Keywords property. You can use the
Query Builder of SQL tabs in the Filter dialog when customizing an Outlook
view to help you build this search syntax.

OL: How to Use the Query Builder for View Filters and Advanced Searches:
http://support.microsoft.com/?kbid=307922

 

[Michael Bednarek]

In my Outlook system I have the emails categorized in categories. Just like
everybody else does, any email can belong to "zero or more" categories, and
categories overlap. Categories are dynamic, as I add and remove categories
all the time.

I want to be able to traverse throught these categories and sign the used
and the not-used ones.
(list all the existing categories and mark the ones that do not have any
email "assigned" to it).

How is the way to go?
I programmed VBA for excell, but never for Outlook.
I understand that in Outlook, instead of going through all the emails one
by one (think about a large quantity of emails!), I can use SQL Selects to
get the information in an practical way.

Can you point me any direction/website to get me started?

This should get you started:

Sub EnumerateCategories()

Dim fldFolder As MAPIFolder
Dim itmItem As Object

Set fldFolder = ActiveExplorer.CurrentFolder
For Each itmItem In fldFolder.Items
If itmItem.Categories <> "" Then Debug.Print itmItem.Subject & " - Cat: " & itmItem.Categories
Next itmItem
End Sub

 

[Shmuel Gershon]

This should get you started:

Sub EnumerateCategories()

Dim fldFolder As MAPIFolder
Dim itmItem As Object

Set fldFolder = ActiveExplorer.CurrentFolder
For Each itmItem In fldFolder.Items
If itmItem.Categories <> "" Then Debug.Print itmItem.Subject & " - Cat: "
& itmItem.Categories
Next itmItem
End Sub

Thanks Michael!
Your code not only woks, but is very easy to understand. Thanks again.

So... In order to get all the used categories, I need to 'walk' into all the
folders and nested folders, and save all the categories used.
It can work, but I see two handycaps:
1) In a large quantity of folders/messages, the macro can take very long to
process
2) Categories that are on the "Master List" but are not _currently_ used in
any message will not be processed.

I wanted to list all the categories, with a note on those that are not used,
so even if this is a very good starting point, maybe there is something
more.

Michael, what do you think of the idea I noted on the original post, of
using SQL Queries to get the info?
Do you think it is possible? How could I go to learn more?

Thanks in advance;
Shmuel

 

[sgershon]

Eric, thanks.
In summary, I understand that the safest way is entering all the
folders recursively and checking the categories of all the messages one
by one.
I understand and accept that in a packed system it will take up to a
few minutes.

I think that the best way to go is having the macro to work on top of
the CurrentFolder and all it's sub-folders. (I cant find how to access
the subfolders of a CurrentFolder, but it is a separated thread...
)

And then, message by message, I'll add the categories to some list, if
they exist.

Does this sound like the best solution for you as well?

Shmuel

 

[Michael Bednarek]

[snip]

Thanks Michael!
Your code not only woks, but is very easy to understand. Thanks again.

So... In order to get all the used categories, I need to 'walk' into all the
folders and nested folders, and save all the categories used.
It can work, but I see two handycaps:
1) In a large quantity of folders/messages, the macro can take very long to
process
2) Categories that are on the "Master List" but are not _currently_ used in
any message will not be processed.

Yes, I thought of that after I posted my message. There are some
problems with Categories, as Eric has already pointed out.

Categories can be attached to items, regardless whether they are in the
Master List or not.

The Master List itself is not readily available to VBA, particularly not
across an enterprise; it's stored in each user's registry, so you might well
have different Master Lists among your users.

The format of the Master List in the registry has changed with OL11 (2003)
from REG_SZ to REG_BINARY, by some work-experience programmer at Microsoft who
didn't know how to write Unicode strings into REG_SZ, because that's what
it is, Unicode.

And as with most Office-related registry entries, the location in the
registry is different for every version (...\Office\11.0\Outlook\...).
It may not even exist if the user has never applied a category.

I wanted to list all the categories, with a note on those that are not used,
so even if this is a very good starting point, maybe there is something
more.

Here's some code to read the Master Category List from the registry for
OL11 (Unicode). I found it some time ago at Klemens' Schmid web site at
<http://www.klemid.de/tools.aspx>:

Set objWSHShell = CreateObject("WScript.Shell")
vCategories = objWSHShell.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Outlook\Categories\MasterList")
strCategories = ""
For i = 0 To UBound(vCategories)
If i Mod 2 = 0 Then
strCategories = strCategories & Chr(vCategories(i))
End If
Next

which really should be rewritten to:
For i = LBound(vCategories) To UBound(vCategories) Step 2
strCategories = strCategories & Chr(vCategories(i))
Next i

Michael, what do you think of the idea I noted on the original post, of
using SQL Queries to get the info?

Not my immediate field of experience.

Do you think it is possible? How could I go to learn more?

I would be interested why you want to do this at all. Just to tidy
things up? Or to achieve consistency across the enterprise? If the
latter, I suggest you investigate other means, like Custome Fields,
because Categories can never be tamed in that direction.