Categories: Microsoft Bookings
*** NOTE: ALL INFORMATION IS ACCURATE AT DATE OF PUBLISHING ***

There are many ways you can manage your appointments within Microsoft Bookings. You can use the mobile app, use Microsoft Teams, or simply log in to Microsoft Bookings directly. How about getting an email each morning with a list of all your bookings? It’s not possible from Bookings itself, but with a bit of investigating in to how the appointments are created, and then using Power Automate, we can get a nice email sent out on a daily basis.

First, if we look at a booking that has been made and open it in Outlook, we can take a look at what is captured. The subject of the appointment is the name of the service that was selected. The staff member assigned to the booking is also added using a category with their full name. Any of the standard fields used on the booking form will be displayed under a Customer info section. In this example, we will focus on the standard fields and sending those along with the type of booking, start and end time, and the consultant or staff members assigned.

Another thing we need to be aware of, when time off is booked on the calendar, this will also be added to the booking calendar. The appointment will have the Show as option set to Away.

This is flagged using ‘oof’ to show Out of Office. When we send the daily email, we can use this information to exclude those appointments from showing in the list of bookings.

OK. Now that we know a bit more about the Bookings that get made and how they are constructed, let’s move on to Power Automate. First, we will set a Schedule for the trigger and set it to run every day.

Next, we will use the Get events (currently this is V4) action from the Microsoft 365 Outlook connector. By default it will link the user that is logged in and try and show any calendars owned by that person. However, we need to get to the Bookings calendar. In order to do this, have your administrator go to the Microsoft 365 admin center, then find the calendar in the list of active users. Simply add a password to the user and save it. That’s it! Now from the Get events (V4) action step, click on the ellipsis (three dots) and then click on Add new connection from the bottom. When the login screen pops up, add the email address for the bookings calendar, then the password that was added to the user record.

Now you should see the Calendar that is used for the Bookings calendar in the list for Calendar id. Select it (there should only be one, called Calendar). We want to make sure we only get the bookings, so we are going to add a filter query to exclude anything that has showAs set to oof. We also only want bookings that have a start time for today. To achieve this, I’ve used the start value being greater than or equal to the start of the day at midnight, and being less than or equal to the end of the day at 23:59. We can also order, or sort the events that are returned, and will use the start/dateTime to do so.

showAs ne 'oof' and start/dateTime ge '@{formatDateTime(utcNow(),'yyyy-MM-dd')}T00:00:00.0000000' and start/dateTime le '@{formatDateTime(utcNow(),'yyyy-MM-dd')}T23:59:00.0000000'

Now we need to add 5 steps, all of them the initialize variable action. The first 4 will have the type of string, and the last one will be an array. Don’t add anything into the value field. We will be populating the values later on in the flow. The string variables are for the Consultant Name, Customer Name, Customer Email and Customer Phone. The array variable will be used so that we can append to the variable and add each event with all of the information about the booking.

We now need to extract the customer information from each event. Adding in a new action we can take the Html of the event and change it to text. Adding in the Body from the Get events step above, this will create the Apply to each that you see below. This means that the steps that follow will all be applied to each event found based on our filtering criteria.

Going back to the booking, we can see that the Name, Email and Phone Number are added to the body of the event. We need to get those out so that we can use them in the list of bookings to be emailed daily.

Here is where we use the variables we initialised earlier. Search for the Set variable action. We need to add three of these actions, and we will use these for the Customer Name, Customer Email and Customer Phone. So select them from the drop down. You can see the expressions used in the value for each below. We are basically splitting up the output from the Html to text step above, and getting whatever value we need. For example, for the Customer Name, the name starts after the word ‘Name:’ and then a space. Everything that comes next is the name, up until we get to the word Email. So we can split that part out and extract just the name. For the Email we get everything between ‘Email: ‘ and the word Phone on the next line. For the Phone Number, we get everything between ‘Phone Number: ‘ and then the word Booking on the next line.

@{last(split(first(split(outputs('Html_to_text')?['body'],'Email')),'Name: '))}
@{last(split(first(split(outputs('Html_to_text')?['body'], 'Phone')), 'Email: '))}
@{last(split(first(split(outputs('Html_to_text')?['body'], 'Booking Info')), 'Phone Number: '))}

Make sure you check to see what information is coming through on your appointment entries on the booking calendar. If you have this setting on the Booking page deselected, you will have extra information coming through into the Customer Info section on the bookings.

You can see below that after the phone number you have the Time Zone, so if you use the expression above to get the Phone Number, the Time Zone would also be included because that comes before the Booking Info. Just something to keep in mind to make sure you adjust these steps to work for your own scenarios and requirements.

Next we are going to get the Consultant or Staff member assigned to the booking. The easiest thing to use is the Category that has been assigned. We can see in the calendar that Tom Simpson has been assigned as a category to indicate that the person Tom Simpson is going to work on this booking.

Add another Set variable action. This time pick the Consultant Name variable, and add Categories Item as dynamic content from the Get events step. This will then create an Apply to each. There should only be one Category though. Close the Apply to each once you have added it so you don’t mistakenly add more steps to it.

The last step in the first Apply to each is to use an Append to array variable action. Select the HTML Table variable, and then add in the values you want in the array. For the service booked, I’ve just used the Subject of the event which is nice and easy! For the Start Time and End Time I am just formatting those two values to just show the day, month and time using an expression (see the expressions below). We are taking each event found, and all of the formatted extracted values, and adding them to the array, which is essentially creating a list of all the events.

@{formatDateTime(items('Apply_to_each_2')?['start'],'dd/MM hh:mm tt')}
@{formatDateTime(items('Apply_to_each_2')?['end'],'dd/MM hh:mm tt')}

Now minimise the Apply to each. Everything else that we add sits outside in the main flow area.

Add an action using Create HTML table. Use the output from the Array variable of HTML Table. You can leave the columns to be created using the Automatic option.

One thing I always like to do when using an HTML table step is to make it look a bit prettier than how it comes out by default. Check out this post from my friend Ryan Maclean for steps and tips on styling the table. All we are doing here is adding a Compose step, adding some CSS at the top, then using the Output from the step above.

The last step, sending the email. Add in the To person or email, then the subject, then whatever you want in the body. Then add the Output from the step above.

Here it is! The finished email, complete with a formatted table, and a list of all of the bookings for a specific day.

Hopefully you can see there are so many different variances of this you could use. You might want to have a step to check how many events are found, and if there are none, don’t send the email, or send it but indicate that no bookings have been found. So tons of options! We will cover more in future posts that delve further in to using Power Automate alongside Microsoft Bookings. You can watch a video on this here.


Check out the latest post:
Applying Filters To Data In Microsoft Clarity


DISCLOSURE: THIS POST MAY CONTAIN AFFILIATE LINKS. FULL DETAILS HERE
This is just 1 of 270 articles. You can browse through all of them by going to the main blog page, or navigate through different categories to find more content you are interested in. You can also subscribe and get new blog posts emailed to you directly.


10 thoughts on “Email Daily Summary Of Bookings Using Power Automate

  1. Hi very interesting article about how using Flow and Bookings and very usefull for a manager or individually – thanks a lot !!!!

  2. Hi Megan,
    Great example of getting the most of of Bookings.

    My question is where did you get the output info where you find the “showAs” field with the value of “oof”?

    I’m trying to use this example you have (with some modifications) but can’t find that anywhere.

    Thanks,
    Steve

    1. Hi Steve. I got the output from the second step for Get events (V4) action. If you did that and then looked at a successful flow run, then you would see all of the events in the output. I ran that first, and noticed the showAs field and realised I needed to omit the ones that showed as oof.

  3. Hi Megan, thanks for your great post. I am trying to implement it and it worked perfectly.

    I just cant remove the \r\n in the customer information from the generated eMails. Do you have any ideas how to get rid of the \r\n in each field? That would make my eMails so much prettier 🙂
    Roger

    1. Hi Roger, apologies for the delay, I’ve been on holiday/vacation all week. I’ve looked back through my post, and I can’t see which step you are referring to as to why you get \r\n. Can you provide more detail? Or send me a message with more detail via my Contact page, then I can follow back up.

    2. Hi Megan, thanks for your reply. I have the \r\n in the eMail see: https://photos.app.goo.gl/FstscVZSztMQ6ud38

      It looks like the flow includes the line breaks from the calendar event during the parsing and generation of the email text.

      Maybe this is something that happens in the German version of Bookings.

  4. Hi Megan,
    Thanks for this outstanding “How to”! This is my first go at Power Automate and the way you have written this up made it really simple to follow.
    Whilst I can get the whole thing working I have encountered a snag which may just be a limitation of how Bookings interacts with it’s Calendar. In my case I have just one Service which is using the “maximum” attendees feature, currently set at 4 (ie >1).

    When the power automate fetches each booking from the calendar, the body returned and converted to text looks like this:

    \r\n******************************************************** \r\n NOTE: This is a read-only view of the booking. \r\n Please use Microsoft Bookings for web, iOS or Android to edit this booking. \r\n Any changes made here will be lost. \r\n******************************************************** \r\n \r\nNOTE: This is a Multi customer booking. Log into Bookings to see customer information and notes for this event. \r\n \r\nBooking Info \r\n——————– \r\nService name: Visit Us! \r\nLocation: Level 7, 15 William Street Melbourne \r\n\r\n

    In other words, I think it only returns the Booking Info section, but not the Customer Info section. BTW, my attendees are always employees (all having O365), I’m not sure if that’s contributes to the problem.

    I suspect that this may because Bookings is storing the multiple “Customers” as some type of sub record rather than just in the HTML body of the Calendar entry. I was wondering if you had any thought on how I could try digging deeper into the Calendar event to find the missing info?

Leave a Reply

Your email address will not be published. Required fields are marked *