27 October 2007

Automatically resolve e-mail sender to selected contact emailaddress1. Part II

OK, then what's the next problem?

The next problem was: If you have a contact(senderA) in CRM, the senderA has 2 email address saved in emailaddress1 and emailaddress2 fields. If senderA send an email from ouside world into CRM by using emailaddress2, then CRM will think that is a associated email address, it's good so far. But when CRM user reply this email to senderA, CRM will always use emailaddress1 to send email! Which is not good because senderA sent this email by emailaddress2, he/she doesn't expect the reply email send into his/her another email box.

So this is the requirement: make emailaddress1 dynamically, so senderA's emailaddress1 will always keep the latest email address he/she used. Because CRM Email router doesn't fire Email Pre/Post Create callout, so I decide to use Workflow assembly to make it happen. Here's the code, you still need to update workflow.xml and set up a workflow Email.Create job.

* Update contact.emailaddress1 by email sender
* */

public void GetEmailSender(Guid activityid, String sender)
CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
string connectionString = "Server=_db;Database=_MSCRM;Integrated Security=SSPI";

//only select a contact(partyobjecttypecode=2) which is also a sender(participationtypemask=1)
string queryString = "SELECT partyid FROM FilteredActivityParty "
+ "WHERE(participationtypemask = 1) AND "
+ "(partyobjecttypecode = 2) AND "
+ "(activityid = '" + activityid.ToString()
+ "')";

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(queryString, connection);

SqlDataReader reader = command.ExecuteReader();
Guid cid = new Guid();

cid = reader.GetGuid(0); // get FilteredActivityParty.partyid
if(cid.ToString() != "00000000-0000-0000-0000-000000000000")
contact c = new contact();
c.contactid = new Key();
c.contactid.Value = cid;
c.emailaddress1 = sender;
catch (Exception ex)
TextWriter log = TextWriter.Synchronized(File.AppendText(@"C:\CRM_Debug\error.txt"));

20 October 2007

Automatically resolve e-mail sender to selected contact emailaddress1. Part I

We faced two problems in CRM 3.0 Email, the first problem was:
When an email(senderA@example.com) sent from outside world into CRM system, if sender's email address isn't in the system, then CRM will ask you to associate it with an existing record(only associate with a Contact record in our case). But once you select a contact, it looks associated, but it doesn't update the Contact's email field. You have to open the Contact record and manually copy the sender's email address into Contact's email field.

Regarding Microsoft KB: 922116, it is by design! Our requirement is make it automatically copy the sender's email address into the assocaited Contact.emailaddress1 field.

To achieve that, I made an unsupported change(again? :))

Automatically resolve e-mail sender to selected contact emailaddress1
Microsoft KB: 922116, http://support.microsoft.com/kb/922116

function applychanges()
//update cotnact emailaddress1
if((document.getElementById("crmExistingLookup").DataValue != null)
&&(document.getElementById("crmExistingLookup").DataValue[0] != null)
&&(document.getElementById("crmExistingLookup").DataValue[0].type == 2))
var eml = document.getElementById("txtName").value;
var cid = document.getElementById("crmExistingLookup").DataValue[0].id;
var connection = new ActiveXObject("ADODB.Connection");
var connectionString = "Provider=SQLOLEDB; Server=_db; Database=_mscrm; Integrated Security=SSPI";

var sql = "UPDATE FilteredContact SET emailaddress1='" + eml +"'WHERE contactid = '" + cid + "'" ;
rs = new ActiveXObject("ADODB.Recordset");
rs.Open(sql, connection, 1, 2);