Send Emails Based on a Date Column in SharePoint with Microsoft Power Automate

I have the SharePoint list of employees that includes a date column for their birth date in this Microsoft power automate tutorial I'm going to show you how to build a scheduled flow that will check this list every weekday and send an email to the employee to wish them a happy birthday this list also includes a person column for the employee's manager I'll also show you how to build a three-day and day of reminder email to the manager this flow can apply to a variety of scenarios such as a list of clients students or even projects your SharePoint list may look a bit different than mine you just need to make sure you have the following a column for the email recipient's name email address and a date my SharePoint list has a person column that stores the employee's Microsoft profile which includes their name and email address if your SharePoint list is using single line text Fields that's okay too you'll just need to adjust your flow to suit your data in my SharePoint list I have a test employee where I've set their birth date so that the month and day match today's date I'm also going to take note of the item ID so that I can use this in the flow while building and testing it the ID column is hidden by default if you aren't seeing it in your SharePoint list click on ADD column then show or hide columns and check off the ID column we'll start off by creating a manually triggered flow once the flow is ready to go we'll replace the manual trigger with the recurrence trigger in power automate create a new instant Cloud flow give your flow a name and select the manual trigger add the get items action select your site address and list name in the filter query field we want to filter the list by the test item I'll use the ID column for that and enter EQ for equals to and enter the ID number whenever you are building out a new flow it's always best practice to limit the number of items returned so that your tests take less time to run rather than returning all items from my SharePoint list I'm only returning a single item alternatively you can use a top count field to limit the number of items returned add a compose action to store the outputs from the get items action this action is optional remember to rename your actions to keep things organized I'm going to save my flow and run a test let's take a look at the output of the birthdate column because the birth date includes a year we can't use the odata filter query to match the day in month 2 today we'll need to use a filter array action instead depending on when you are running your flow it's good practice to add a convert Time Zone Action to ensure that the date comparisons are accurate in the base time field insert an expression we'll use the UTC Now function to get the current date and time for the source time zone search for a coordinated universal time and select that for the destination time zone search for your local time zone and select that for the format string search for round trip and select that next add a compose action to hold the current month and day so that we can use it in our filter array action this compose action is optional however if you are new to power automate it can help you to better understand what is going on in your flow personally I like to use compose actions to confirm the outputs of the dynamic content and expressions in my flow insert an expression we'll use the format date time function to convert the outputs from the action above into a format that we can use to filter the items returned from the SharePoint list click on the dynamic content Tab and insert the outputs from the convert Time Zone Action add a comma in single quotes I'll enter Capital mm Dash and lowercase DD to match the date format from the get items output run a test to verify the outputs of this compose action next add a filter or reaction we'll use this action to filter out the items returned from the get items action instead of using an apply to each and condition action which would have to Loop through each item in your list and use the condition action to check if the birth date contains today's date it's much more efficient to use a filter array action to filter out the items first before using and apply to each action in the from field insert the value Dynamic content from the get items action in the first value field select the column that is storing your date in my case I'm going to select the birthdate column since the birth date column in SharePoint is storing a full date which includes the year we need to change the operator to contains in the second value field insert the outputs from the compose action above this filter reaction will check the items returned from the get items action to see if any items have a birth date that contains today's month and day next add a compose action anytime I use a filter array action or a get items action with a filter query I always add a compose action to store the number of items returned insert an expression we'll use the length function click on the dynamic content Tab and insert the body outputs from the filter array action above let's run a test since this flow will be running on a schedule we want to check if there are any items returned before continuing on with the flow add a condition action in the first value field insert the outputs from the compose action above change the operator to is not equal to and insert a 0 in the second value field in the S Branch add and apply to each action insert the body Dynamic content from the filter array action next add a compose action to store the first name of the employee currently the only Dynamic content from the filter array action available to select from is the item and body you'll need to use an expression to access additional content let's take a look at the outputs of the filter array action from the previous test run to return any Dynamic content from the filter array action you need to use the item function and a key the key is a red text in between the double quotes I'm going to insert a few compose actions to hold the dynamic content from the filter array action these compose actions are optional although these Expressions that I'll be using can be inserted directly into a send an email action it slows down the flow building process I prefer to use compose actions to store the dynamic content from the filter array action especially when the Last Action in my flow is to send an email it's a lot more efficient to run a test and check the outputs of the compose action rather than waiting for an email to arrive the compose actions can also help with troubleshooting in the compose action insert an expression insert the item function followed by a question mark square brackets and single quotes enter the key of the dynamic content in between the single quotes in my case it's first to name all one word with a capital F and N add another compose action to store the employee's email address insert an expression add the item function followed by a question mark square brackets and single quotes the profile column in my SharePoint list stores the employee's Microsoft profile the employee's email is nested under the profile key enter profile with a capital P forward slash an email with a capital e if you are storing the email address in a single line text field enter the appropriate key here add a compose action to store the manager's first name insert an expression add the item function followed by a question mark square brackets and single quotes the manager's name is nested under the manager key I'll enter manager with a capital M forward slash and display name all one word with a capital d and n in order to return the first name I'll need to wrap this entire expression in a split function press the up Arrow key to move your cursor to the start of the expression enter split with an opening bracket press the down arrow key to move your cursor to the end of the expression add a comma in single quotes I'll insert a single space press the down arrow key to move to the end of the expression and enter a closing bracket this will split the display name by The Space Between the manager's first and last name to return the first name we'll need to grab the first part of the split string wrap the entire expression in a first function press the up Arrow key to go to the start of the expression and enter first with an opening bracket press the down arrow key to move your cursor to the end of the expression and enter a closing bracket add one more compose action to store the manager's email address the manager's email address is nested under the manager key insert an expression and add the item function followed by a question mark square brackets and single quotes enter manager with a capital M forward slash an email with a capital e run a test I'm going to confirm that the outputs of these compose actions are correct if you need to use additional Dynamic content feel free to add additional compose actions to your flow add a send an email V2 action we'll use this action to send an email to the employee whenever I add a send an email V2 action to my flows I always insert my email into the recipient field while I'm building and testing the flow insert a subject line and the outputs from the compose action that contains the employee's first name in the email body add a single line of text to confirm who the email will be sent to insert the outputs from the compose action that contains the employee's email address this line is just for testing purposes and when the flow is ready to go we'll remove it I have some email content on my clipboard that I'm going to paste into the email body I'm going to replace the employee's first name placeholders with the compose action output that contains the employee's first name add another send email V2 action I'm going to insert this action in a parallel Branch so that both emails will be sent at the same time we'll use this action to send an email to the employees manager I'll insert my email into the recipient field as well insert a subject line in the outputs from the compose action that contains the employee's first name in the email body add a single line of text to confirm who the email will be sent to I have some email content on my clipboard that I'm going to paste into the email body I'll replace the placeholder text with the appropriate Dynamic content I'm also going to bold the first line of each email body so it stands out run a test I've received two emails one for the employee and one for the manager I'm going to confirm that the content of the email is the correct content for the recipient of the email if you only want to send an email on the day of the birthday to the employee and manager use a timestamp in the description box below to skip ahead to the replace a trigger section of the tutorial to add a three-day reminder email to this flow we'll need to adjust the filter array action to filter items where the birth date is three days from today first I'm going to add a scope action to my flow to group my date actions together this makes it easy to collapse these actions with a single click it also helps to keep my flow organized I'll drag and drop these actions into the scope action add a compose action to store the number of days in advance of the employee's birthday I prefer using a compose action for this as it makes it easy to adjust the days at a later date instead of needing to edit the expression you only need to edit the number here next add another compose action to store the dynamic date we'll need an expression to add the number of days in this compose action to today's date insert the add days function click on the dynamic content Tab and insert the outputs from the compose action that is storing today's date add a comma and insert the outputs from the compose actions during the number of days to add another comma in single quotes enter the date format of capital mm Dash and lowercase DD the filter reaction is currently only filtering out items where the birth date contains today's month and day it may look like the filter array action can only accept a single condition with the advanced editor you can use an expression to filter by multiple conditions click on edit in advance mode we'll be building this expression in a text editor because it's impossible to type an expression in this editor as the cursor always jumps around copy this current expression onto your clipboard and paste it into a text editor switch back to basic mode and compose another condition in the second value field delete the outputs from the compose action that contains today's date and insert the outputs from the compose action that contains the date three days from today in my case it's the dynamic date compose action click on edit in advance mode and copy the expression to your clipboard and paste it onto the next line in the text editor first remove the add symbol from the second line next add a comma to separate the conditions since I want to filter out the employees where their birth date month and day is equal to today's month and day or is equal to the month and day in three days from today we'll need to use the or operator wrap this expression in an or function insert the word or after the first at symbol and insert an opening bracket go to the end of the expression and insert a closing bracket this expression will now check to see if the employee's birthday is today or is three days from today copy this expression to your clipboard and paste it back into the filter array action in the apply to each action add an additional compose action to store the employee's birth date insert an expression add the format date time function add the item function followed by a question mark square brackets and single quotes the employee's birth date is stored in the birth date column the key is birth date with a capital B place your cursor before the closing bracket add a comma and single quotes I'd like to format this date to only include the month and date enter capital mm-dd to streamline this flow I'm going to initialize a string variable to hold some text that I'll use in the email to the manager if the birthday is in three days the email should say in three days if the birthday is today the email should say today insert a condition action in the first value field insert the outputs from the compose action that contains the employee's birth date leave the operator as is equal to and insert the outputs from the compose action that contains today's date if the employee's birthday is today in the yes Branch will set the variable to today add a set variable action because the email that is sent to the employee will only need to be sent on the employee's birthday we'll need to drag this send an email action into the S branch if there isn't a match this means that the employee's birthday is in three days add a set variable action to the no branch this variable will contain the text in three days however instead of hard coding the number three insert the outputs from the compose action that is storing the number of days in advance of the employee's birthday this way if you decide to change it to 5 days or seven days the text stored in this variable will change dynamically the send an email to the manager action can remain outside of the condition branches because this email will be sent to the manager if the employee's birthday is today or in three days I'm going to adjust the subject line and insert the variable in the body of the email I want to convert the variable text to lowercase or that I'll use the two lower function and insert the variable in SharePoint I'm going to adjust the test employees birth date to three days from today let's run a test I should only receive a single email for the manager now that I've confirmed that the flow works I need to adjust the recipient of my email actions in the send an email action to the employee I'm going to remove my email address from the recipient field and insert the outputs from the compose action that contains the employee's email address the labels for compose actions aren't very helpful however if you hover over a label you'll see the name of the compose action appear below this is also why it's important to rename your actions in the manager send an email action I'm going to remove my email address from the recipient field and insert the outputs from the compose action that contains the manager's email address lastly I'm going to replace the manual trigger with the recurrence trigger adjust the settings of this trigger to suit your preferences I'd like this flow to run every weekday morning at 8 am I'm going to change the frequency to week and select all weekdays I'll select my time zone in 8 for 8 am if your SharePoint list contains items where the date column is empty you will need to adjust your filter reaction otherwise your flow will fail let me show you I'm going to remove the birth date value from my test item and run a test the flow failed let's modify the filter array expression since I already have this expression in my text editor I'm going to delete it and switch back to the basic mode in the first value field insert the birthdate Dynamic content and change the operator to is not equal to and insert an expression we'll use the null function click on edit in advance mode and copy this expression to your clipboard and paste it onto the first line in the text editor delete the second at symbol and insert a comma after the first line we'll need to wrap the entire expression with the and function insert the word and after the first at symbol and add an opening bracket place your cursor at the end of the expression and insert a closing bracket copy the expression to your clipboard and paste it back into the filter reaction let's run another test the flow ran successfully I'll add the birth date back into the SharePoint item and run one more test to confirm everything is still working because I've already removed my email address from the recipient field the email should be in my sent items folder if you found this video helpful and plan to build a flow like this one or one similar to it please consider giving this video a like what other flows are you looking to build let me know in the comments down below if you'd like to learn more about how to use a filter array action in your flows watch this video thanks for watching

Channel: A Creative Opinion

Views: 9,309

Keywords:

Id: d99dwBeTFvo

Length: 21min 19sec (1279 seconds)

Published: Sun Sep 10 2023