Automate customer sales material requests with Power Automate, Dataverse and SharePoint

Has the scenario ever arisen, when a client or multiple clients have wanted you to automatically send an email with an attachment from CRM, such as sales literature, to a customer to save staff a large proportion of their working day manually processing this on a day-to-day basis?

In our scenario our client wanted to automatically send a brochure or brochures to a customer automatically from CRM when they rang up and/or emailed in for a product information.

In the past I would have said the easiest way to do this was to upload the pdf brochure files to a public space on the internet and then when a client requested information on a specific product, send them the link to the pdf… job done!

This works, but also means every time they have new sales material, they are then looking outside CRM to access it, never mind the hassle of speaking to the website host to get the document uploaded to the right place.

Plus, in our scenario the client wanted to attach the PDF, not just send a link and this PDF needed to be stored in SharePoint.

So where to start? After doing some research around the subject there were an awful lot of blogs around sending emails using Power Automate, but primarily using the action Send an Email V2.

Now for me, this is by far the easiest was to send an email with Power Automate, but my main issue with this is using this action is it runs outside the DataVerse and therefore when the email is sent you as the user have zero visibility of it in the timeline of the record.

So, I researched for the answer and found pretty much nothing when it comes to attaching a file from SharePoint attaching it to an email and sending it with a DataVerse ‘Email Message’ action.

So let go through it, here the client wanted to be able to upload a brochure pdf to SharePoint location related to a Brochure request record (custom table) and then based on the information in a lead record send that brochure.

Below is a high-level diagram of the process:

So, let's build out the flow, in this scenario with a triggering it with an on-demand trigger.

Once we trigger the flow and get the lead record, the next step is to create the email. Don’t forget to set the regarding leads field otherwise you won’t see it in the timeline of the lead.

In the next step as we have more than one brochure (and therefore brochure record), we need to confirm which brochure record we are selecting with a condition.

Now in the apply to each we get the record ID of the brochure record.

Now we get the files (or file in this case) related to the SharePoint folder related to the record ID we just got from above.

Next, we use the Get file content SharePoint action to get the file identifier.

Now, the Entity field is an oddity, to make this work, select custom value and enter the word email.

Now in the body use the base 64 expression to convert the file into something Dataverse can read.

Finally use a bound action to send the email. 

Now, all being well look at the timeline on your lead record and you should see the email.

Having this visibility in Dataverse is great as everyone can see the email that was sent and when it was sent. The other kicker is you could write in the outbound email ‘If you have any other queries please reply to this email’ in doing so the reply email would go straight back into the timeline of the lead for all to see. This is not something you could do with the action Send an Email V2.

My only other thought which I haven’t included here is, it would be worth deleting the attachments from the email record once it’s sent. If not, you are going to end up with lots and lots of precious (and expensive) Dataverse storage being filled with hundreds of PDFs.

Being able to perform a task like this with Power Automate, Dataverse and SharePoint could potentially save staff hours of manually processing emails. Also consider at the same time Power Automate could be configured so the PDFs went straight to the customer removing any manual intervention at all.

I hope you found this blog helpful, and feel free to get in touch if you feel like this solution like this could benefit your business.