Tuesday, January 30, 2007

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

11 comments:

  1. I have been searching for this very capability. One question relates to the reportpath parameter. When I create a custom report say abc.rdl and load it into CRM, what should the value of the reportpath be for the render to work?

    ReplyDelete
  2. If you go into Report Manager (check where Reports are installed, if you have a standard install of crm at http://crm:5555 then you'll probably find the report manager at http://crm/Reports) and select the report in question, check the report path at the top of the webpage. You should be able to figure it out if you get into report manager, its quite simple :).

    Gustaf

    ReplyDelete
  3. Great post, I was thinking the almost same function two weeks ago. Thanks!

    Andrew
    Melbourne

    ReplyDelete
  4. Nice post. i was actually looking for the kinda stuff. But i have one question regarding email attachments. Can we attach Docx files?

    Regards,

    Ayaz Ahmad
    MVP - Microsoft Dynamics CRM
    http://ayazahmad.wordpress.com

    ReplyDelete
  5. Ayaz,
    Yes, you can, but as far as I know there isn't an export method from SQL RS that exports to docx.

    What you need to do is load up the byte vector "result" with you docx-file. Then when "result" has been loaded with the data convert it as I did in my example with:

    "encodedData = System.Convert.ToBase64String(result);"

    The rest should be the same.

    I hope this description is enough, even though it is a bit short.

    Gustaf

    ReplyDelete
  6. I am missing something...

    Where does the method declaration go?

    Where do the parameters go?

    After it's all said and done, how do you create the email?

    Please explain more.

    ReplyDelete
  7. Vin:
    Please have a look at standard workflow dll-development. The method decalartion go into the file workflow.config in the assembly directory.

    Gustaf

    ReplyDelete
  8. Perfect. More such examples are needed, seeing as there is such a lack of doucmentation for CRM, especially 4.0.

    ReplyDelete
  9. Is it possible to create the email as text and not an attachement? We want to send shipping confirmations including details of the products that shipped.

    ReplyDelete
  10. Hello Gustaf!

    will this solution work with CRM 2011?

    ReplyDelete
    Replies
    1. Yes, with some modifications to work with the the CRM 2011 API:s and the SQL 2008 RS Webservice. The latter is a bit of a hassle but it can be done as I have done it. I am thinking of releasing it as a product on Dynamics Marketplace but havn't gotten around to it yet.

      Delete