Sending an email with associated excel sheet with details of child records is a generic requirement and also great way to pass child record details to a customer. For example, you may need to send an email to a customer with quote details and associate quote products as a excel attachment.
In my example I am sending email to a Account and Sender is a Queue (you may obviously use a System user). I am sending Opportunity details and associated excel will carry Opportunity Products. This Email will be shown in timeline of Opportunity hence regarding Object would be the Opportunity.
I have two separate code snippets here.
1) Sending the Email with Template and Excel
What we need to understand is we should create the Email first and then send as two steps.. This way, we get a room prior to sending, to attach the excel. Note how we pass different Ids like Sender, Template, Receiver and Regarding Object here. Also notice that we pass the excel as the attachment body.
Public void SendEmailWithPaymentEvaluationDetails(Guid templateId, Guid toAccountId, Guid fromQueueId, Guid regOpportunityId)
{
// Initiate Email
InstantiateTemplateRequest request = new InstantiateTemplateRequest()
{
TemplateId = new Guid(templateId),
ObjectId = invoiceRequest.Id,
ObjectType = invoiceRequest.LogicalName
};
InstantiateTemplateResponse response = (InstantiateTemplateResponse)OrgService.Execute(request);
Entity email = response.EntityCollection[0];
Entity Fromparty = new Entity("activityparty");
Entity Toparty = new Entity("activityparty");
Toparty["partyid"] = new EntityReference("account", toAccountId);
Fromparty["partyid"] = new EntityReference("queue", fromQueueId);
email["from"] = new Entity[] { Fromparty };
email["to"] = new Entity[] { Toparty };
email["directioncode"] = true;
email["regardingobjectid"] = new EntityReference("opportunity", regOpportunityId);
Guid emailId = OrgService.Create(email);
// Link the Attachment
Entity attachment = new Entity("activitymimeattachment");
attachment["subject"] = "OpportunityId Product List";
attachment["filename"] = "OpportunityId Product List.xlsx";
attachment["body"] = Convert.ToBase64String(CompileExcelFile(regOpportunityId));
attachment["mimetype"] = "application/vnd.ms-excel";
attachment["attachmentnumber"] = 1;
attachment["objectid"] = new EntityReference(email.LogicalName, emailId);
attachment["objecttypecode"] = email.LogicalName;
OrgService.Create(attachment);
// Send Email
SendEmailRequest sendEmailRequest = new SendEmailRequest
{
EmailId = emailId,
TrackingToken = string.Empty,
IssueSend = true
};
SendEmailResponse sendEmailResponse = (SendEmailResponse)OrgService.Execute(sendEmailRequest);
}
2) Compilation of Excel
Here we compile the excel which is called in above method when preparing the attachment.
In this technique, we need to have a view created in
Opportunity Product and retrieve its Id to be used here. That's the Id you see in
SavedQuery. Interestingly, though we assign an id of a saved view, we are defining dynamically what we need in the excel via our own Fetch query. Under grid section, we adjust the column widths etc.
public byte[] CompileExcelFile(Guid regOpportunityId)
{
var exportToExcelRequest = new OrganizationRequest("ExportToExcel");
exportToExcelRequest.Parameters = new ParameterCollection();
exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("View", new EntityReference("savedquery", new Guid("{4c523f5b-e8c5-4cb5-bc83-bf4ef934342d}"))));
string stringFetchXml = @"<fetch distinct='false' no-lock='false' mapping='logical' returntotalrecordcount='true'>
<entity name='opportunityproduct'>
<attribute name='lineitemnumber' />
<attribute name='productname' />
<attribute name='description' />
<attribute name='baseamount' />
<filter>
<condition attribute='opportunityid' operator='eq' value='{0}' />
</filter>
</entity>
</fetch>";
exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("FetchXml", String.Format(stringFetchXml, regOpportunityId.ToString())));
exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("LayoutXml", @"
<grid name='resultset' object='2' jump='lineitemnumber' select='1' icon='1' preview='1'>
<row name='result' id='opportunityproductid'>
<cell name='lineitemnumber' width='100' />
<cell name='productname' width='200' />
<cell name='description' width='300' />
<cell name='baseamount' width='125' />
</row>
</grid>"));
exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("QueryApi", ""));
exportToExcelRequest.Parameters.Add(new KeyValuePair<string, object>("QueryParameters", new InputArgumentCollection()));
var exportToExcelResponse = OrgService.Execute(exportToExcelRequest);
if (exportToExcelResponse.Results.Any())
return exportToExcelResponse.Results["ExcelFile"] as byte[];
else
return null;
}
Hope this helps!
