D365: Download SharePoint document, attach to Email using PowerAutomate and Azure Function

In this post, we had discussed how to upload document to SharePoint using PowerAutomate. Here, we’ll discuss how to download that document and attach it to an email activity.

We’ll extend the same PowerAutomate we were using in the previous post and will also see how to execute HTTP Triggered Azure Function from PowerAutomate.

So, let’s get started with Azure Function that takes 3 strings as inputs:
1. EmailGUID: GUID of the email activity record to which the document will be attached
2. FilePath: SharePoint File Path
3. FileName: Name of the File to be downloaded and attached

Here’s the code:

 [FunctionName("AddAttachmentToEmail")]
        public static async Task<HttpResponseMessage> Run(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)] HttpRequestMessage req,
            ILogger log)
        {
var postedData = await req.Content.ReadAsStringAsync();       
            var input = JsonConvert.DeserializeObject<PowerAutomateInput>(postedData);
            if (input == null || string.IsNullOrWhiteSpace(input.EmailGUID) || string.IsNullOrWhiteSpace(input.FilePath) ||
                string.IsNullOrWhiteSpace(input.FileName))
            {
                return req.CreateResponse(HttpStatusCode.BadRequest, new
                {
                    summary = "Required Input is empty"
                });
            }

            var crmApi = await CRMWebApiHelper.GetCRMWebAPI(log);
            string sharePointAccessToken = Common.GetSharePointAccessToken();

            if (string.IsNullOrWhiteSpace(sharePointAccessToken))
            {
                return req.CreateResponse(HttpStatusCode.BadRequest, new
                {
                    summary = "Unable to get SharePoint access token."
                });
            }

            var SHAREPOINT_FILE_URL = string.Format("https://SHAREPOINTDOMAIN.sharepoint.com/sites/SITENAME/_api/Web/GetFileByServerRelativeUrl('/sites/SITENAME{0}')/$value", input.FilePath);
            var request = (HttpWebRequest)WebRequest.Create(SHAREPOINT_FILE_URL);
            request.Method = "GET";
            request.Accept = "application/json;odata=verbose";
            request.Headers.Add(HttpRequestHeader.Authorization, "Bearer " + sharePointAccessToken);
            request.ContentLength = 0;

            using (var response = request.GetResponse())
            {
                var byteArray = Common.GetByteArrayFromStream(response.GetResponseStream());
                var attachmentGUID = Common.AttachPDFDocumentToEmail(byteArray, crmApi, input.EmailGUID, input.FileName);
            }

            return req.CreateResponse(HttpStatusCode.OK, new
            {
                summary = "Request Processed"
            });
}
 public class PowerAutomateInput
    {
        public string FilePath { get; set; }

        public string EmailGUID { get; set; }

        public string FileName { get; set; }
    }

 public static byte[] GetByteArrayFromStream(Stream input)
        {
            using (var ms = new MemoryStream())
            {
                input.CopyTo(ms);
                return ms.ToArray();
            }
        }

 public static Guid AttachPDFDocumentToEmail(byte[] byteArray, CRMWebAPI crmApi, string emailId, string fileName)
        {
            var convertFileToBase64 = Convert.ToBase64String(byteArray);
            var attachmentDictionary = new Dictionary<string, object>();
            var emailObject = $"/activitypointers({emailId})";
            attachmentDictionary.Add("objectid_activitypointer@odata.bind", emailObject);
            attachmentDictionary.Add("mimetype", "application/pdf");
            attachmentDictionary.Add("objecttypecode", "email");
            attachmentDictionary.Add("body", convertFileToBase64);
            attachmentDictionary.Add("filename", fileName);
            var result = crmApi.Create("activitymimeattachments", attachmentDictionary).Result;
            return result;
        }

NOTE: GetCRMWebAPI method is defined here: https://ajitpatra.com/2020/01/15/using-service-bus-triggered-azure-durable-function-with-d365-ce/
GetSharePointAccessToken method is defined here: https://ajitpatra.com/2020/01/07/d365-call-sharepoint-api-from-plugin-custom-workflow-c-part-2/

After deploying the Azure Function and passing valid input as shown below to the Azure Function, we can verify that it’s working fine:
{
“FilePath”: “/lead/Jodie Ryan_a8c392f1f679ea11a811000d3a799888/a8c392f1-f679-ea11-a811-000d3a7998882020-04-14T02_15_50.pdf”,
“EmailGUID”: “f387bbd8-f57d-ea11-a811-000d3a799888”,
“FileName”: “a8c392f1-f679-ea11-a811-000d3a7998882020-04-14T02_15_50.pdf”
}

Now, let’s continue with our existing PowerAutomate from the previous post to get these inputs and execute Azure Function.

Let’s add Create a New Record Action at the end to create Email Activity.
Connector: CDS Environment
Action: Create a New Record
Entity Name: Email Messages
Subject: {Full Name of Lead Record} – Quotation – {formatDateTime(utcNow(),’ddMMyyyy_HHmmss’)}
To: Email Address of Lead Record
Regarding: Lead Record from the previous step

Add HTTP action to execute Azure Function.
Method: Post
URI: Azure Function URL
Body:
{
“FilePath”: Path of Create file – Quotation PDF Document step,
“EmailGUID”: Record Id of Create Email Activity Record step,
“FileName”: Name of Create file – Quotation PDF Document step
}

Now, let’s save the PowerAutomate and test it.
Go to a Lead record which has Preferred Accommodation. From Flow-> Run Create Quotation – Lead PowerAutomate.

Once the PowerAutomate is executed successfully, let’s go to Activities Tab and we can see that Email Activity has been created.

Opening the Email activity, in the Attachment grid, we can see that the pdf document has been attached.

Download the attachment and verify the content.

Hope it helps !!

4 thoughts on “D365: Download SharePoint document, attach to Email using PowerAutomate and Azure Function

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.