Today I’d like to highlight an approach to handling the mass distribution of email and letter activities in Microsoft Dynamics CRM. This is a strategy you can use when you have the need to distribute email and letter activities to a large audience, similar to marketing campaigns, but whenever the intended recipients of those activities are determined at random throughout the day.
Let’s face it. Even in this day and age not everyone has an email address. When you need to run a campaign and communicate with a wide variety of people, one challenge is in building a system that can handle both types of consumers. On the one hand, people with an email address could receive an email immediately, and people without an email address….well, you still want to communicate with them but it’s not quite as immediate or easy to pull off.
For example, let’s say you wanted to build a communication module that will notify applicants if they’ve been accepted or denied. The system should send an email to any applicant that has an email address. The system should also assist you printing a letter for any applicant that does not have an email address.
Since applicants can be accepted or denied at various random times throughout the day. Emails are easy enough as the system will just send them immediately. But for letters, how do you keep track of who needs one? How can you print them easily and know who you’ve already printed one for?
Enter Microsoft Dynamics CRM. In this scenario, there are really three key steps in the process. I call it the Trigger, Print, Complete (TPC) Method.
Step 1: Trigger
In the TPC Method, first you build workflows in CRM that are triggered by system events. The workflows send emails or create activities, depending on the recipient’s profile. For example, the workflow below checks first to see if the recipient has an email address. If so, then it sends an email immediately. If not, then it creates a letter activity immediately, giving it a specific subject line. This letter activity will remain open for the next step.
Step 2: Print
The next step is to locate recipients who need a letter. Using CRM’s advanced find feature, you can build a search that will find any recipient who has a letter activity with a certain subject line that is still in the open status. Letters in the open status still need to be printed and sent.
I recommend that you save this view or create a system view so that you can find the recipients with just a few clicks the next time:
Once you have the list in front of you, now you can take two paths. First, you can use the native CRM mail merge to author and print a letter for each recipient. You can start with an existing template or create a new one. Once you are finished, you can save the template for next time.
Mail merge is great for most letters but does have two limitations you should be aware of. First, there is a limit to the number of records you can mail merge at any one time. The limit is 5000 as of this writing. Second, mail merge can pull data in from the recipient, or from any related or parent record where there is an N:1 (many-to-one) relationship. It does not allow you to pull in information from related records where there is a 1:N (one-to-many) relationship. If you think about it, it kind of makes sense since Microsoft Word expects a single row by row source of recipients, there is no way to tell Word how to handle one-to-many type “detail” records for your recipient.
If you find you need to get around the 5K or one-to-many limits, you can create a SQL Reporting Services report to fill the gap. In this case you build the report, typically in Visual Studio or Report Builder, and then publish the report to CRM. When you build the report, you can bring in data from anywhere in CRM, and users will be able to print over 5,000 pages, assuming the printer can handle the load. Be sure to set the “display in” related records options when you upload the report, so that the report is available in the same toolbar as the Word mail merge button.
Step 3: Complete
Finally, you need to tell CRM that the recipients are now done so they do not show up in the next search. In general there are two ways to do this. First, you can close each letter activity individually. I prefer to create a “Close Activity” workflow for this so that you can highlight several activities at one time and then close them in batch.
Second, you can write a workflow that you can run from the recipient level to close the related activities. This one is a bit trickier. You will need to write a Microsoft .NET custom workflow assembly to find all related activities with a specific subject line and then build a workflow at the recipient level to call the custom assembly. If you have the basic .NET skills to do it, that method is better since you can run the workflow using the same advanced find search you created in Step 2.
That’s it – so there are three steps in the TPC Method: Trigger, Print, and Complete. Most of this uses native CRM functionality, with a few options to enhance the end user experience with Microsoft SQL Server Reporting Services and a custom plug-in if needed.