Automating Sick Day Notifications (O365)

I love automating - recurring tasks should be automated, that’s one of my mottos. IFTTT, Zapier, n8n, or Power Automate (formerly Microsoft Flow) can significantly make our lives easier when it comes to automation. These event-driven automation tools all have their pros and cons, I won’t go into those here.

I recently had a case that I was able to automate relatively easily. The case itself isn’t relevant to the IT community, so this post targets less experienced users (parents).

The Case - UseCase

My eldest started school in September (2020) as a first grader. Anyone who experienced 2020 knows it was a difficult and extraordinary year. 2020 was under the shadow of Covid-19, including schools.

School is an institution, for me always associated with a building, but such a school encompasses more than just the bricks it’s made of. Caretakers, administration, teachers, students, and of course parents. We now count among the latter.

The school naturally wants to be informed about sick days. One of the first documents shared by the parent council contained this information. The usual channels of phone, fax, and email are offered. With the latter - email - “preferred as additional info/request” was noted.

We had a case of illness, so Mom and Dad had to report it. In this case, I opened my mail app on my smartphone, wanted to enter the recipient… that’s where the problems started. Below I describe the steps I needed to send this seemingly simple email:

1. Open email app
2. Enter recipient (this information was missing)
3. Search for recipient —> not saved
4. Search for the document shared by the parent council
5. Found in WhatsApp, open in PDF viewer
6. Copy out the email address
7. Paste the email address
8. Paste subject: Sick note '[First name Last name]' Class '1x'
9. Paste body/text
10. Send

Way too much time lost - I knew it could be easier. At my employer, sick day notifications work similarly - I have to email a distribution list. I’ve had a Flow for that for a long time, which with one click in the app does exactly that. Now I wanted the same for our son’s sick day notifications.

What’s Needed

Usually all the automation tools mentioned above and others can be used for this requirement. In this case, I’ll use Power Automate (Flow). Students at the school in question have an M365 Education subscription, so Flow is already available in the standard version.

What’s needed:

• M365 account (can also be from an M365 Single/Family subscription)
• Recipient email
• Email from the parents

Opening Power Automate

How do you get to the Flows? That’s quite simple: after logging into M365, you arrive at a page showing all available M365 apps.

After clicking on Power Automate, you arrive at the administration interface. The current Flows are accessible by selecting “My Flows” in the left navigation.

The Flow

At this point, you have the option to build the described Flow step by step as shown in this post, or to import an existing version and just adjust the needed information. For the latter case, I’ve prepared a package for a certain circle (parents from my son’s class), which can be downloaded and imported via the Import function. For importing, you can continue with the Special Flow section.

When we click “New” at the top, a selection of possible triggers and predefined templates for the new Flow appears. We choose “Instant” here.

The Flow Trigger “Instant”

As long as our children haven’t had a bio-health-tracker implanted, it will be a manually triggered event. For this, Flow provides a trigger with buttons that can be triggered directly with one click from the Flow app, for example. The app is available in both the Play Store and the App Store.

This is the so-called Instant trigger. If you create a Flow with this trigger, a button with the Flow title will appear in the mobile app, for example.

After selecting the trigger, another input form appears. Here we assign the Flow name and select “Manual trigger flow” and click “Create” at the bottom.

The Actions

After our trigger is configured, we can proceed with the actions to be executed. First, we define our variables - we use the Variable action for this. To add a new step in the processing, we click ”+ New step” and search for “Variable” in the search box. In the selection shown below, we choose the “Initialize Variable” action. Additionally, we’ll use the “Send an email (V2)” action from the Microsoft Outlook integration.

The Variables

We’ll use variables for our Flow. Creating the following variables is always the same, only the variable names and values differ. Therefore, I’ll describe the way to add a variable only once.

Variables:

Today’s Date:

Adding this variable is analogous to the others, the only difference being that we don’t enter a static string as the value but a formula as an Expression. After selecting the action, the name can be entered and the type selected.

When clicking in the “Value” column, a new window usually pops up.

Here you must click “Expression” and enter the formula below:

formatDateTime(triggerOutputs()['headers']['x-ms-user-timestamp'], 'dd.MM.yyyy')

After confirming with “OK”, the formula is set as the value.

What’s happening here? That’s quite simply explained. We already receive predefined values via environment variables - one of which is the timestamp created when the Flow is triggered, but it’s in a poorly readable format. With the formula listed, we create a more readable format (Day.Month.Year, e.g. 05.12.2023) for human eyes.

We Send an Email

Now we come to our final action: sending the email. We’ll use the “Send an Email (V2)” action.

To be able to set all required values, we need to show all options.

The following content must be entered into the fields of the action. Subject and Body will be handled separately, as inline expressions are used there. For all other fields, the already defined variables can be used.

Variables in Text

If you click in either the Subject or Body fields, static text can now be mixed with variables (dynamic content). Below are example texts for the subject and content of the email. This can be copied and pasted into the respective fields. The places with variables (e.g. @{variables('Child: Name')}) now need to be selected with the mouse and then the variable selected from the dynamic content popup.

Subject:

Sick note @{variables('Child: Name')} Class @{variables('Child: Class')}

Content:

Hello Dear Secretariat,

unfortunately we have to report that '@{variables('Child: Name')}' - Class '@{variables('Child: Class')}' - is sick for today (@{variables('Date: Today')}).

Best regards,
------------------------------------------------
@{variables('Parents: Info')}

That completes the general part. The Flow template for this case can be downloaded here. After import, the variables - except “Date: Today” - must be adjusted.

Special Flow

This section is for a special audience. For the parents of my son’s classmates, I’ve created a different package. Basically, parents could follow the approach described above and implement everything themselves or use the general package.

The advantage of the separate package is that parents only need to adjust the contactInfo and mailOfParents variables after import. The rest is retrieved via the User Profile (child’s name and class). Therefore, before importing, the “Office 365 User” connection must be set up.

To do this, select “Connections” under “Data” in the left navigation. All already existing connections are displayed here.

By selecting “new Connection”, you arrive at a new page with a list of available services. We can now filter this list using the search in the top right.

After clicking the Add button (the plus sign ”+”), another mask opens, here it’s sufficient to click “Create”. In the background, the connection is created. We need this connection to get information like class, name, etc.

After that, the package can now be imported via the Import menu under “My Flows”.