Sync Dataverse Email Attachments to SharePoint using Power Automate

The Dynamics 365 App for Outlook (which you can also use even if you don’t have Dynamics 365…) is great for synchronising customer communications from your emails into Dynamics 365 or your Model-driven Power App so you can have all your interactions with a customer in one place. From here on in Dataverse = Dynamics 365 or Model-driven Power App.

When you ‘set regarding’ an email in outlook, an email record will be created in Dataverse, linked to the relevant people and also the specific ‘regarding’ record chosen. If the email has any attachments, each attachment will be created as a note, with a file attached which is helpful but also rather restrictive. Instead, it would make more sense to upload these files into the relevant SharePoint folder of the regarding object because of all the below reasons of why storing files in Dataverse is not a good idea.

Why not store files in Dataverse?

Lots of reasons but here is a few.

• Capacity is limited based on your licensing/storage subscriptions, buying additional storage can become very costly

• Only users with access to dataverse can access the files

• No document collaboration or version history. You cannot open and ‘edit’ the files without downloading then -re-uploading them.

You can review the capacity usage for your organisation by logging in at at https://admin.powerplatform.microsoft.com/resources/capacity

Document Management in SharePoint

There is a really neat integration between Dataverse & SharePoint that lets you manage documents in Dataverse, which are seamlessly stored in SharePoint. Further info on setting this up can be found here -> https://docs.microsoft.com/en-us/power-platform/admin/manage-documents-using-sharepoint

Store Dataverse files in SharePoint

Our good friend flow likes Dataverse and SharePoint a lot, so he is the ideal guy to help up synchronise the files from Dataverse into SharePoint. Our Document Management in SharePoint is already set up for our Clients (contacts), so lets jump in.

When an email is sent or received in Dataverse

We only want to upload the attachments once an email has been sent or received. This is the status that any email will be in if it is tracked from Outlook into Dataverse. So the trigger is when a row is added in the Email table in Dataverse. Filtering on statecode eq 1.

Get the files attached to the email

Each email attachment is created in Dataverse as an Attachment, be sure to use the right ‘Attachment’ table as in some cases there is more than one. The one we need to use here is the ‘activitymimeattachments’ one. The filter lists all the attachments for this particular email and also filters out any .png or .jpg images. The reason for this was to stop the flow from uploading images in peoples signatures being uploaded to the SharePoint folders (ideally I would work out a smarter way to identify and exclude only the signature images whilst allowing actual attached images to be uploaded, so feel free to suggest ideas on that one!).

(_objectid_value eq '@{triggerOutputs()?['body/activityid']}' and not contains(filename, '%.png%') and not contains(filename, '%.jpg%'))

Check for attachments

First we need to check if the email has any attachments (which are not images), if not - cancel the flow and save yourself some API calls!

empty(body('Get_Attachments')?['value'])

Switch based on the ‘Regarding’ type

In the ‘Yes’ branch, add a switch statement. This example will only sync the files set regarding to Clients (contacts) or Opportunities but you could easily expand this to any other tables for which you use SharePoint Document Management. Ensure you use the plural table name for the regarding type switch values.

Document Locations, Folders & URLs

A ‘Document Location’ is what connects the SharePoint folder, to a particular Dataverse record. We have set up the folder structure ‘based on entity’, where the entity is the Client (contact). If you have not then you would use the same to step method for Contact as you would for Opportunity to create the documents URL, as the folder is at the top level rather than nested in the Client (contact) folder.

We are going to save all the attachments in a folder called ‘Email Attchments’ which is held in the Clients (contacts) folder. Each document location has a ‘relative URL’ but this is not the complete URL we need to tell the SharePoint action where to create the file. If the flow finds an existing ‘Email Attachments’ folder then it will place the files here, otherwise it will create it. No need to check and initiate the create as a separate step

Get the Email Attachments folder location

Contact

From the contact we simply need to know the relative URL of the Clients document location. Using a ‘List Records’ action we can retrieve the Document Location linked to the Client, the only column we need is ‘relativeurl’

Then we use a Compose step to add ‘contact/’ before, then the FIRST relative URL from our list records (there should only be one anyway, but this avoids getting stuck in an unnecessary loop) and then the name of the attachment folder afterwards ‘/Email Attachments’

_regardingobjectid_value eq @{triggerOutputs()?['body/_regardingobjectid_value']}
first(outputs('GetDocLocRegarding')?['body/value'])?['relativeurl']

Opportunity

From the email, which is linked to the opportunity, we need to get back up to the Clients document location. Using a ‘Get Record’ step to retrieve the Opportunity, we then have the GUID for the customer.

Then using the same ‘List Records’ action as above we can retrieve the Document Location linked to the Client, this time using the the Client (value) from the get record step, again the only column we need is ‘relativeurl’.

Then we use a Compose step to add ‘contact/’ before, then the FIRST relative URL from our list records (there should only be one anyway, but this avoids getting stuck in an unnecessary loop) and then the name of the attachment folder afterwards ‘/Email Attachments’

first(outputs('GetDocLocClient')?['body/value'])?['relativeurl']

For each email attachment

We will use a loop here to go through each email attachment in the ‘Get Attachments’ step.

First we create the file in SharePoint, using the generated folder URL. We convert the ‘body’ of the attachment from Base 64 into Binary. Using the original attachment file name. Note: I have used an environment variable for the SharePoint Site Address as this makes it easier to promote through to production without directly updating the flow, but this is entirely optional.

Finally, once you are happy your flow is working correctly, you can add a step to delete the file from Dataverse, as there is no need to have it in both places and unnecessarily eating away your Dataverse storage

outputs('Get_Attachments')?['body/value']
outputs('ClientAttachmentsRelativeURL')
outputs('CLientAttachmentsRelativeURL2')
base64ToBinary(items('Apply_to_each')?['body'])

Summary

Storing email attachments in SharePoint is a much cleaner and efficient way to store documents - it’s what SharePoint was made to do! This flow doesn’t cover ‘tracking’ of emails as there could be multiple recipients for teh email, in which case you would end up creating multiple copies of the same file in SharePoint - yuck!

Hopefully one day these documents will automatically be stored in SharePoint rather than Dataverse if you have the SharePoint integration configured, it’s on my Christmas wish list 😉