Sorting Addressees


I met Shelly Bowman (aka @g33kgurrl) on Twitter a couple of days ago when I responded to one of her tweets. This particular tweet was aimed at Microsoft telling them that it would be

AWESOME if users could alpha-sort distribution lists and meeting invitees in Outlook

While I don’t think I’d ever use such a feature I can see where it could be useful. Outlook does not offer any built-in means of sorting addresses that I know of. I have to leave room for the possibility that there is such an ability and I’m ignorant of it or it’s undocumented and known only to those that have discovered it by accident.

There are several possible solutions to this issue, but only one of them seems practical. Shelly could

  • Enter the addresses in alphabetical order. That’s pretty easy to do with a short list of recipients but becomes more complicated as the number of recipients goes up.
  • Copy and paste the addresses into Excel, sort them, and copy them back. Doable, but not very friendly.
  • Employ a script to sort the addresses. The best solution although it does have a couple of potential flaws.

I decided to give the script approach a shot. Before actually launching into writing the code I searched through my code archives and found that I’d written a macro for this sometime within the last year or so. I cleaned it up a bit and tested it to make sure it works properly. In my admittedly simple test scenarios it does sort the addresses, but not perfectly. It has a problem with an addressee who has multiple email addresses.

When addressing an item we either select names from an address book, type in the name of the addressee, or type in an addressee’s address. If we select a name from an address book, then Outlook automatically resolves the selection to an address. Ditto for entering an address. Entering a name is different though. Outlook has to search all available address books for the name. If it doesn’t find a match, then the entry is “unresolved” (i.e. Outlook cannot find an address for the entry). Unresolved addresses are those that are not underlined in the address line. If instead Outlook finds a match and the match has a single address, then the entry is “resolved” (i.e. Outlook found an address for the entry). The third and final possibility is that Outlook finds a match but the entry in the address book has multiple email addresses (e.g. office, home, mobile, etc.) Outlook cannot tell which address the user wants the message to go to, so it displays a dialog-box listing all the addresses and asking the user to chose the one they want. Once the user chooses and address the entry is “resolved”.

You’re probably wondering what resolving addresses has to do with sorting the addressees. The answer requires an understanding of how we have to go about sorting the addressees. There is no magic sort command for this, so to get them in sequence requires four steps.

  1. Read the addresses into a data structure that we can sort.
  2. Remove all the addressees from the item.
  3. Sort the entries.
  4. Write the sorted list back into the item.

The problem is that when the code performs step #4 any addressee with multiple addresses goes back into an unresolved state forcing the user to again select the address to send to. That’s a pain. I don’t know how big of a pain because it depends on addressees having multiple addresses and I don’t know how many of Shelly’s contacts have multiple addresses.

The Code.

Sub SortAddresses()
    Dim colNames As New Collection, _
        intCounter As Integer, _
        intIndex As Integer, _
        olkItem As Object, _
        olkRecipient As Outlook.Recipient, _
        olkAddressee As Outlook.Recipient, _
        varName As Variant
    Set olkItem = Application.ActiveInspector.CurrentItem
    For intCounter = olkItem.Recipients.count To 1 Step -1
        Set olkRecipient = olkItem.Recipients.Item(intCounter)
        If colNames.count > 0 Then
            intIndex = 1
            For Each varName In colNames
                If intIndex = colNames.count Then
                    If LCase(olkRecipient.Name) > LCase(varName) Then
                        colNames.Add olkRecipient, LCase(olkRecipient.Name), , intIndex
                        Exit For
                    Else
                        colNames.Add olkRecipient, LCase(olkRecipient.Name), intIndex
                        Exit For
                    End If
                End If
                If LCase(olkRecipient.Name) < LCase(varName) Then
                    colNames.Add olkRecipient, LCase(olkRecipient.Name), intIndex
                    Exit For
                End If
                intIndex = intIndex + 1
            Next
        Else
            colNames.Add olkRecipient, LCase(olkRecipient.Name)
        End If
        olkItem.Recipients.Remove intCounter
    Next
    For Each olkAddressee In colNames
        Set olkRecipient = Session.CreateRecipient(olkAddressee.Name)
        olkRecipient.Resolve
        If olkRecipient.Resolved Then
            Set olkRecipient = olkItem.Recipients.Add(olkAddressee.Name)
        Else
            Set olkRecipient = olkItem.Recipients.Add(olkAddressee.Address)
        End If
        olkRecipient.Type = olkAddressee.Type
    Next
    olkItem.Recipients.ResolveAll
    Set olkRecipient = Nothing
    Set colNames = Nothing
End Sub

Adding the Code to Outlook. Shelly will need to follow these instructions to add the code to Outlook. This will have to be done at each comptuer Shelly wants to use this solution on.

  1. Start Outlook
  2. Click Tools > Macro > Visual Basic Editor.
  3. If not already expanded, expand Microsoft Office Outlook Objects.
  4. If not already expanded, expand Modules.
  5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window.
  7. Edit the code as needed. I included comments wherever something needs to or can change.
  8. Click the diskette icon on the toolbar to save the changes.
  9. Close the VB Editor.

Using the Solution. Using this solution is simple. Shelly will create a new appointment, address it as normal, then with the appointment open onscreen she will run this macro. She has two options on how to run the macro: use Outlook’s built-in menus or create and use a toolbar button. If chooses to use the menus, then she’ll click Tools > Macro > Macros, select this macro, and click the Run button. Using a toolbar button will make the process a lot simpler, but of course she has to create the toolbar button first. Here’s how she can do that.

  1. Click View > Toolbars > Customize
  2. Click the Toolbars tab
  3. Click New
  4. Name the toolbar
  5. Click the Commands tab
  6. Under Categories click Macros
  7. Under Commands click and hold on the macro, then drag it out and drop it on the new toolbar
  8. Dock the toolbar somewhere onscreen

Notes.

  1. This solution has NOT been thoroughly tested. In my limited testing it appears to work properly, but that’s does not guarantee that it will work properly in all situations. Use it with caution until you’re sure it works properly.
  2. Keep in mind that any addressee with multiple addresses will revert to an unresolved state after running this macro. You will have to reselect the address for each such entry.
  3. The solution will sort addressees for messages as well as appointments.
  4. The code has to be installed on each computer you want to use it on.
  5. I wrote and tested this using Outlook 2007. It should work okay on 2010 too. It may also work on 2003 and earlier, but will probably trip Outlook’s built in security against code accessing any field that can contain an email address. The result will be a pop-up dialog-box warning that a program is accessing your mailbox and asking for permission to allow it to continue.

I hope this helps Shelly and anyone else who decides to use it. Perhaps in some future release of Outlook Microsoft will add an addressee sort feature.

Advertisements

10 comments on “Sorting Addressees

  1. Thanks, this really helps. I’ve combined it with another macro I found online so I can print a sorted attendance list for my meetings. A couple of notes.
    1) You might get duplicate names if you’re using distribution groups in your meeting invites. I work for the state of Michigan, and some of our distribution groups are pretty large and don’t always stay up-to-date. So some people are in more than one distribution group. Or are still in the distribution group when they leave state employ.
    2) Given 1), I modified the second For Loop where you resolve the names to delete recipients who aren’t resolved. The state is very diligent to inactivate anyone’s email address who no longer works for the state, so if their name doesn’t resolve, they’re no longer a valid invitee.

    If olkRecipient.Resolved Then
    Set olkRecipient = olkItem.Recipients.Add(olkAddressee.Name)
    olkRecipient.Type = olkAddressee.Type
    Else
    olkAddressee.Delete
    ‘ Set olkRecipient = olkItem.Recipients.Add(olkAddressee.Address)
    End If

  2. Thanks so much for posting some useful Outlook VBA code. It’s fiendishly powerful stuff, and yet the limitations of deploying (no easy way for non-technical people to load libraries unless you build use COM Add-ins) make this sort of solution less common than it might be otherwise.

    One issue I found is that duplicate names will *break* the code as it tries to collect the names on the initial pass. This is relatively easy to fix with a check for the existing name.

    This will be pretty destructive for a less-technical user, as the address list will be truncated. Caveat emptor!

    • Hi, Bruce.

      Thanks. I agree. It’s a shame that Microsoft didn’t make it easier to deploy/share macros.

      You’re right. If a user enters the same name more than once (e.g. once on the To line and again on the CC/BCC line), then the code will generate an error and drop an instance of the name from the list of addresses. My first thought is why would a user enter an address twice? It seems like an unusual situation. That said, I’ve seen users do a lot of strange things. I may update the solution in the future to allow for duplicate names. Thanks for pointing out the flaw.

  3. I’m a newbie to this. What needs to be edited. I get an error on the first line
    Dim colNames As New Collection, _
    Anyhelp would be greatly appreciated. Thanks

    Chuck

    • Hi, Chuck.

      Nothing needs to be edited in this script. The instructions are a stock set I use. I should have deleted #7. When you copied the code did it pick up the line numbers too? If so, then that’s the problem. There shouldn’t be any line numbers.

  4. David,
    Thank you so much for providing this code. I added it as a macro for 2007, and it’s working great. It will be easy even for non-coder types to add and use this macro.

    One thing of note for anyone who wants to use this: you can sort the list of scheduled attendees for a meeting you did not create on the “Scheduling” page. However, even if you save the meeting after re-sorting, you will not see the addresses in alpha order on your printed version of the meeting invite.

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