Create an Effective Bill Reminder Template for Teams

Bill reminder template for teams

last week Tony one of our members asked if it was possible to have a spreadsheet automatically email a reminder containing a list of tasks a few days before an event for example here I've got my task list with the due date and the date we want to send out the reminder by the way my dates are formatted day month year now we can see that I need Excel to send me one email containing my two tasks and another email to Joe with her one task for this I'm going to look at how we can use power automate to program this with absolutely zero coding required all you need is a Microsoft 365 account and your Excel file saved either on one drive for business or SharePoint online let's take a look before we switch to power automate it's important to point out that your data must be saved in an Excel table you can do that via the insert Tab and then table or use the keyboard shortcut contrl t now my data is already in a table and if we look at the table design tab you can see the table name is tasks and we'll be referencing that in our automation at the top we can see the file is saved on one drive you can use one drive for business or SharePoint online for this but not one drive personal now if you're familiar with power automate then you may have used the addin available for Excel you can access the addin by the get addins icon and then search for it in the store and it used to be called flow they haven't updated it so in the addins it's still called Microsoft flow for Excel I've already got it installed and we can see it on my data tab over here on the right hand side I won't be using power automate here because the new flows that are available are limited to the instant Cloud flow and that requires you to click a button to trigger the flow whereas I want to create a flow that's triggered based on the reminder date in my table without me having to even open the file and that means I need to use power automate online now you can navigate to power automate from the app launcher in the top left when you're logged into Microsoft apps online if you don't see it in the list here you can click on all apps and then navigate to it I'm going to open it in a new tab now I'm creating a new flow so I'm going to click on Create and I want a scheduled Cloud flow and that's going to allow me to choose when and how often it runs I'm going to call it email task reminders I'm going to start it today and it can run at 10:00 a.m. every and let's choose day so we click create so I've created my first step in my flow I can click on it here to edit it and we can make changes obviously I don't need to edit it it's doing what I want so I'm ready to add my next step here I want to get a table from Excel and I want to list the rows present in a table here it is in the list the location is one drive for business and you can see my SharePoint sites are also available because I've selected one drive business the document library is one drive if you select SharePoint then you'll have different options for the document library then I simply need to navigate to the file so it's in a folder called blog posts and then automate and there's the file once you've selected your file you can then select the table remember my file has a table called tasks obviously if you have more tables in your file then they're all going to be listed here I just have the one now under Advanced options I can set some filters I only want to get the rows in the table that contain today's date so here I'm going to enter in a filter and I'm filtering based on the reminder date it's called reminder date with no spaces and then EQ which is the abbreviation for equals and I need to open the single quote and then for the date I can add an expression that automatically returns today's date so I want expression and then in the formula field I'm going to enter my formula the first function I need here is format the date and time and then I need to convert my time from UTC and what time am I converting well the UTC now time this is the same as the noun function in Excel now I need to convert it from UTC to my local time zone I'm in Australia on the eastern coast so my time zone is called e. Australia Standard Time and notice it's in single quotes close parentheses on my convert function and then I need to tell it the format and that's going to be y y y mm and then day close single quote close parentheses and that's my formula so we're formatting the date that we're converting from the UTC now time into my Australia Standard time and we're formatting it in the format year month day click okay now I need to close the single quote so I've got an open single quote then my expression and then close single quote and then down here I need to set the date time format to is 8601 now obviously not all of you are going to be on Australia's eastern coast so in the video description I've included a link where you'll find the various time zone names so that's the step to get the data from my table let's add a new step to get a distinct list or unique list of emails from that filtered list so that each person only gets one email we do this by adding a select step just type select into the search that filters the actions down here and it's this one here in the from I'm going to get the values from the table so I can just add Dynamic content and click on that and in the map we're going to switch to text mode and in here all I want to do is grab the list of emails so select is just extracting the emails from my table let's add a new Step cuz next we need to find a distinct list and we can do that with compos so select compos from the list and here the input is an expression we can use the Union function to generate a distinct list and here I want to select Dynamic content and we're going to reference the output from the select step which is our list of emails and then reference It Again by referencing itself it generates a distinct list click okay and that's how composed step done next we want to add a control to extract the data for the email from each row of the Excel table so we want a control and we want the apply to each action this is going to Loop through the rows in the Excel table so the source here is our output from the composed step remember that's the array of distinct emails and then I want to add an action to filter the values from the table so in here I want to filter array and there it is there the filter array operation and my array to filter is going to be the values from the table where the email is equal to the current item now the current item is the outputs up here and we can see that that's the outputs from the composed step and the composed step is the distinct list of emails so we want to filter the array where the email is equal to the distinct list of emails and then we're ready to create the HTML table that's going to be inserted into our email so in here we want to filter for HTML and create HTML table the HTML table is going to get the body of the data in the filter array so basically the data from the Excel file now I can let power automate insert all the columns from the original table or if I only want some columns I can specify them in the advanced columns and then instead of automatic we choose custom and in here I type in the column headers and then the values so I only want two columns the due date and the task name you can't have any spaces in the headers so that's why due date is one word now in the value I need to enter an expression remember my dates are date serial numbers in Exel so I need to tell power automate how to format them so we're going to format date time and we're going to get the item from the due date column and we want to format that mmm day and then year and click okay the next column I want is just the task name so again we need an expression this time it doesn't need any formatting cuz it's already text so we just want the item and we want the item from the task column and lastly I want to generate the email so I'll add an action I'm going to use Office 365 Outlook and in here I want send an email V2 for the email addresses I can use Dynamic content which is the current item remember the current item is the list of distinct emails so let's scroll down and choose current item then in the subject again I can use Dynamic content here but I don't need it in this case I just want the subject to say tasks due the body is the HTML table we created here so we want the output of the HTML table I can add some text before it for example I might want to say please find a list of your upcoming tasks I can use the formatting tools up here to format this font and because the output is an HTML table if I'm familiar with HTML I can use the code View and add in some formatting to nicely format that table I'm going to leave it as is cuz it's fairly basic anyway now in the advanced options you can choose who the sender is and CC or BCC others you can also add attachments set sensitivity reply to and importance let's set the importance to high because these are du tasks we want people to pay attention to them and that's it all I need to do here is save it and before we test it out let's recap what we've done first we set up the trigger for the flow and we set it to run every day then we got the data and filtered it for today's date then we got a list of all the emails in the file so that we could create a distinct list to ensure that people only got one email and in the apply to each step we filter the data created a HTML table which we inserted into the email so let's test the flow we're going to manually test it run the flow it's done let me go check my email and there's the email that I received you can see I've got two tasks book venue and book entertainment and the due date is March the 3D if you want to to inspect any of these steps and see what the output is you can do that here so for example if we look at the select step you can see it selects the data in each row of the table so that's the first row second row third row and down the bottom we have the emails that it's extracted and then in the compos step you can see it's reduced that to just two emails so that we have a distinct list I hope you found this useful and you're excited to give power automat try go ahead and grab the step-by-step written instructions from the link here and if you like this video please give it the Thumbs Up And subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for [Music] watching