[ad_1]
If you want to schedule emails, Microsoft Outlook has limits. You can delay a message, but Outlook must be open for it to work. There’s also no way to create a conditional scheduled date. For instance, you might want to send an email to employees when they have comp time available. You could create an Outlook template and send it when appropriate, but wouldn’t it be nice if you didn’t have to do anything at all?
SEE: Google Workspace vs. Microsoft 365: A side-by-side analysis w/checklist (TechRepublic Premium)
In this tutorial, we’ll create a Power Automate flow to send reminder emails to employees regarding comp time. You’ll learn how to enter a simple expression to create an internal query that returns only employees with comp time. Then, the flow will generate an email for each of these employees and send it. The only thing you’ll have to do is maintain the source data.
I’m using Microsoft 365, Power Automate and OneDrive. You can also use SharePoint.
How to set up the source data in Excel
For this demonstration we need two things: A data source and a Power Automate flow. In the real world, a list of employees with comp time might come from a payroll app or a human resources database. We’ll use Microsoft Excel because it’s universal. You can probably schedule an update to the source data using another flow.
For now, we’ll use the simple sheet shown in Figure A. Three employees have comp time, and one doesn’t. When an employee has eight or more hours of comp time, a flow will send those employees an email reminding them that the time is available.
Figure A
Eight hours is the condition that will determine who gets an email. Column F contains a simple IF() statement that returns Y or N:
=IF([@Hours]>=C1, "Y", "N")
If the number of accrued hours in the Hours column is greater than or equal to the benchmark value of 8 in C1, the function returns Y. Otherwise, the function returns N. You could enter 8 in the IF() function but using an input value is more flexible. You can update it later by changing only that value instead of updating all the functions.
Admittedly, in this situation, that’s not a big deal, but when working with a busy sheet, input values are helpful. In our case, the flow needs a way to identify which employees get an email.
You’ll notice the email address is mine. For demonstration purposes, enter your email so you can test the example later. When applying this to your own work, you will want to use the employees’ email addresses.
There are a few things you should know about flows before we continue:
- You can’t send formatting to a flow. If your data requires formatting, create a second column using the TEXT() function to display formatting and send that data, not the original data.
- Power Automate only works with Excel Table objects. The name of the demonstration Table is TableSchedule.
- You must save the Excel file to SharePoint or OneDrive. I’m working with OneDrive, but instructions won’t differ much if you use SharePoint.
Once the Excel file is ready to go, you can start creating the flow in Power Automate.
How to create a scheduling flow in Power Automate
To start a scheduling flow, sign into your Microsoft account and launch Power Automate as you normally would. In the left pane, click Create and then choose Scheduled Cloud Flow. In the resulting dialog, create the schedule you see in Figure B and then click Create. Power Automate will run this flow every Thursday at 10:00AM.
Figure B
Figure C
Figure C shows the Recurrence trigger. To continue, click New Step. We want to connect to an Excel file, so click Excel Online (Business). If you’re using a personal account, click Excel Online (OneDrive). The resulting list of actions narrows down to only Excel actions. In this case, click List Rows Present In a Table (Figure D).
Figure D
Use the dropdowns to specify the Location and Document Library. To identify the Excel file, use the Browse tool as you normally would when trying to open a file. From the Table dropdown, choose the Table that contains your comp time schedule. The demo Table is TableSchedule, as shown in Figure E.
Figure E
Click the Show Advanced Options link to identify the employees who will receive an email. Click inside the Filter Query control and enter the expression
Reminder eq 'Y'
as shown in Figure F.
Figure F
This expression creates an internal query that returns only the records where the value in the Reminder column is Y. You must use single quotes instead of double quotes to enclose the Y. The rest of the advanced options are irrelevant so continue by clicking New Step.
Next, specify the operation, which in this case is to send an email. Enter email in the Search control to narrow down the options. Click Office 365 Outlook, as shown in Figure G, or the email server you’re using. From the resulting list shown in Figure H, choose Send An Email (V2).
Figure G
Figure H
In this next step, we create the actual email message. The contacts will change each week, so we need to specify dynamic content by clicking Add Dynamic Content. If you don’t see that link, click the double arrows at the top-right of the To control. Doing so will switch you to advanced mode.
Under the Search control, Power Automate lists the column in the Excel file that contains the email address. If you want to check, click See More to the right. As you can see in Figure I, you have access to all the columns, but Email is the correct one, so click it.
As you can see in Figure J, Power Automate knows to send a separate email to each email address in the Email column.
Figure I
Figure J
Next, click Send an email (V2) under the output control that displays value x. Power Automate populates the To control, so let’s finish the email. Enter Comp Time Reminder for as the Subject text. You can add the recipient’s name to the subject by clicking Add Dynamic Content and then clicking Employee. Doing so will add that column to the subject, as shown in Figure K. Be sure to add a space after for before adding the Employee column.
Figure K
Figure L
Next, click inside the Body and enter the text for the email, as shown in Figure L. Notice that I added dynamic content to fill in personal information where appropriate.
Click the Show Advanced Options link and enter any attention information that applies to your situation. As you can see, I added my organization address in the From control, as shown in Figure M. If you’re doing this for someone else, enter their address instead. You’re ready to save the flow so click Save at the bottom of the screen.
Figure M
Now it’s time to test the flow.
How to test the scheduled flow in Power Automate
Power Automate can test the flow for you now. Click Test in the top-right corner. In the resulting Test Flow pane, click Manually, as shown in Figure N, click Test and then click Run Flow. Click Done if the flow ran successfully. If not, you might need to do some troubleshooting and test again.
Figure N
How can you make sure the test really was successful? In this case, the flow sent two emails to you — remember, for demonstration purposes, you used your email organizational address, so open your email client and look for those emails. Figure O shows mine.
Figure O
Creating a Power Automate flow is easy considering what you gain in return — freedom from repetitive tasks! In this demonstration, your only task is updating the source data in Microsoft Excel once a week. It’s likely, however, you could download updated data using another flow and bypass Excel altogether.
[ad_2]
Source link
