Create automated email notification when your incoming emails stops synchronizing in your Dynamics CRM

First let me explain the requirement here. Our client had set up server side sync for incoming email synchronization. There is one support mailbox to which end-users sends email. Once the email comes to support mailbox, the email is forwarded to CRM at every specified interval. And once it comes inside CRM, it is assigned to some queue.

But one regular problem that our customer is facing that email synchronization stops due to mailbox errors or some other issues related to our async service and then it does not get caught unless some support user complains that customers have already sent an email, but the email does not appear in his queue.

Obviously this can be caught by continuous monitoring of the mailbox. But that is something very strenuous. So I asked my customer – “What if I could automate this?”. The word automation excites every customer and my customer was not an exception.

These days I do not get to much hands on. More on architecting and design. Perhaps one of the perils as you grow up in experience . But being from a programming background, whenever I get a chance, I grab it with both hands.

So this is what I did.

• Created a console application.
• In the console application, I set-up connection to both our CRM organization and the Exchange Mailbox.
• In then query the CRM Emails entity to find the timestamp of the last incoming email.
• I take that timestamp and pass it to the below function.

internal static List<EmailMessage> GetEmailsNotInCrm(DateTime dateToCompare, ExchangeService service)
        {
            SearchFilter.IsGreaterThan search = new SearchFilter.IsGreaterThan();
            search.PropertyDefinition = ItemSchema.DateTimeReceived;
            search.Value = dateToCompare;

            FindItemsResults<Item> findResults = service.FindItems(
                        WellKnownFolderName.Inbox, search,
                                        new ItemView(100));

            var emailMessageCollection = new List<EmailMessage>();

            foreach(var emailItem in findResults)
            {
                EmailMessage message = EmailMessage.Bind(service, emailItem.Id);

                emailMessageCollection.Add(message);
            }

            return emailMessageCollection;
        }

        internal static ExchangeService CreateExchangeConnection()
        {
            ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2007_SP1);

            service.Credentials = new NetworkCredential(ConfigurationManager.AppSettings["owausername"],
                System.Text.Encoding.UTF8.GetString(Convert.FromBase64String(ConfigurationManager.AppSettings["owapassword"])), "<your company domain where the mailbox is hosted>");

            service.Url = new Uri(ConfigurationManager.AppSettings["owaurl"]);

            return service;
        }

Please note that you would need to reference the Microsoft.Exchange.Webservices.dll for the Exchange related classes.

The function above queries for all the emails that have come to the Mailbox and are still not created in CRM. The method ‘CreateExchangeConnection’ creates a connection to the exchange mailbox.To explain the code a bit,

dateToCompare – This parameter is the datetime value of the last incoming email created in CRM.

So here I am searching the inbox for all the emails which arrived in the inbox after the last incoming email in CRM. And using the ItemView class, I am retrieving only 100 records.

I then create a collection of EmailMessage objects and then pass it to the below methods which prepares the email body and send the email to CRM support team alias for our customer.

/// <summary>
        /// Method to send email for debugging purpose when the job fails.
        /// </summary>
        /// <param name="emailBody">The email body.</param>
        /// <param name="subject">The operation which failed</param>
        internal static void SendEmail(List<EmailMessage> messages, DateTime dateToCompare)
        {
            try
            {
                bool sendEmail;
                MailMessage msg = new MailMessage();
                string[] arr = ConfigurationManager.AppSettings["ToMails"].ToString().Split(‘;’);
                foreach (var mail in arr)
                {
                    msg.To.Add(mail);
                }
          &#16
0;     string fromEmail = ConfigurationManager.AppSettings["FromMail"].ToString();

                msg.From = new MailAddress(fromEmail);

                msg.Subject = string.Format("Email Age report");

                var htmlBody = EmailHelper.GetEmailBody(messages, dateToCompare, out sendEmail);
                if(!sendEmail)
                {
                    return;
                }

                msg.Body = "The following are the emails arrived in mailbox which are still not pending creation in CRM:<br />" + htmlBody;
              
                msg.IsBodyHtml = true;
                SmtpClient client = new SmtpClient();
                client.Host = ConfigurationManager.AppSettings["SMTPServer"].ToString();

                try
                {
                    client.Send(msg);
                }
                catch (Exception ex)
                {
                    if (ex is SmtpException)
                    {
                        try
                        {
                            client.Send(msg);
                        }
                        catch (Exception secondEx)
                        {
                            throw secondEx;
                        }
                    }
                    else
                    {
                        throw ex;
                    }
                }
                finally
                {
                    if (msg != null)
                        msg.Dispose();
                    if (client != null)
                        client.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        internal static string GetEmailBody(List<EmailMessage> messages, DateTime dateToCompare, out bool sendEmail)
        {
            sendEmail = true;
            var removeList = new List<EmailMessage>();
            var sb = new StringBuilder();

            sb.Append("<table style=’border: solid 1px black’ cellspacing=’0′><tr><th style=’width:200px;border:solid 1px black’>Email details</th><th style=’border: solid 1px black’>Age</th></tr>");

            foreach(var email in messages)
            {
                var timeSpan = dateToCompare.Subtract(email.DateTimeReceived);

                if(timeSpan.Days == default(int) && timeSpan.Hours == default(int) && timeSpan.Minutes == default(int) && timeSpan.Seconds == default(int))
    &#
160;           {
                    removeList.Add(email);
                    continue;
                }

                sb.Append("<tr><td style=’border: solid 1px black’>");
               
                sb.Append(string.Format("From: {0}{1}", email.From.Address, "<br />"));
                sb.Append(string.Format("To Recipients: {0}{1}", string.Join(";",email.ToRecipients.Select(t => t.Address)), "<br/>"));
                sb.Append(string.Format("CC Recipients: {0}{1}", string.Join(";", email.CcRecipients.Select(t => t.Address)), "<br/>"));
                sb.Append(string.Format("Email subject: {0}", email.Subject));

                sb.Append("</td><td style=’border: solid 1px black’>");
               
                sb.Append(timeSpan.Days + "d: " + timeSpan.Hours + "hh: " + timeSpan.Minutes + "mm: " + timeSpan.Seconds + "ss");
                sb.Append("</td>");
                sb.Append("</tr>");
            }

            sb.Append("</table>");

            if (removeList.Count == messages.Count)
            {
                sendEmail = false;
            }

            return sb.ToString();
        }

The subject of the email is ‘Email Age Report’ and below is a sample of the emails that the support team received after I configured this console application.

 

What I did, is I scheduled the console application in the task scheduler in one of CRM async servers and configured it to run every 30 mins. So if the support team receives a report in which there the email age is more than 30 mins or so, they know that there is some problem.

Did the customer like it? Oh Yeah! the kind of accolades our team got for this was in fact more than some of the complex requirements we implemented for the customer. And that too we achieved all this in 2 hours which was like icing on the cake.

Hope this helps!