DataVerse Email Templates in Flows

Most apps need emails sent out, very likely from Power Automate. As a developer the thing I always hate is doing work. Well, boring work. A lot of Flow writers have a tendency to embed the email content into the Flow. Need a grammar change? Code release. Too much work.

Previously I would create a DataVerse (or SharePoint list) custom entity, with fields for each email subject and body (and file attachment), and display a form in the admin section of the app. The email body would be a RTF which admin users could then use to write their own emails. I would provide a side help pane of all the ‘variables’ they could use in the body, so #FULLNAME# would be replaced with the contact/user’s fullname from the records. Example:

But I found for some reason formatting and inline images do not work quite so well with these RTFs as they do with the out of the box email templates. So, to save effort (a good thing), I’ve now switched to using the built in Email Templates with my idea of variables.

Add Email Templates to the site map (or hardcode links to the templates in the site map if better for UX purposes), and create a New template. In my usage, the Category is not important, unless they may be used on individual records as well.

Create some content, adding any cool look desired, and add a couple of variables. Here I’m going to send a hello to a contact record, and include an attachment (or as many as desired).

Then at some point in the business process, a Flow will be called which will send an email to that contact, using this template.

Make a note of the GUID of the template (from the URI bar) as this will remain constant across environments as the templates will be in the solution.

The Flow

Produces this email with styling and attachments:

Breaking it down. First fetch a contact record, hardcoded in my Flow. Initialize my variables to hold the email body, subject and recipients (activity parties). This Flow is simple, but usually, there will be different paths to get different templates, but all using the same variables and email send at the end. For those not aware how to send emails from DataVerse, see my earlier post.

Now to the fun:

Get the email template from the ID (it will travel in the solution so hardcoding is fine).

Note the expand query – this gets back any attachments with the fields needed. This took me a while to work out !

Expand query : template_activity_mime_attachments($select=filename,mimetype,body)

The fields returned for subject and body which will be used are :

Other body/subject fields are returned but these seem to work best. The subject comes wrapped in style tags so those need to be removed:

<div data-wrapper=”true” style=”font-family:’Segoe UI’,’Helvetica Neue’,sans-serif;font-size:9pt;”>An Incredible Subject</div>

I do this by using slice(), to slice out 2 characters after “> up to the trailing </div>. This then just returns “An Incredible Subject”.

slice(
outputs('Get_email_template_row_by_ID')?['body/subjectsafehtml'],
add(indexof(outputs('Get_email_template_row_by_ID')?['body/subjectsafehtml'],'">'),2),
indexof(outputs('Get_email_template_row_by_ID')?['body/subjectsafehtml'],'</div>')
)

The description uses replace() to swap the variables in the template (#FULLNAME# etc) with values from the contact record:

The example replace() is here. Often these get very long with a lot of nested replace(0 statements, as it is easiest to use one standard expression everywhere in the Flow, rather than tailor to variables. All this is doing is taking the body from the template, and replacing any instance of #VARIABLE# with a value from the contact record.

replace(
    replace(
    outputs('Get_Email_Template_row_by_ID')?['body/safehtml'],
    '#FULLNAME#',outputs('Get_the_contact_row_by_ID')?['body/fullname']),
    '#ADDRESS#',outputs('Get_the_contact_row_by_ID')?['body/address1_composite']
)

Finally, deal with attachments and send that email.

Add a DataVerse create new record action, use the emailDescription and emailSubject variables in the ‘Add new row to Email Messages’ along with the activity parties variable (as in that other post recall?).

Then check if any attachments are returned:

length(outputs(‘Get_Email_Template_row_by_ID’)?[‘body/template_activity_mime_attachments’]) is greater than 0.

If so, parse the JSON returned:

the JSON schema:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "@@odata.type": {
                "type": "string"
            },
            "@@odata.id": {
                "type": "string"
            },
            "@@odata.etag": {
                "type": "string"
            },
            "@@odata.editLink": {
                "type": "string"
            },
            "filename": {
                "type": "string"
            },
            "mimetype": {
                "type": "string"
            },
            "body": {
                "type": "string"
            },
            "_attachmentid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": {
                "type": "string"
            },
            "_attachmentid_value@Microsoft.Dynamics.CRM.lookuplogicalname": {
                "type": "string"
            },
            "_attachmentid_value@odata.type": {
                "type": "string"
            },
            "_attachmentid_value": {
                "type": "string"
            },
            "activitymimeattachmentid@odata.type": {
                "type": "string"
            },
            "activitymimeattachmentid": {
                "type": "string"
            },
            "attachmentid@odata.associationLink": {
                "type": "string"
            },
            "attachmentid@odata.navigationLink": {
                "type": "string"
            }
        },
        "required": [
            "@@odata.type",
            "@@odata.id",
            "@@odata.etag",
            "@@odata.editLink",
            "filename",
            "mimetype",
            "body",
            "_attachmentid_value@Microsoft.Dynamics.CRM.associatednavigationproperty",
            "_attachmentid_value@Microsoft.Dynamics.CRM.lookuplogicalname",
            "_attachmentid_value@odata.type",
            "_attachmentid_value",
            "activitymimeattachmentid@odata.type",
            "activitymimeattachmentid",
            "attachmentid@odata.associationLink",
            "attachmentid@odata.navigationLink"
        ]
    }
}

and then add the attachment row and relate to the email:

where the Item(Email Messages) is:

emails(outputs(‘Add_a_new_row_to_Email_Messages’)?[‘body/activityid’])

and the body, filename and Mime Type are :

items(‘Apply_to_each_attachment_returned’)[‘body’]

items(‘Apply_to_each_attachment_returned’)[‘filename’]

items(‘Apply_to_each_attachment_returned’)[‘mimetype’]

The Flow is available on my github page.