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.
- Read the addresses into a data structure that we can sort.
- Remove all the addressees from the item.
- Sort the entries.
- 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.
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.
- Start Outlook
- Click Tools > Macro > Visual Basic Editor.
- If not already expanded, expand Microsoft Office Outlook Objects.
- If not already expanded, expand Modules.
- 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.
- Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window.
- Edit the code as needed. I included comments wherever something needs to or can change.
- Click the diskette icon on the toolbar to save the changes.
- 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.
- Click View > Toolbars > Customize
- Click the Toolbars tab
- Click New
- Name the toolbar
- Click the Commands tab
- Under Categories click Macros
- Under Commands click and hold on the macro, then drag it out and drop it on the new toolbar
- Dock the toolbar somewhere onscreen
- 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.
- 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.
- The solution will sort addressees for messages as well as appointments.
- The code has to be installed on each computer you want to use it on.
- 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.