Using Sharepoint as an alternative to CRM for Mail Merge

 

We recently had a requirement where we wanted to automatically create Word documents which had pieces of information from Microsoft Dynamics CRM and were stored in a Sharepoint destination. MailMerge functionality has changed in Microsoft Dynamics CRM 2013 and it was looking like it would be a challenge to implement, but in the end, turned out to be quite simple using the power of Sharepoint rather than CRM to do the heavy lifting.

In this post we are going to explore a different mechanism for handling MailMerge.

Sharepoint Set Up

First of all, you will need to prepare your Document in Sharepoint to be used as a template. This should be a DOCX type and it should have ‘tags’ to denote where Sharepoint (rather than CRM Mail Merge) will place the relevant information just like a normal mailmerge. The template itself does not have to be in Sharepoint, it could be a filesystem directory for instance.

clip_image002

You will also need to prepare the Sharepoint metadata for the Document. To do this, you would navigate to the Document Library Settings area and add Columns as necessary.

clip_image004

clip_image006

Once you have added your metadata columns to the Sharepoint Library, every document stored in that library will have the accompanying metadata attributes available for completion. The illustration below shows the result of a completed merge.

clip_image007[4]

As long as there is a column reference in the Sharepoint metadata and the same tag name in the template document, then Sharepoint will handle the mailmerge for you. It should be noted that a user can also navigate to the actual document in Sharepoint and simply Edit the document properties directly as well. In the scenario above, the DeveloperName is left blank and will be completed by the user directly within Sharepoint at a later point in the process.

CRM Set Up

I wont pretend that this step is plug and play, but if you have a Developer handy, it is very easy to arrange. The final step to the process is to create a custom workflow/process step which will aggregate the information that is required from CRM and then squirt the finished document into Sharepoint. Since the process of actually implementing a Custom Workflow Step is fairly well known, I will concentrate instead on the actual nuts and bolts. Assuming that you have the information you require from CRM all ready, the object is to put the document into Sharepoint and let Sharepoint do it’s thing.

In this, the secret is not so much in the actual document or in doing anything to it, but more to do with the values being passed along with the document.

1. Add a WebReference to your project pointing to the Sharepoint URL. It will usually take the form of http://yourserver/_vti_bin/Copy.asmx

2. Create the Copy Service

clip_image009

1. Now, to actually tell Sharepoint which values to merge, you will need to create a series of FieldInformation objects. One for each of the attributes you wish to ‘mailmerge’. Note that the DisplayName parameter must match exactly the name value in Sharepoint that has been assigned.

clip_image011

The AccountName attribute is derived from CRM directly and will be sent to the Sharepoint document when it is posted.

1. Squirt all of your FieldInformation parameters into a FieldInformation array

clip_image013

5. Now, send the Document to Sharepoint using the CopyIntoItems method being sure to add the fieldInfoArray information you prepared above.

clip_image015

The Final Result

Once you have registered the workflow step and added it to an onDemand workflow, you can use this technique anywhere. Some additional ideas might be to extend the WorkflowStep so that it is more generic or perhaps uses dynamic attributes. You can store the document template in Sharepoint itself and retrieve it for use. In our implementation, the requirement was for a unique document name derived by using a Base36 encoded sequential number, all of which can be achieved inside the workflow.

When the user opens the newly created Document from Sharepoint, the mailmerge has completed using the metadata you passed in the fileInformation array and the CopyIntoItems method.

clip_image016

The final step would be to hook that workflow up to a button and using JavaScript, invoke the workflow for the record selected in the Grid.

function ExecuteWorkflow(entityId,workflowLocalName) {

var workflowId = getWork
flowId(workflowLocalName);

    alert("Executing workflow");

var url = Xrm.Page.context.getServerUrl();

var OrgServicePath = "/XRMServices/2011/Organization.svc/web";

    url = url + OrgServicePath;

var request;

    request = "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">" +

"<s:Body>" +

"<Execute xmlns=\"http://schemas.microsoft.com/xrm/2011/Contracts/Services\" xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\">" +

"<request i:type=\"b:ExecuteWorkflowRequest\" xmlns:a=\"http://schemas.microsoft.com/xrm/2011/Contracts\" xmlns:b=\"http://schemas.microsoft.com/crm/2011/Contracts\">" +

"<a:Parameters xmlns:c=\"http://schemas.datacontract.org/2004/07/System.Collections.Generic\">" +

"<a:KeyValuePairOfstringanyType>" +

"<c:key>EntityId</c:key>" +

"<c:value i:type=\"d:guid\" xmlns:d=\"http://schemas.microsoft.com/2003/10/Serialization/\">" + entityId + "</c:value>" +

"</a:KeyValuePairOfstringanyType>" +

"<a:KeyValuePairOfstringanyType>" +

"<c:key>WorkflowId</c:key>" +

"<c:value i:type=\"d:guid\" xmlns:d=\"http://schemas.microsoft.com/2003/10/Serialization/\">" + workflowId + "</c:value>" +

"</a:KeyValuePairOfstringanyType>" +

"</a:Parameters>" +

"<a:RequestId i:nil=\"true\" />" +

"<a:RequestName>ExecuteWorkflow</a:RequestName>" +

"</request>" +

"</Execute>" +

"</s:Body>" +

"</s:Envelope>";

var req = new XMLHttpRequest();

    req.open("POST", url, true)

// Responses will return XML. It isn’t possible to return JSON.

    req.setRequestHeader("Accept", "application/xml, text/xml, */*");

    req.setRequestHeader("Content-Type", "text/xml; charset=utf-8");

    req.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");

    req.onreadystatechange = function () { assignResponse(req); };

    req.send(request);

}

function getWorkflowId(workflowName) {

var odataSelect = Xrm.Page.context.getServerUrl() + ‘/XRMServices/2011/OrganizationData.svc/WorkflowSet?$select=WorkflowId&$filter=StateCode/Value eq 1 and ParentWorkflowId/Id eq null and Name eq \” + workflowName + ‘\”;

var xmlHttp = new XMLHttpRequest();

    xmlHttp.open("GET", odataSelect, false);

    xmlHttp.send();

if (xmlHttp.status == 200) {

var result = xmlHttp.responseText;

var xmlDoc = new ActiveXObject("Microsoft.XMLDOM");

        xmlDoc.async = false;

        xmlDoc.loadXML(result);

return xmlDoc.getElementsByTagName("d:WorkflowId")[0].childNodes[0].nodeValue;

    }

}

function assignResponse(req) {

if (req.readyState == 4) {

if (req.status == 200) {

            alert("Workflow successfully executed");

        }

    }

}

As you can see, although mail merge from a user point of view in CRM is now less useful, there are other ways and means to achieve largely the same result. There are of course limitations to this approach, chief of which is that a CRM developer is required to implement the custom workflow step in the first place, but this need not be much of an impediment.

Leave a Reply