Blogs

Attaching media files from SharePoint to Dataverse Email records

Inkey, February 25, 2021 2140 Views

I was wondering if we had any tools, workflow solutions, automatic processes or any connectors in PowerAutomate to support attaching the media files like images or videos from SharePoint folder to email messages before we send the emails. Basically, we wanted to automate the process of attaching the images and videos into the emails which were stored in the specific SharePoint folder to ease the manual process of attaching the media files by downloading from the SharePoint and attaching to Dataverse email records.

Since there is no existing Out-Of-The-Box solution for this requirement, we decided to go with the custom C# code to achieve the functionality. This blog is intended to walk you through the steps on how successfully we were able to do this using the custom C# code implementation.

The scenario is that all the contact related details like images, tutorial videos are stored in the SharePoint in the list called “Contact Details”. When the user want to send the email to the specific contact with these media files as attachments which are stored in the SharePoint, the user can simply click on the custom ribbon button on the email record. This will trigger all the backend processes to fetch the files from SharePoint and attach them to the email record automatically.

Initially we added the ribbon button on the email entity record in the Dataverse to invoke the JavaScript. From the JavaScript the C# WebAPI controller is called which is hosted at Azure. I have provided the required code of implementation as below:

JAVASCRIPT CODE TO CALL WEBAPI ON CLICK OF RIBBON BUTTON ON EMAIL RECORD

Here pass the “Primary Control” parameter from the ribbon workbench while calling the below function. So in the below function “formContext = PrimaryControl”

function RB_LoadImage_Click(formContext) {

    if (formContext!= null) {

        var isFormDirty = formContext.data.entity.getIsDirty();

        if (isFormDirty == true) {
            var alertStrings = {
                confirmButtonLabel: "OK", text: "Please save a record first and then load images."
            };
            var alertOptions = { height: 120, width: 260 };
            Xrm.Navigation.openAlertDialog(alertStrings, alertOptions).then(
                successCallback,
                errorCallback
            );
            return;
        }

        var emailId = formContext.data.entity.getId();
        if (emailId != null) {
            emailId = emailId.replace("{", "").replace("}", "");

            var confirmStringsDialog = { confirmButtonLabel: "Yes", cancelButtonLabel: "No", subtitle: "", text: "Are you sure want to load images or video link?", title: "Confirmation Dialog" };
            var confirmOptionsDialog = { height: 200, width: 450 };
            Xrm.Navigation.openConfirmDialog(confirmStringsDialog, confirmOptionsDialog).then(
                function (success) {
                    if (success.confirmed) {
                        Xrm.Utility.showProgressIndicator("Processing");
                        setTimeout(function () {
                            webApiURL = "https://yourapi.azurewebsites.net/api/MediaFilesFromSharePointToEmailController?emailId=" + emailId;

                            //Call WebApi to embed images and video links into the provided email record and update
                            var req = new XMLHttpRequest();
                            req.open("POST", webApiURL, false);//false - Sync, true - Async
                            req.onreadystatechange = function () {
                                if (this.readyState === 4) {
                                    req.onreadystatechange = null;
                                    if (this.status === 200 || //Success codes with return value
                                        this.status === 204 || //Success codes without return value
                                        this.status === 1223) { //Success codes for associate and dissociate requests
                                        result = "Success";
                                        if (this.response != null) {
                                            result = JSON.parse(this.response);

                                            //Close progress indicator
                                            Xrm.Utility.closeProgressIndicator();

                                            var entityFormOptions = {};
                                            var entity = formContext.data.entity;
                                            entityFormOptions["entityName"] = entity.getEntityName();
                                            entityFormOptions["entityId"] = entity.getId().replace("{", "").replace("}", "");
                                            Xrm.Navigation.openForm(entityFormOptions); //Reload current entity form

                                        }
                                    }
                                    else {
                                        Xrm.Utility.closeProgressIndicator();
                                        //error
                                    }
                                }
                            };
                            req.send("");
                        }, 1000);
                    }
                }
            );
        }
    }
}

API Controller

using System;
using System.Text;
using System.Web.Http;

namespace AttachMediaFilesService.Controllers
{
  public class MediaFilesFromSharePointToEmailController : ApiController
  {
    public IHttpActionResult Post(string emailId)
    {
      StringBuilder errorLogMessage = new StringBuilder(string.Empty);
      try
      {
        if (string.IsNullOrWhiteSpace(emailId) == false)
        {
          AddMediaFilesToEmailBody(emailId);
        }
        else
        {
          errorLogMessage.AppendLine("EmailId should not be null.");
        }
        return Ok(errorLogMessage.ToString());
      }
      catch (Exception ex)
      {
        string innerException = ex.InnerException != null ? ex.InnerException.Message : string.Empty;
        string errMessage = $"Exception:{ex.Message}{Environment.NewLine}InnerException:{innerException}{Environment.NewLine}ErrorLogMessage:{errorLogMessage.ToString()}";
        return Ok(errMessage);
      }
    }
  }
}

METHOD CALLED FROM API CONTROLLER

using Microsoft.SharePoint.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security;
using System.Text;
internal static void AddMediaFilesToEmailBody(string emailId)
        {
            try
            {
                IOrganizationService iOrganizationService = OrganizationService.GetOrganizationService();

                if (iOrganizationService != null)
                {
                    Guid emailIdGuid = new Guid(emailId);
                    Entity fetchEmailEntity = iOrganizationService.Retrieve("email",
                                                                                                                        emailIdGuid,
                                                                                                                        new ColumnSet("regarding_contact",        "description"));
if (fetchEmailEntity != null)
                    {
                        string description = fetchEmailEntity.GetAttributeValue<string>("description");
                        EntityReference contact = fetchEmailEntity.GetAttributeValue<EntityReference>("contact");
                        if(contact!= null)
                        {
                            //Get the SharePoint site and fetch the Document Location records related with the entity 
                            Guid sharePointSiteId = ”yourSharePointSiteId”;

                            Entity sharePointSite = iOrganizationService.Retrieve("sharepointsite",
                                                                                                                             sharePointSiteId,
                                                                                                                             new ColumnSet("sharepointsiteid", "absoluteurl"));
                            if (sharePointSite != null)
                            {
                                QueryExpression documentLocationQueryExpression = new QueryExpression("sharepointdocumentlocation");
                                documentLocationQueryExpression.ColumnSet = new ColumnSet("relativeurl");
                                documentLocationQueryExpression.TopCount = 1;
                                documentLocationQueryExpression.Criteria.AddCondition("regardingobjectid", ConditionOperator.Equal, employeeDetail.Id);
                                LinkEntity linkParentSiteOrLocation = documentLocationQueryExpression.AddLink("sharepointdocumentlocation",
                                                                                  "parentsiteorlocation",
                                                                                  "sharepointdocumentlocationid",
                                                                                   JoinOperator.LeftOuter
                                                                                   );
                                linkParentSiteOrLocation.EntityAlias = "parentSiteOrLocation";
                                linkParentSiteOrLocation.Columns.AddColumn("relativeurl");

                                EntityCollection documentLocationEntityCollection = iOrganizationService.RetrieveMultiple(documentLocationQueryExpression);

                                if (documentLocationEntityCollection.Entities.Count > 0 &&
                                    documentLocationEntityCollection.Entities != null)
                                {
//Get all the required parameters to connect with the SharePoint such as site-url, relative-url, username and password
                                    Entity documentLocation = documentLocationEntityCollection.Entities[0];

                                    string doucmentLocationRelativeUrl = documentLocation.GetAttributeValue<string>("relativeurl");
                                    string parentSiteRelativeUrl = string.Empty;

                                    AliasedValue aliasedValue = documentLocation.GetAttributeValue<AliasedValue>("parentSiteOrLocation.relativeurl");
                                    if (aliasedValue != null)
                                    { parentSiteRelativeUrl = (string)aliasedValue.Value; }

                                    string relativeURL = $"/{parentSiteRelativeUrl}/{doucmentLocationRelativeUrl}";
                                    string sharePointFileName = string.Empty;

                                    string siteURL = sharePointSite.GetAttributeValue<string>("absoluteurl");
                                    string userId = ”yourSharePointUserName”                                   
                                    string userPassword = ”yourSharePointPassword”

        //Now connect with the SharePoint site using the client context object as below and get the SharePoint List based on the Title
                                    ClientContext clientContext = new ClientContext(siteURL);

                                    SecureString password = new SecureString();
                                    userPassword.ToCharArray().ToList().ForEach(p => password.AppendChar(p));
                                    clientContext.Credentials = new SharePointOnlineCredentials(userId, password);

                                    Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Contact Details");
                                    clientContext.Load(spList);
                                    clientContext.ExecuteQuery();

                                    if (spList != null && spList.ItemCount > 0)
                                    {
                                       //Get the folder and the files in the list which is retrieved from the above code
                                        Folder folder = clientContext.Web.GetFolderByServerRelativeUrl(relativeURL);
                                        clientContext.Load(folder);
                                        clientContext.ExecuteQuery();

                                        clientContext.Load(folder.Files);
                                        clientContext.ExecuteQuery();
                                        FileCollection fileCollection = folder.Files;
                                        List<string> lstFile = new List<string>();
                                        StringBuilder sbContentToEmbedInEmail = new StringBuilder(string.Empty);

                                       //Open each file, load and execute to get the document name, filename and its extension to  get the file format(image or video)
                                        foreach (Microsoft.SharePoint.Client.File file in fileCollection)
                                        {
                                            ClientResult<Stream> clientResultStream = file.OpenBinaryStream();

                                            clientContext.Load(file);
                                            clientContext.ExecuteQuery();
if (clientResultStream != null)
                                            {
                                                clientContext.Load(file.ListItemAllFields, item => item["EncodedAbsUrl"], item => item["FileRef"], item => item["FileLeafRef"]);
                                                clientContext.ExecuteQuery();
                                                string fileURL = file.ListItemAllFields["EncodedAbsUrl"].ToString();
                                                string fileName = file.ListItemAllFields["FileRef"].ToString();
                                                string fileExtension = Path.GetExtension(fileName);
                                                string documentName = file.ListItemAllFields["FileLeafRef"].ToString();

                                               //For Video Files
                                                if (fileExtension.Equals(".mp4", StringComparison.InvariantCultureIgnoreCase))
                                                {
                                                    sbContentToEmbedInEmail.AppendLine($"{documentName}: </br>");
                                                    sbContentToEmbedInEmail.AppendLine($"<a href='{clientContext.Web.CreateAnonymousLinkForDocument(fileURL, ExternalSharingDocumentOption.View)}'>{clientContext.Web.CreateAnonymousLinkForDocument(fileURL, ExternalSharingDocumentOption.View)}</a></br>");
                                                }
                                               //For Image Files
                                                else if (fileExtension.Equals(".jpg", StringComparison.InvariantCultureIgnoreCase) ||
                                                         fileExtension.Equals(".jpeg", StringComparison.InvariantCultureIgnoreCase) ||
                                                         fileExtension.Equals(".png", StringComparison.InvariantCultureIgnoreCase))
                                                {
                                                    using (MemoryStream memoryStream = new MemoryStream())
                                                    {
                                                        clientResultStream.Value.CopyTo(memoryStream);
                                                        memoryStream.Position = 0;

                                                        byte[] newFile = memoryStream.ToArray();
                                                        string fileContent = Convert.ToBase64String(newFile);
                                                        sbContentToEmbedInEmail.AppendLine($"{documentName}: </br>");
                                                        sbContentToEmbedInEmail.AppendLine($"<img style='display:block; width:100px;height:100px;' id='base64image' src = 'data:image/jpeg;base64,{fileContent}' /></br>");
                                                    }
                                                }
                                            }
                                        }
//Add the files to the email record and update email entity
Random random = new Random();
                                        description = $"{description}<div id=images{random.Next().ToString()}>{sbContentToEmbedInEmail.ToString()}</div>";

                                        Entity emailEntity = new Entity("email", emailIdGuid);
                                        emailEntity.Attributes["description"] = description;
                                        iOrganizationService.Update(emailEntity);
                                    }
                                }
                            }
                        }
                  }
                }
            }
            catch (Exception ex)
            { throw new Exception(ex.Message); }
        }

This is how the media files can be attached from the SharePoint to the existing email entity record. The user can then send this email to the intended employee email address.

I hope this helps!

ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://www.inkeysolutions.com/microsoft-power-platform/power-app/atm-inspection

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator