How to create an CRM email with a report server attachment as a pdf

In Microsoft CRM there are several methods to create great looking documents with mailmerge and the crm emails can be created with dynamic data. However, it is quite complicated to create automatic mails with data from one main entity and several sub entities. The most common example being an order confirmation, with data from both the order head and the order detail lines.

To make it a bit flexible, I created a function that could be called from the workflow engine.

Here is the method declaration that I will use bellow:

public string SendMailWithReport(System.Guid OrderId, string subject, string body, string reportpath, string callerXml)


The parameters are as follows:
OrderId – A Guid containing the orderid to be used as a parameter to the report.
Subject – a string that will contain the mail subject.
Body – a string that will contain the mail body
Reportpath – to make it a bit more flexible, the report path is not hard coded but can be inputed as a parameter,
callerXml – standard handling for getting the caller data to enable impersonation.

The first thing we want to do is to get the pdf from the report server.

We’ll store the binary pdf in the byte-array called result. When this is done, we’ll encode this into a string called encoded data. The rest is stuff that is needed to make this happen.

string encodedData = "";

//Create Report PDF
ReportingService rs = new ReportingService();
rs.Credentials = new System.Net.NetworkCredential(username, passwd, domain);
Byte[] result;

string encoding;
string mimetype;
ParameterValue[] parametersUsed;
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "CRM_OrderId";
parameters[0].Value = "{" + OrderId.ToString() + "}";

Warning[] warnings;
string[] streamids;

result = rs.Render(reportpath,
"PDF",
null,
null,
parameters,
null,
null,
out encoding,
out mimetype,
out parametersUsed,
out warnings,
out streamids);

encodedData = System.Convert.ToBase64String(result);

The most complex part of this which took me the most time, was the “Render” method of the reporting server web service. I use it this way because it works, don’t ask me what all the parameters really are, I don’t know.

The next part is to create the CRM mail. This is quite straight forward, if you are used to the crm web service.

The “from” and “to” can be set to anything that can be used as a “to” or “from” in the CRM GUI.

CrmService service = new CrmService();
service.CallerIdValue = new CallerId();
service.CallerIdValue.CallerGuid = GetCaller(callerXml);
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

//Get current user
WhoAmIRequest userRequest = new WhoAmIRequest();
WhoAmIResponse user
= (WhoAmIResponse) service.Execute(userRequest);

//Load salesorder and account objects.
salesorder so = (salesorder)service.Retrieve(EntityName.salesorder.ToString(), OrderId, new AllColumns());

account acc = (account)service.Retrieve(EntityName.account.ToString(), so.customerid.Value, new AllColumns());

email em = new email();
activityparty from = new activityparty();
from.partyid = new Lookup();
from.partyid.type = EntityName.systemuser.ToString();
from.partyid.Value = user.UserId;
em.from = new activityparty[] {from};

activityparty toparty = new activityparty();
toparty.partyid = new Lookup();

toparty.partyid.type = EntityName.account.ToString();
toparty.partyid.Value = acc.accountid.Value;

em.to = new activityparty[] {toparty};

em.subject = subject;
em.sender = "test@test.com";
em.regardingobjectid = new Lookup();
em.regardingobjectid.type = EntityName.salesorder.ToString();
em.regardingobjectid.Value = so.salesorderid.Value;

em.description = body;
em.ownerid = new Owner();
em.ownerid.type = EntityName.systemuser.ToString();
em.ownerid.Value = user.UserId;

Guid createdEmailGuid = service.Create(em);

Now, we have created the email. You can see it in CRM if you like.

The last part is now to create the attachment on the email as the pdf that we downloaded from the report server in the first part of this walk-through.

activitymimeattachment ama = new activitymimeattachment();
ama.activityid = new Lookup();
ama.activityid.type = EntityName.email.ToString();
ama.activityid.Value = createdEmailGuid;
ama.body = " ";
ama.mimetype = "application/pdf";
ama.attachmentnumber = new CrmNumber();
ama.attachmentnumber.Value = 1;
ama.filename = "A filename";
Guid createdAttachment = service.Create(ama);

//Upload file
// Create the Request Object
UploadFromBase64DataActivityMimeAttachmentRequest upload = new UploadFromBase64DataActivityMimeAttachmentRequest();

// Set the Request Object's Properties
upload.ActivityMimeAttachmentId = createdAttachment;
upload.FileName = "attachmentfilename.pdf";
upload.MimeType = "application/pdf";
upload.Base64Data = encodedData;

// Execute the Request
UploadFromBase64DataActivityMimeAttachmentResponse uploaded = (UploadFromBase64DataActivityMimeAttachmentResponse) service.Execute(upload);

The last part, if you want to, is to send the mail:

SendEmailRequest req = new SendEmailRequest();
req.EmailId = createdEmailGuid;
req.TrackingToken = "";
req.IssueSend = true;
SendEmailResponse res = (SendEmailResponse)service.Execute(req);

I have made some simplifications, like removing try-catch clauses, which you really should use, but the code has apart from that been cut-n-pasted from a working application and should work.

Gustaf Westerlund
CRM and SharePoint Consultant

Humandata AB
www.humandata.se