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 Smile. 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);
                }
                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))
                {
                    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.

image

 

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. Smile And that too we achieved all this in 2 hours which was like icing on the cake.

Hope this helps!

Advertisements

About Debajit
I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

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

%d bloggers like this: