Project Description

CRM Queue Manager allows you to convert mail to cases in one or more queues in Microsoft Dynamics CRM 4.0. It handles various notifications and can automatically create contacts based on the sender's e-mail address. You can configure it to route e-mails sent from non-existent addresses to a temporary queue and send an e-mail requesting validation from the sender. A smart way to handle support spam and you can let your customers enter additional information such as name, phone, alternative e-mail address etc the first time they e-mail you. It uses emailaddress1, emailaddress2 and emailaddress3 to check for contacts if the sender is unresolved.

Introduction

I couldn't find a good way of handling incoming e-mail to queues that works well for a support organization. So, before upgrading to CRM 4.0 I put together a tool that has most of the functionality required.

CRM Queue Manager offers the following key functionality:
  • Simple installation/setup. CRM Queue Manager consists of a simple exe based on .NET Framework 2.0 that can be run in console mode or as a Windows service on any machine with access to the CRM server. No installations and no licenses based on number of CRM users. I haven't had the time to write a proper configuration wizard so all configuration is done using config file. However, sample settings comes with the release and it's pretty straight forward. Shouldn't take more than 10 minutes to get up and running.
  • Multiple queues. It monitors one or more queues for e-mail not already mapped to a case.
  • Validation. Spam is a huge problem when dealing with automatic case creation, even if you use greylist and e-mail washing. With CRM Queue Manager follows a simple aspx page that you can place outside your firewall so your customers can reach it. When someone sends an e-mail for the first time (i.e. they do not exist as a contact in CRM) the e-mail will be moved to a temporary queue and an e-mail will be sent to the sender asking him/her to validate their information. This also means that the user fills out his/her contact information (mobile phone, company name, alternate e-mail etc) so you don't have to :-). Once they've done that the e-mail will be mapped to the newly created contact and moved back to the support queue and the process continues as if the contact existed. This requires that you can open up a port in your firewall that can reach your CRM server and that you know basic ASP.NET to be able to modify the page to your liking.
  • Case number matching. A problem with CRM is that if you use tracking numbers in the subject line customers tend to mix them up with the case/ticket number. If a customer sends in an e-mail with the subject "What's the status of CAS-01234-YTRE?" Queue Manager will resolve that using a regular expression based on your organizational settings and match it with an existing case (open or resolved) and then relate the e-mail back to it. Set matchcaseticketnumber to true to activate. No more confusion (Well, it is. But at least the system is now in on the confusion...).
  • Contact creation. If you can't/don't want to use the verification page you can configure CRM Queue Manager to create the contact automatically. It first tries to locate an existing contact using emailaddress1, emailaddress2 and emailaddress3. A problem with other solutions out there is that they only use emailaddress1. Many times customers use different e-mail addresses which will result in multiple contacts and increased maintenance.
  • Parent account matching. This allows us to automatically resolve parent account based on existing contacts. Example: Someone e-mails the support queue using douglas.smith@contoso.com. If the contact doesn't exist in CRM the normal procedure will kick in (send verification mail or create the contact straight away). When creating the contact we will look up the first 10 contacts with domain name @contoso.com in CRM. If they all have the same parent account it's safe to assume Douglas also belongs to Contoso. Supports internal notification for both failed and successful matches. This solves a lot of time when you have many people from the same organization contacting you. It only uses emailaddress1 since emailaddress2/emailaddress3 is often gmail/hotmail type of addresses.
  • Notifications. You can set up notifications to be sent out when a verification mail is sent, when a case has been created and if an error occur when processing queues. It uses vanilla .NET Mail classes and doesn't clog up the CRM database with automatic replies of the type "A new case has been created...".
  • Template driven. All e-mail sent can be configured using a standard text editor and can either be html or plain text. The case template will be parsed and fields will be replaced with data from the case/e-mail
  • Cc/Bcc filtering. You can optionally filter out the creation of cases for e-mails sent where the queue is on the Cc or Bcc line. These fields should typically be used for FYI-type-of-mails. If they want to open up a case, they should e-mail you directly. You have the option of sending an e-mail saying "You e-mailed us but we didn't open up a case. If you want that to happen, please e-mail us directly and describe your problem in detail.".
  • Subject line filtering. You can configure CRM QueueManager to ignore e-mails with certain words in the subject field (eg. "AutoReply", "Out Of Office" etc).
  • Logging Weird things do happen. If an error or a warning occurs CRM Queue Manager will send an e-mail to a configurable e-mail address as well as log the error to the event log. You can also configure the service to log everything to a plain text file that will be renamed with a date stamp when it grows large.
  • Default case attributes. You can define values for additional case attributes when the case is created.
  • CRM 3.0 support There is a test version of CRM Queue Manager for CRM 3.0 available under the release tab. This has not been fully tested by myself but it seems to be working well. Note: This version has been frozen at version 1.5 and no development will be made for CRM 3.0. New versions only targets version 4.0.
  • Dynamics CRM Online support There is experimental support for Dynamics CRM Online from version 1.6. This has not been fully tested by myself due to lack of test environment. See below for more info on CRM Online.

Feel free to test and come with suggestions how it can be improved and become more useful. CRM Queue Manager does more or less what c360 EMail To Case does and adds some very useful features. And it's free.

Installation instructions

Service Installation

The ony requirement for CRM Queue Manager is .NET Framework 2.0 which is a free download from Microsoft. To install CRM Queue Manager simply download the latest release from this page. It's a zip file that consists of two directories that you can unzip to any location. The service directory is the main application and web contains they files you need to use the web verification functionality.

The file QueueManagerService.exe is the main application. It can be used either in console mode or as a Windows Service and be run from any machine with access to the CRM server. When run in console mode it will run through all queues configured to be monitored and then exit. Simply enter the file name without any parameters to run it. This is good when testing that all works and is properly configured. To install it as a Windows Service run install.cmd in the same directory. This will install it as service called CRM Queue Manager in manual startup mode and Local System as the user. You can change this to whatever you like afterwards.

The subdirectory templates contains the mail templates that will be used when sending automatic case e-mail and/or verification e-mail. Should be pretty self-explanatory how these work. Please see casenotify.htm for a full explanation of the template parser for case e-mail.

You need to setup a queue with an e-mail address and run through the E-Mail Router Configuration Manager included in Dynamics CRM 4.0 and make sure e-mails are being sent to the queue properly. Also, if you want to use the e-mail verification feature you need to create a temporary queue (name it Temporary Support or something). This is where e-mails waiting for approval ends up.

The file QueueManagerService.exe.config is the main configuration file. You can open it in any text editor. There are two sections you need to change, appSettings which contains settings for all queues and queues which is where you define settings for each queue you want to monitor.

appSettings

crmserviceurl
The full URL of the CRM service endpoint
organization
The organization of the CRM server
username
A service account used to access the CRM server. This must be a user in CRM with access to the queues, rights to create cases and create, update and remove e-mails for the entire organization.
password
The password of the CRM service account
domain
The domain of the service account
logeventlog
Whether to log to the windows application event log (true/false)
logerrornotify
The e-mail address to send a notification e-mail to if an error occur while processing queues.
logwarningnotify
The e-mail address to send a notification e-mail to if a warning occur while processing queues.
lognotifyfrom
The e-mail 'from' address of all log notification mails sent
logtofile
Log all activity to a file (will be rolled at 50kb). Full path to file. If empty, nothing is being logged.

queues

You can add as many queues as you want. E.g one for regular support, one for priority support, one for German support etc. The IDs below are the object IDs in CRM which you can find in the address line or by right clicking on the object and selecting Copy shortcut.

name
The name of the queue (just a description, doesn't have to match the real name)
queueid
The ID (GUID) of the queue to monitor
tempqueueid
The ID (GUID) of the temporary queue if you want to use the verfication feature (web site).
matchcaseticketnumber
When true the subject line of unrelated e-mails are scanned for a valid case number in the subject line. If a match is found it will be related to the case.
ignorelist
Semi colon separated list of words. If any of the words are matched in the subject field the e-mail is not processed (eg. "AutoReply", "Out of office"). Case insensitive.
moveignored
When set to true the mail ignored because of subject filtering is moved to the temp queue and removed after a certain time as specified by tempqueueage.
interval
How often the queue should be checked in seconds. Minimum is 10 seconds between each check. Don't set this too low.
tempqueueage
The number of hours e-mails moved to temp queue should stay there before being removed. 0 is default and will disable clearing of the queue.
createcontact
When true contacts are created automatically for contacts not found. Should be false when sendverificationmail is set to true.
sendverificationmail
When true an e-mail is sent out asking for verification. Should be false when createcontact is set to true.
allowonlyto
When true only e-mail sent directly to the queue (not CC/BCC) are allowed.
allowonlyto-reply
When true a reply is sent to the sender using the details below
allowonlyto-notify
One or more e-mail addresses separated by comma used to notify when we're cc/bcc:ed
allowonlyto-from
The from address used when replying.
allowonlyto-template
The full path to the template used in the reply. Will be parsed with {queueaddress} = queue e-mail address, {subject} = subject of e-mail.
allowonlyto-subject
The subject line of the reply
verificationmail-from
The e-mail from address of verification e-mail sent. Don't set this to the same as the queue e-mail address as people tend to reply to it. Use an {*} to use the e-mail address of the queue owner.
verificationmail-bcc
One or more e-mail addresses separated by comma used to notify of verification e-mail.
verificationmail-subject
The subject line of the verification e-mail
verificationmail-template
The full path to the template file for the verification e-mail.
verificationmail-usehtml
Whether to send as HTML or plain text (true/false)
verificationmail-url
The URL of the verfication page.
sendcasemail
Whether to send e-mail when new cases are opened
casemail-from
Either a GUID or an e-mail address. When a GUID it will be sent using CRM and thus tracked in CRM. When an e-mail address it will be sent using SMTP directly and not tracked in CRM. Use an {*} to use the e-mail address of the queue owner.
casemail-fromtype
Either queue or systemuser depending on the object type of the GUID specified in casemail-from.
casemail-notify
One or more e-mail addresses separated by comma used to notify of newly opened case e-mail. Use an {*} to use the e-mail address of the queue owner.
casemail-subject
The subject line of the case e-mail (will be parsed)
casemail-template
Full path to the template to use for the case e-mail. The template will be parsed, see templates\casenotify.htm for description.
casemail-usehtml
Whether to send case e-mail as HTML or plain text.
updatecasedescription
When 'true' the case description field will be updated with the content of the e-mail body.
caseattributes
The attributes to update when creating a case. See below for detailed instructions.
resolveparent
When 'true' an attempt to resolve the parent account is made.
resolveparent-notify
The addresses to inform of automatic resolves. Separate e-mail addresses with comma. Leave blank to disable notification.
resolveparent-from
The e-mail 'from' address in the resolve e-mail being sent.
sendcasenotificationmail
When 'true' a separate notification mail is sent when a case is created. Useful for notifying e.g. a support team.
casenotification-from
The e-mail 'from' address in the notification e-mail being sent. Use an {*} to use the e-mail address of the queue owner.
casenotification-to
The recipients (comma separated) that will be notified when a new case is created. Use an {*} to use the e-mail address of the queue owner.
casenotification-subject
The subject line of the e-mail being sent
casenotification-template
Full path to the template used for the notification. Please see templates\casenotify.htm for full documentation.
casenotification-usehtml
Whether to send notification e-mail as HTML or plain text
removerepliesfromqueue
When 'true' all incoming e-mails already related to a case (Regarding field) will automatically be removed from queue (still available in case history).
mapcasetoaccount
When 'true' the case will be created for the parent account of the contact rather than the contact itself. When setting 'createcontact' is set to 'true' the newly created contact is used for case creation.
parentaccountmissing
If setting 'mapcasetoaccount' is set to 'true' and contact doesn't have a parent account, the contact will be used for case creation. When 'false' the e-mail will be ignored and no case will be created.
setresponsiblecontact
When 'true' the case attribute responsiblecontactid will be updated with the contact's id.

Case attributes
You can define a set of attributes that will be updated when the case is created. This is done using the setting caseattributes. The format is:

attribute1#value|attribute2#value

Each attribute is separated by a pipe character ( | ) and the attribute and value is separated by #. Example:

prioritycode#2|caseorigincode#2|mycustomfield#sampletext

Please note that this feature does not attempt to replace the workflow in CRM but helps overcome the fact that it's not trivial to retrieve which queue a newly created case belongs to. This makes it easy to update a custom attribute for each queue and base a workflow on this value. Please navigate to http://yourcrmserver/yourorg/sdk/mdbrowser/entity.aspx?entity=incident for a complete list of attributes.

You also need to enter smtp settings in the mailSettings network section. Please see http://msdn2.microsoft.com/en-us/library/ms164242.aspx for details.

Web Site Installation

To setup the web site you need to configure a ASP.NET web site for ASP.NET 2.0 in IIS and update web.config's appSettings:

crmserviceurl The full URL of the CRM service endpoint
organization The organization of the CRM server
username A service account used to access the CRM server. This must be a user in CRM with access to the queues, rights to create cases and create, update and remove e-mails for the entire organization.
password The password of the CRM service account
domain The domain of the service account
logeventlog Whether to log to the windows application event log (true/false)
logerrornotify The e-mail address to send a notification e-mail to if an error occur while processing queues.
logwarningnotify The e-mail address to send a notification e-mail to if a warning occur while processing queues.
lognotifyfrom The e-mail 'from' address of all log notification mails sent
logtofile Log all activity to a file (will be rolled at 10kb). Full path to file. If empty, nothing is being logged.
resolveparent When 'true' an attempt to resolve the parent account is made.
resolveparent-notify The addresses to inform of automatic resolves. Separate e-mail addresses with comma. Leave blank to disable notification.
resolveparent-from The e-mail 'from' address in the resolve e-mail being sent.

Also, make sure ClientInformation.aspx (you can redesign it to your liking) and the bin directory is there. I haven't spent too much time on the layout/design nor made it feature rich.

You also need to enter smtp settings in the mailSettings network section. Please see http://msdn2.microsoft.com/en-us/library/ms164242.aspx for details.

Dynamics CRM Online support

In order to connect to use CRM Online you need to modify the following four settings in QueueManagerService.exe.config (and web.config if you are using the web app).

<appSettings>
<add key="crmserviceurl" value="https://dev.crm.dynamics.com/MSCRMServices/2007/Passport/CrmDiscoveryService.asmx" />
<add key="organization" value="crmorg" />
<add key="username" value="user@domain.com" />
<add key="password" value="password" />
...
</appSettings>


crmserviceurl
This should always be set to

https://dev.crm.dynamics.com/MSCRMServices/2007/Passport/CrmDiscoveryService.asmx

which is the discovery endpoint for CRM Online (see http://msdn.microsoft.com/en-us/library/cc151051.aspx for details).

organization
This should be set to the name of your organization in CRM Online. (<yourorg>.crm.dynamics.com)

username
Set this to a Live ID enabled e-mail address used to access CRM Live.

password
The Live ID password.

Please report any issues found in this experimental release. Note that this has not been tested properly as I'm lacking good access to CRM Online. Many thanks to Dave Yack (http://blog.davidyack.com) for helping out with development access!

Last edited Jan 24, 2010 at 9:33 PM by manso, version 21