Friday, August 12, 2005

Outlook recipients: AD contact postal address data

For our MSCRM customers we have made a small service that monitors the MSCRM database for changes to accounts and contacts, and maintains a specific Active Directory (AD) container that contains AD contacts that shadow the MSCRM accounts and contacts and their e-mail addresses. All these AD contacts are made available to Outlook through a new address book entry configured in Exchange System Manager. This ensures that all users have access to the e-mail information stored in MSCRM, even those that do not use Sales For Outlook. They can use the Outlook address book to pick or search for e-mail addresses and see details about an e-mail address such as contact name, company, postal address, etc.

The AD container is also used to hold AD distribution lists (mailing lists) built from the AD contacts. These distribution lists are also made available to Outlook through Exchange 2003. In addition, we have made a .NET add-in for Outlook that allows the users to preview the members of a list before sending e.g. the weekly newsletter e-mail (using an add-in toolbar in the e-mail Inspector window). The add-in allows the users to see the extra information stored in AD, and remove those members that should not get a mail this time. The users (ship brokers) typically decide that the e-mail should be sent to all members except those in Greece, and use the add-in to sort by country, multi-select the applicable members in a WinForms checkbox ListView and finally remove the selected members. This 'explodes' the mailing list into recipients, in addition to moving them to BCC to ensure that recipients do not see who else got this e-mail.

The code that maintains the AD contacts and the AD distribution lists runs on the Exchange Server 2003 to be able to modify data in both AD and Exchange (details here). Adding and maintaining AD contacts with .NET C# is quite easy, there are just a few pitfalls to be aware of when using AD contacts in combination with Exchange (details here).

The users recently requested the possibility to see the country of the MSCRM account/contact in the Outlook address book and when removing distribution list members. "No problem", I responded quickly and started checking postal address fields in Outlook and AD. I added full address information for an AD contact I knew was in the Outlook address book, waited for the RUS, and found the entered data in Outlook. I was ready to start coding !

First I added a 'country' column to my list view and then inspected the AddressEntry properties for access to postal address data. Unfortunately, Microsoft chose not to expose these properties in the Outlook object model. Fortunately, we were already using Redemption for other purposes in our add-in:

Redemption.MAPIUtils mapiUtils = new Redemption.MAPIUtils();
const int PR_COUNTRY = 0x3A26001E;
const int PR_EMAIL = 0x39FE001E;
foreach (Outlook.AddressEntry entry in list.Members)
string country = "";
object mapiCountry = mapiUtils.HrGetOneProp(entry.MAPIOBJECT, PR_COUNTRY);
if (mapiCountry != null) country = mapiCountry.ToString();

string smtp = entry.Address;
//check if Exchange address
if (smtp.StartsWith("/o="))
//get SMTP address from MAPI
smtp = mapiUtils.HrGetOneProp(entry.MAPIOBJECT, PR_EMAIL).ToString();

//add to listview
string[] items = new string[] { entry.Name, smtp, country };
ListViewItem itemX = new ListViewItem(items);
//keep name and address for later matching against recipients
itemX.Text = entry.Name;
itemX.Tag = entry.Address;

You will find a list of relevant MAPI property keys at

Then I modified our AD updater service to read the country of each MSCRM account/contact to be able to set it on each AD contact. I used the ADSIEdit MMC snap-in to inspect the properties of my 'guinea pig' AD contact to see which property was used for storing the country. The 'co' property contained the country name, and the property 'countryCode' seemed to have something to do with a contact's country. Some googling lead me to MSDN, which revealed that these three properties must be set according to ISO 3166:

  1. c: two letter country designation
  2. co: country name
  3. countryCode: country code (integer)

You will find the ISO 3166 list here. Copy and save the list to a .TXT file, open it in Excel as a 'fixed width' file to convert the text into a useful .XLS table. Use SQL Server 2000 DTS to import the .XLS to a new table in your database, enabling you to lookup MSCRM country names in the ISO 3166 country list.

The code to set MSCRM data into an AD contact properties looks like this:

//set properties
adContact.Properties["DisplayName"].Value = displayName;
adContact.Properties["mail"].Value = mailAddress;
//NOTE: AD fails on empty string, "invalid attribute syntax"
if(firstName.Length!=0) adContact.Properties["givenName"].Value = firstName;
if(lastName.Length!=0) adContact.Properties["sn"].Value = lastName;
if(company.Length!=0) adContact.Properties["company"].Value = company;
if(department.Length!=0) adContact.Properties["department"].Value = department;

//format: name;code;number
country += ";;;"; //just to be sure
string[] parts = country.Split(new char[]{';'});
string countryName = parts[0];
string countryA2 = parts[1];
string countryCode = parts[2];
if(countryName.Length!=0) adContact.Properties["co"].Value = countryName;
if(countryA2.Length!=0) adContact.Properties["c"].Value = countryA2;
if(countryCode.Length!=0) adContact.Properties["countryCode"].Value = Convert.ToInt32(countryCode);

// Flush to the directory

Note that I chose to require the country information to be provided as a semicolon separated string just for "simplicity" as I have not used entity objects (data xfer objects) in my AD service. I think refactoring of my service interface is needed, soon the users will want "just one more field" to be added...

Programming tip: use a reverse for-loop (i--) when removing entries from the Outlook.MailItem.Recipients collection, because the collection changes when a recipient is removed and this messes up the iteration if not performed end-to-beginning.

No comments: