Replacing Outlook Distribution Lists with Categories


Categories are one of Outlook’s most powerful features. Besides organizing items into groups they also enable you to color code them. For example you could create and assign a category to all messages, appointments, contacts, and tasks pertaining to a given project that would color them blue. Another, somewhat lesser known, feature is that you can use categories in lieu of a distribution list. For those of you who didn’t know about this capability, here are the steps you’d follow to do this in Outlook 2007:

  1. Switch to your Contacts folder
  2. Switch to the By Category. view
  3. Select a category by clicking on the group header
  4. Click Actions -> Create -> New Message to Contact

Using categories instead of distribution lists is actually quite useful. You don’t have to create or maintain any lists, just categorize your contacts as you add them. Another advantage is that there is no limit to the number of items assigned to a category, but there is a limit on the number of members in a distribution list. The limit is actually a size limit, but it effectively limits the maximum number of distribution list members to about 125 – 130 entries. For best results Microsoft recommends keeping distribution lists to no more than 50 – 70 members. The problem with using categories as distribution lists is that there are too many steps involved and you cannot add contacts to the To, CC, or BCC line like you can a distribution list. Is there a way to overcome these issues? The answer is Yes.

The code below is the solution to both problems. With it in place you can address messages to categories just as you would to a contact or a distribution list. You can even select them from the address book. It does this by scanning messages as you send them looking for what I call a category pseudo-address. “Huh?”, you’re thinking, “What’s that?” To answer that I have to explain a bit about contacts and distribution lists. Each time you send a message Outlook scans the list of addressees and looks for matches in all the address books (e.g. the GAL, your Contacts folder) it knows about. This is knowing as resolving the address. If Outlook finds a match, then it adds the contact’s address to the message. If a contact has more than one address, then Outlook pops up a dialog-box asking you to choose which address to use. If Outlook can’t find a contact with a matching name, then it displays a different dialog-box asking you to find and select the contact that matches the name you entered. The exception to this process is when you address a message to an email address (e.g. John.Doe@company.com instead of John Doe). Outlook recognizes and understands email addresses and knows it doesn’t need to resolve them. Categories don’t appear in any address book so Outlook cannot resolve a category name to an email address. But what if we gave each category an email address? That would bypass Outlook’s name-to-address resolution process allowing us to address a message to category. That’s what the category pseudo-address does. It gives us a way to enter an address that bypasses Outlook’s name resolution process and identifies the address as a category to the code. The pseudo-address is composed of the category name and a dummy email domain that identifies the address as a category name. I’ve written the code to use a dummy domain name of “cat.lst” (for category list), but you can change that to something else if you want to.

Confused yet? Here’s an example to show how it works. Say that you created a category for a project you’re working on. The category name is “ProjectX”. If you wanted to send a message to all the contacts that belong to the ProjectX category, then with this code in place you could address it to ProjectX@cat.lst. Sending the message triggers the code. The code checks to see that the item is an email (this only works for messages, not appointments, tasks, etc.) Having passed that test the code then scans the message’s addressees. For each addressee it checks to see if the address contains the category pseudo-domain name, which in this case is “cat.lst”. In this example it sees that domain in the address and knows to treat the entry as a category. Next, it breaks the address down to get the category name (i.e. the portion appearing before the @ sign). Armed with the category name it searches your Contacts folder and finds all the contacts that belong to that category. For each contact found the code checks to see if the contact has an email address. If it does, the contact is added as an addressee on the message. Once the code finishes processing the contacts it removes the pseudo address from the message’s addressees. Why? Because it knows that there isn’t anyone with that address. In short, the address ProjectX@cat.lst tells the code “This is a dummy address that points to a category. Go find all contacts that belong to this category and address the message to them if they have an email address. When you’re finished with that, remove the dummy address.”

That’s great, you say, but it still doesn’t allow me to pick the category from my address book like I can with contacts and distribution lists. Instead, I’ll have to type the address out each time. Good point. The remedy for that is to create a contact for the category and assign it the pseudo-address. For the ProjectX example you’ll simply create a contact with the name ProjectX and the address ProjectX@cat.lst. Now when you click To, CC, or BCC the address book that pops up WILL have a ProjectX entry. From an addressing perspective you can now use categories just like distribution lists.

There are a few caveats to this solution.

  • This only works with the full version of Outlook on a PC. It will not work with OWA (Outlook Web Access), Outlook for the Mac, or messages you send from a mobile device.
  • Your category names must be unique and must not include another category’s name as part of its name. For example, this would not work properly if you had two categories named “Project X” and “Project X Stakeholders” and sent a message to the category “Project X”. That message would go to all the members of both categories since “Project X” is a substring contained within “Project X Stakeholders”, the other category’s name. However, a message addressed to “Project X Stakeholders” would only go to the members of that category since the name is not a substring of “Project X”.
  • This is a proof of concept. You should test it in your environment to make sure you understand how it works before depending on it. While I have done some limited testing I may have overlooked something and/or it may behave differently for you than it does for me. I’d hate for a message to go to the wrong group of people and cause you any embarrassment or worse.

Adding the Code to Outlook.

Outlook 2007.

  1. Start Outlook
  2. Press ALT+F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  4. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  5. Edit the code as needed. I included comment lines wherever something needs to or can change
  6. Click the diskette icon on the toolbar to save the changes
  7. Close the VB Editor
  8. Click File and select Options
  9. When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
  10. Click Macro Settings
  11. Select either of the two bottom settings (i.e. “Notifications for all macros” or “Enable all macros (not recommended; potentially dangerous code can run)”. The choice of which to chose is up to you. If you select “Notifications”, then you’ll be prompted at times to enable macros. If you pick “Enable all” then there’s a chance that a malicious macro could run. It’s a question of how much risk you want to assume.
  12. Click Ok until the dialog-boxes have all closed
  13. Close Outlook
  14. 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.

Outlook 2010.

  1. Start Outlook
  2. Click Tools > Macro > Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
  4. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  5. Edit the code as needed. I included comment lines wherever something needs to or can change
  6. Click the diskette icon on the toolbar to save the changes
  7. Close the VB Editor
  8. Click File and select Options
  9. When the Outlook Options dialog appears click Trust Center then click the Trust Center Settings button
  10. Click Macro Settings
  11. Select either of the two bottom settings (i.e. “Notifications for all macros” or “Enable all macros (not recommended; potentially dangerous code can run)”. The choice of which to chose is up to you. If you select “Notifications”, then you’ll be prompted at times to enable macros. If you pick “Enable all” then there’s a chance that a malicious macro could run. It’s a question of how much risk you want to assume.
  12. Click Ok until the dialog-boxes have all closed
  13. Close Outlook
  14. 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.
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    'On the next line edit the name of the dummy mail domain used to represent categories
    Const CATEGORY_PSEUDO_DOMAIN = "@cat.lst"
    Dim olkMsg As Outlook.MailItem, _
        olkRcp As Outlook.Recipient, _
        olkNew As Outlook.Recipient, _
        olkFld As Outlook.MAPIFolder, _
        olkLst As Outlook.Items, _
        olkCon As Object, _
        intPos As Integer, _
        intCnt As Integer, _
        strCat As String, _
        strFlt As String, _
        strAdr As String
    'Is the item an email?
    If Item.Class = olMail Then
        Set olkMsg = Item
        'Loop through the recipients the item is addressed to
        For intCnt = olkMsg.Recipients.Count To 1 Step -1
            Set olkRcp = olkMsg.Recipients.Item(intCnt)
            'Look for the category domain in the address
            intPos = InStr(1, LCase(olkRcp.Name), CATEGORY_PSEUDO_DOMAIN)
            'If the category domain is in the address
            If intPos > 0 Then
                'Get the category name
                strCat = Left(olkRcp.Name, intPos - 1)
                'Get all the contacts that belong to this category
                strFlt = "@SQL=" & Chr(34) & "urn:schemas-microsoft-com:office:office#Keywords" & Chr(34) & " LIKE '%" & strCat & "%'"
                Set olkFld = Session.GetDefaultFolder(olFolderContacts)
                Set olkLst = olkFld.Items.Restrict(strFlt)
                'For each contact in the category
                For Each olkCon In olkLst
                    If olkCon.Class = olContact Then
                        strAdr = ""
                        'If the contact includes an email address
                        If (olkCon.Email1Address <> "") Then
                            strAdr = olkCon.Email1Address
                        ElseIf (olkCon.Email2Address <> "") Then
                            strAdr = olkCon.Email2Address
                        ElseIf (olkCon.Email3Address <> "") Then
                            strAdr = olkCon.Email3Address
                        End If
                        If strAdr <> "" Then
                            'Add the contact as an addressee
                            Set olkNew = olkMsg.Recipients.Add(strAdr)
                            olkNew.Type = olkRcp.Type
                        End If
                    End If
                Next
                'Delete the category pseudo-address from the addressees
                olkRcp.Delete
            End If
        Next
        olkMsg.Recipients.ResolveAll
    End If
    Set olkMsg = Nothing
    Set olkRcp = Nothing
    Set olkNew = Nothing
    Set olkFld = Nothing
    Set olkLst = Nothing
    Set olkCon = Nothing
End Sub
Advertisements

15 comments on “Replacing Outlook Distribution Lists with Categories

  1. David,
    It’s been a long time since I first got your help with Outlook. Now I have a need to send mail to groups in a subfolder using categories. How do I get to the subfolder with your VBA code? Thank you for your help.

    Regards,
    Bobby

    • Hi, Bobby.

      It’s good to hear from you again.

      To get to any folder other than the default contacts folder add this function to the code you already have.

      Private Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
          ' Purpose: Opens an Outlook folder from a folder path.'
          ' Written: 4/24/2009'
          ' Author:  David Lee'
          ' Outlook: All versions'
          Dim arrFolders As Variant, _
              varFolder As Variant, _
              bolBeyondRoot As Boolean
          On Error Resume Next
          If strFolderPath = "" Then
              Set OpenOutlookFolder = Nothing
          Else
              Do While Left(strFolderPath, 1) = "\"
                  strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
              Loop
              arrFolders = Split(strFolderPath, "\")
              For Each varFolder In arrFolders
                  Select Case bolBeyondRoot
                      Case False
                          Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                          bolBeyondRoot = True
                      Case True
                          Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
                  End Select
                  If Err.Number <> 0 Then
                      Set OpenOutlookFolder = Nothing
                      Exit For
                  End If
              Next
          End If
          On Error GoTo 0
      End Function
      

      Now, replace this line in the Application_ItemSend subroutine

      Set olkFld = Session.GetDefaultFolder(olFolderContacts)
      

      with

      Set olkFld = OpenOutlookFolder("Path to the target folder")
      

      Replace “Path to the target folder” with the path to the folder your contacts are in. If the folder is a sub-folder under contacts, then the path will be something like “Mailbox – Strain, Bobby\Contacts\Sub-folder name” or “bobby@company.com\contacts\sub-folder name”.

  2. David,
    It’s me again. I have a category with contacts without email addresses. The code to send to categories suddenly seems to send multiple copies to recipient that is followed by a contact with no email address. I don’t follow this with the code. Maybe your discerning eye can spot the reason for such behavior.

    Happy New Year!
    Bobby

    • Hi, Bobby.

      Happy New Year to you too!

      “I have a category with contacts without email addresses. The code to send to categories suddenly seems to send multiple copies to recipient that is followed by a contact with no email address. I don’t follow this with the code.”

      Sorry, but I don’t understand. Are you saying that you have a set of contacts that all belong to a given category and that none of those contacts have an email address? If so, then are you saying that the code is sending to these contacts even though you entered a different category name to send to?

    • David,
      I spent a bit more time looking at the code, and corrected the problem. When looping through the addresses, if one (or more) is blank, i.e. no address, the previous address is still in the string, so it is added again for the blank until a new address is read. I corrected this behavior by setting the address to “” at the end of each loop. I guess this has always happened, and my sibling who uses the code just noticed. Her recipients are too kind and never reported multiple mails. And it’s a great help for her. Outlook is really complex if one want to do something out of the ordinary. Also, I looked at printing addresses for a category in card form, hoping to get a dozen per page. I did find help, but, boy, is it complicated. I guess I’ll have to buy a book for Outlook VBA and develop some procedures to make such tasks simple.

      Regards,

      Bobby

    • Bobby,

      Got it. I’ve added that change to the code so others don’t run into it. Thanks for catching and pointing it out to me!

      As to “printing addresses in card for”, if you’ll share what you have in mind, then I might be able to help out.

  3. David,
    I, too, could not send mail with the code. It seems that Outlook tried to send it to the address as entered. Do you have an update for resolving this? It’s really neat, and exactly what I need for my older sibling.
    Thank you for your help.

    Bobby

    • Hi, Bobby.

      I just tested the code as is and it worked correctly. That suggests that something is wrong at your end. The most likely cause is that macros aren’t running at all. What is macro security set to in Outlook? Also, what version of Outlook are you using?

    • David,
      You are right that the macro didn’t run in Outlook 2010. There is something wrong with the installation. So I moved to Outlook 2007. The macro runs, but when the string comparison is executed, the intPos is 0, so the macro doesn’t finish execution. The statement looks OK to me, and the value should be positive. I am using Tester as the category. I will re-enter the string comparison and see if that does the trick. But not until tomorrow. Thanks for your prompt reply.

      Bobby

    • David,
      I resolved part of the issue using Outlook 2007. The code executes and does everything intended. But I get the sysadmin note that failure to deliver to recipients, with none listed. So I go the the sent folder, and the email is there, with the recipient names as expected. Then I attempted to resend the message, and Outlook didn’t have any valid address for the recipients, even though they were displayed in the To: field as per the code, last name and first name. What was also puzzling is that the mail was sent from my secondary account.
      I use Norton antivirus software. Could this be the cause of the failure? I am at a loss for resolution. You seem to be the only person on the planet who is using this technique with Outlook. There seems to be not Add-in to accomplish this so easily, either. And, I might mention that the category and pseudo domain in the contact folder is set up with only the name (sans @cat.lst) and the address is where it should be. So I had to change the code to use pseudo recipient address rather than the name. Works fine to locate the string for the category. Like good beer, it ain’t over until the beer can be consumed.

      Bobby

    • Bobby,

      When I was running the code last night to verify that it works I saw something that bugged me. I noticed that contacts were being inserted twice instead of once. To fix that I changed the code so it inserts the contact’s address instead of name. Please delete the code you have now then download and use the new version. Let’s see if that fixes the problem for you. If it doesn’t, then we’ll take it from there.

    • David,
      Thank you for the modified code. It worked perfectly, as you intended. I took me quite a long search to find your help. Now I know where to look for Outlook. Do you have any recommendation about searching for other gems like yours about Microsoft Office products?

      Bobby

    • You’re welcome, Bobby.

      You can go to this page and search for an MVP (most valuable professional) in a particular area of expertise (e.g. Word, PowerPoint). Experts-Exchange, although disliked by a lot of people, is another place where you can find help on a particular Office product. Quite a few MVPs hang out there. I know a few people with specific skills, but would rather know what you’re looking for than list them all.

  4. Good Day Lee
    I have outlook 2010 )part of 2010 office professional) and just had a go at the Replacing Outlook Distribution Lists with Categories VB scripting etc (newbie at VB). anyway it doesn’t work..i love the idea of being able to have the cats in the BCC (especially now that most people are a bit wary of harvesting email adressess from long email list)
    1. what i did was open the tab “Developer”, clicked on the icon Visual Basic, clicked on the This Outlook Session, somehow got a box open which was titled VbaProject.OTM-ThisOutlookSession (Code), copied the code from your blog, into the top section (General), saved the project and a dialog box opened with Syntax error and the top line was highlighted yellow.
    2 a i put a comma a the end of it and saved it, closed outlook, reopened sent a message (to testing123@cat.lst) but got a delivery failure as the pseudo email address was not recognized (DNS Error: Domain name not found)
    2b (before i closed off) I set up a new category “testing123” put a couple of my email addresses into that cat, and set up a contact card with testing123@cat.lst as the address.

    i have since done things like choose application from the drop down box (application or general) but not much else as i am pretty green in the VB area.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s