Sending a Confirmation Email with Power Automate

Previously, we built a time-based calendar and added mechanics to capture appointment data. We now need to extend that to send a confirmation email.

Time to show the wife some Power Automate stuff, which will include a nice cheat code for generating a top-notch email body!

Table of Contents

The requirement

Whenever an appointment is added to our Dataverse table, an automated email needs to be sent to the client. The email has the following requirements:

> Detail all appointment related information, so date, time, cost, treatments etc.
> Include a link to a health consultation form for the client to complete.
> Professionally designed email body, which is fully responsive to the users device.
> Attach a PDF with directions to our house, where the salon (aka glorified shed in the garden) is location.

Nothing too bad then. We’ll also have the sender of the email be a different address, depending on what Power Platform environment we’re calling the flow from. Sounds like an Environment Variable to me.

The email senders

If you go back to my first blog of this series, you’ll see mention of us originally planning to use Microsoft Bookings. That idea got thrown in the sea when they discontinued the iOS mobile app.

At the time, we created x3 Bookings instances. These effectively create a Shared Calendar in Microsoft 365 for each instance, and therefore each one has its own email address. We might not be using Bookings, but we can repurpose these emails!

Useless Bookings fact – in the web browser you can open an instance of Bookings, select Integrations and you’ll find the SMTP address for it:

I say useless, because in Power Automate terms Bookings only has 3 triggers (all of which are still in Preview) and 1 action. If you want to do any meaningful integrations, you need to use the Exchange/Outlook actions or build a custom connector and talk to the Bookings API.

We are working in a solution – a best practice in itself. Therefore we’ll add an environment variable to the solution called ‘Email Sent From’. This will have its Current Value set to the relevant email address as per above.

If you’re unfamiliar with creating environment variables, I’ve given some insight here. We are using a Text type and building in DEV to begin with. This is the way.

Create a flow

Again, I’ll be doing this from within the solution we have. So within that, click New, then Automation, then Cloud Flow, then Automated:

If you’re following along and not so fussed about solutions for now, head on over to make.powerautomate.com, click Create then Automated cloud flow:

Whichever way you create, the experience hereafter is the same.

In case you were unaware, we’re selecting ‘Automated cloud flow’ as that’s the correct type to handle our requirement. A scheduled flow runs at a specific date/time we choose, so that won’t work here. An instant flow is something we have to manually trigger when we need to, that could be an option but it’s very manual and knowing the wife, she’d probably forget to do it.

Automated cloud flows will run automatically, whenever something occurs. Our ‘something’ is when a new appointment is created.

In the resulting popup, I’ll name the flow Confirmation email. For the trigger, I’ll want the When a row is added, modified or deleted option that relates to Dataverse. If you’re working with other data storage products, there are similar triggers for those:

Click on Create and the Flow will load up, ready to add more actions.

Configure the trigger

We only want the flow to trigger when a new entry has been added to our Scheduled Appointments table. The first two parts of the trigger take care of themselves. For the scope, this is my personal tenant so ‘User’ is fine. If you’re using this trigger in the workplace or for a client development, you may need to select a different option. This is well documented by Microsoft, so worth adding this to your favourites if you need to.

Do some Date stuff

Our email body is going to need the appointment date in a couple of different formats. This will become clear why later in the article. Yes, I could do these directly as an expression where needed, personally I like to compartmentalize.

Click on New step, then search for and select Scope:

From within the Scope block, click on Add an action, then search for and select Compose:

I’m going to rename the Compose action so it’s easy to find later, when we need to reference it as dynamic content. You can rename any trigger or action in Power Automate by clicking on the ellipsis to the right, then selecting Rename:

We’re renaming this Compose action to Get year of appointment. We then need to write an expression to get the Year from the date of the appointment that’s been submited. Click in the Input area of the Compose action, then click on the Add an expression option:

To begin, we need the formatDateTime function:

With our cursor between the two brackets, we then need to click Dynamic values so we can select our appDate field. This field stores the appointment date and is a Date/Time field type.

Selecting the appDate field this way auto-populated the text between the brackets:

Finally, add a comma to the end and then the desired format. ‘yyyy‘ is what we need for the Year, then click Save:

If done correctly, the entry in the Input box will resolve itself and look like this:

I’m adding two more Compose actions, with two other formatDateTime functions. The first puts the date in the friendly short format for the UK (ie, 20/07/2023) and another for long format (ie, Thursday 20th July 2023):

Do some Directions stuff

We have a PDF file saved in OneDrive for Business with instructions of how to find the salon. The PDF will need to be attached to the email we’re going to send, but to do that we need to retrieve the file and its content. This works in an almost identical way if files are stored in other popular file locations.

Inside a new Scope action, add a New step, then find the Get file metadata action for OneDrive for Business:

Use the folder icon to navigate and select the file in question. This doesn’t need to be dynamic in our case, it’s always going to be the same file:

Underneath that, add an action and find the Get file content action for OneDrive for Business:

For the unique identifier of the file, use the Dynamic content and select Id from the previous action:

That completes our scope for this part:

Send an email

The wife wants a nice professional email body, so going to need something custom-built in HTML. Not one of my strengths it has to be said. Google to the rescue.

There are many sites that can provide the service we need, without any costs associated; use a drag & drop design interface to build an email body, steal the code, whack it into Power Automate, happy days. We opted for BEE.

We started with spinning up a new project on their site, then filtering the available templates by our industry of choice:

This seems to fit the bill – confirmation email. That’ll do for a starting point:

A bit of customising with the wife’s brand colour (light green), logo and placeholder text, we have something like below. The toggle in the top left of the designer helps us see what it’ll look like on desktop vs mobile:

Once happy, we’re able to export the full HTML:

Standard email config

Back in our Power Automate flow, click New step and add the Send an email (V2) action:

To begin with, some easy config. For the To box, we want the email address for the client who’s just booked an appointment. We access this with Dynamic content. Email address is mandatory for our data source so should never be blank.

For From (Send as), we want the Email Sent From environment variable that stores our relevant Microsoft Bookings instance. This will make the sender appear from Bloom Beauty when in Production. The Reply To address will be set to the primary email address for her business, and we’ll set Importance to High:

Attach PDF file

For the two boxes relating to attachments, we need the file name and content.

Our Attachments Name can be populated from the Get file metadata for Directions PDF action:

The Attachments Content can be populated from the Get file content for Directions PDF action:

Add and configure HTML body

Our HTML built using BEE can now be added too. Start by clicking on the Code View option for the Body field of the Send an email action:

Delete any code that’s already there, then paste in the code copied from the HTML designer. For us, this is a fair bit of code. We’ll save the flow at this point just to be safe.

The slightly tricky part is now finding all the hardcoded parts in the template, that need replacing with dynamic content. Short term pain, long term gain.

First updates are replacing the client name, using the output of the Get friendly long date compose action for the date & the appStartText value from the Dataverse trigger:

Next, the table of appointment details. Day needs the output of the Get friendly short date compose action, whilst Time, Duration, Service(s) and Cost need their respective dynamic content from the Dataverse trigger:

Finally, need to replace the hardcoded year to take the output of the Get year of appointment compose action:

Caveat

Something that escaped my brain until the day after building this – any images added to our template on BEE are also hosted by them too. You can see this in the underlying HTML we copied:
Ideally then, we want to reference images WE are hosting, not someone else. I already knew of the Base64 method, but what I didn’t know is that this doesn’t work for those using Gmail. Paul Murana’s video explains that very well, along with an alternative that works for all email clients. Paul’s blog & YouTube channel is full of excellent Power Automate tips & guides, highly recommended.

Testing

Let’s add an appointment, fingers crossed we should get an email. Here’s the test appointment entry:

Email output for mobile:

Email output for desktop:

I love how quickly this took for us to put together and have a quality output from!

Bonus tip

I’ve long been in the practice of adding any expressions as a note to the action block. It makes them easy for myself and others to see at a glance what’s happening. Add a note can also be found in the ellipsis menu for any trigger or action:

Here’s an example of that in action, which incidentally is also the completed Scope for doing Date conversions:

Maybe something to consider the next time you’re building a Power Automate flow that contains expressions.

Next time out, we’ll be building another Flow to take responses from a Microsoft Form and add them to a database.