How to Send a SINGLE EMAIL ✉️ with multiple SharePoint list items | Build THIS Power Automate Flow

if you are trying to send an email to each user with a list of items they've been assigned to in a SharePoint list like this you'd be sending a single email for each item in your SharePoint list which isn't what you want a user would end up receiving multiple emails instead of one I have the SharePoint list of tasks each task is assigned to a user I'd like to send a single email to every user that has tasks that aren't complete and due this week in this power automate tutorial I'm going to walk you through how to build a flow that will send a single email to each user with the tasks assigned to them the logic in this flow can be applied to many different scenarios first I'll show you how to use a filter query to return the items from your SharePoint list that meet your criteria then I'll show you how to return a list of unique email addresses so that each user receives a single email lastly we'll compose an email that will contain an HTML table with a list of tasks for each user Make sure you stick around to the end of the video where I'll show you how you can customize the outputs of the task list by using an append to string variable action we'll start off by creating a manually triggered flow once once the flow is ready to go we'll replace the manual trigger with the recurrence trigger I'll be running my flow once a week in power automate create a new instant Cloud flow give your flow a name and select the manual trigger while I'm building this flow I'd like to mimic the date the flow is being run so I can fully test out my flow I'm going to add a date input to my manual trigger I'd like to return items from my SharePoint list that aren't complete but due this week I'll need to compose a date range in my filter query this section is optional if you prefer to compose your dates directly in the filter query field you can use a timestamp listed below to skip ahead I prefer to use compose actions in my flow first add a scope action I like to use scope actions to quickly collapse a group of actions also using scope actions make it easy for you to copy a group of actions from one flow to another this scope action will come in handy in future flows don't forget to rename your actions to keep keep things organized 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 the date from the manual trigger input when the flow is ready to go we'll replace this with the UTC Now function which will return the current date and time at the time the flow runs because I'm manually selecting a date I'll need to select my local time zone for both The Source Time Zone and the destination time zone dropdowns for the format string search for round trip and select that next add a compose action to hold today's date so that it can be used in the filter query 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 and expression the convert time zone action will return a date and time we'll use the format date time function to return the date only click on the dynamic content Tab and insert the converted time Dynamic content from the convert Time Zone Action add a comma and single quotes place your cursor inside the single quotes I'll insert this format for the date since this flow will run every Monday I will also need Friday's date of the current week add another compose action insert and expression we'll use the add days function insert the outputs from the compos action above add a comma and a number because I want to return Friday's date I'll insert the number four add another comma in single quotes place your cursor inside the single quotes I'll insert this format for the date this expression will add four days to the date stored in this compose action run a test to verify the outputs of these compose actions I'll select Monday for the date note that the Friday will be October 6th this compose action should output October 2nd and this one should output the 6th add a get items action I'll select my site and list name click on show Advanced options in my SharePoint list I have a choice column for the task status I want to filter out the items that aren't completed and are due this week you'll need to use the internal column name for your column this may not always match the name of the column you see here to get the internal column name click on the settings icon and select list settings select your column and check the address bar of your browser go to the end of the URL and verify the internal column name enter this text into the filter query keep in mind this text is case sensitive I'll enter NE for not equal to two and type the value in between single quotes in my case it's complete next I need to compose the part of the filter query that checks if the task is due this week insert the word and the internal column name for the task due date column is task due date I'll enter GE for greater than or equals to and insert single quotes in between the single quotes I will insert the outputs from the compose actions during today's date now we need to check for item that are less than or equal to Friday's date insert the word and I'll copy and paste the internal column name for the task due date column enter Le for less than or equals to and insert single quotes in between these single quotes I will insert the outputs from the compos actions during Friday's date I'd also like to sort the items by the task due date and then by the title I'll paste the task due date internal column name and type in ASC for ascending order add a comma and I'll Insert Title for the task name and ASC as well anytime I use a filter query in a get items action I always add a compose action to store the number of items returned insert an expression use the length function and insert the value Dynamic content from the get items action run a test if you have a large list of SharePoint items you may want to limit the number of items returned by inserting a number in into the top count field this will help to speed up your flow runs while you build and test your flow add a condition to your flow we'll use this action to check if there are any items returned from the get items action insert the outputs from the compose action above change the operator to is not equal to and enter zero if there are no items returned we'll do nothing if the number of items returned is not equal to zero will'll insert the rest of the actions for the flow into the yes Branch we'll need to pull the details of each user who has an assigned task that is due this week add a select action in SharePoint the user profile is stored in the assigned to column insert the value Dynamic content from the get items action into the from field in my flow I only want to return the user's name and email in SharePoint the user's profile is stored in the assigned two column I'll input a key for the name and I'll insert the display name Dynamic content from the assigned two column here I'll inp put a key for the email and insert the email Dynamic content from the assigned two column here run a test as you can see here the outputs of the select action returns an array of the name and email for the user assigned to each task we need a way to return an array of unique values add a compose action we'll need it expression insert the Union function the Union function combines two arrays or objects and removes any duplicate items select the dynamic content Tab and insert the outputs from the select action add a comma and insert the outputs from the select action again run a test the outputs of this compose Action Now contains an array of unique users now that we have an array of unique users we can use and apply to each action to Loop through each user insert the outputs from the compos action above here next add a scope action this scope action is optional in this scope action I'm going to add a few compos actions to store the user's full name first name and email address these compose actions are also optional however I find it helps with troubleshooting when I can see the details of the current item being looped through I'll use this compose action to store the user's full name in insert an expression use the item function add a question mark square brackets and single quotes in the single quotes enter the key for the user's name in my case it's name with a capital N this will return the user's full name I also want to return the user's first name only which I'll use in the email body add another compose action insert an expression we'll use the split function insert outputs from the composed action above add a comma and single quotes insert a space between the single quotes this function will split the user's first name and last name at the space to return the first name only wrap the entire expression in the first function press the up Arrow key to go to the start of the expression and type in first with an opening bracket press the down arrow key to go to the end of the expression and type in a closing bracket add one more compose action to store the user's email address run a test let's take a look at the output of the compose actions you may be tempted to insert a get item action into the apply to each action however because we already have a get items action in the flow here it would be inefficient and unnecessary to use that action again instead we'll use a filter array action for a more detailed tutorial on how to use the filter array action make sure to check out this video first I'll add another scope action to group the next set of actions add a filter array action in the from field insert the value Dynamic content from the get items action in the first value field insert the email Dynamic content of the person column in my case it'll be the assigned to email Dynamic content I'll leave the operator as is equal to and in the second value field insert the output from the compose action that is storing the current user's email address this filter array action will take the items returned from the get items action and filter out the items that are assigned to the current user being looped through whenever I use a filter array action in my flow I always use a compose action to get the number of items returned insert an expression use the length function and insert the body outputs from the filter AR Reay action above run a test review the count of items returned from the filterate action for each Loop now that we have a filtered array of each user's items it's time to create an HTML table add a create HTML table action to your flow in the from field insert the body Dynamic content from the filter array action click on show Advanced options in the columns dropdown change this to [Music] custom in the header Fields enter the headers for your table I'll insert task name task description and task due date to return the dynamic content from the filter array action you'll need to use an expression insert the item function a question mark square brackets and single quotes in between the single quotes we'll need to insert the dynamic content key review the outputs of the filter array action from your previous flow Run for the dynamic content Keys the keys are the red text between the double quotes the task name is stored in the title column I'll type in title with a capital T for the task descript description I'll type in description with a capital D for the task due date I'd like to format the date I'll use a format date time function I'll insert the item function I'll type in task due date remember that the keys are case sensitive add a comma in single quotes and I'll enter this date format run a test review the outputs of the create HTML table action add a send an email V2 action to your flow whenever I use a send an email V2 action in my flows I always insert my email into the recipient field while I'm building and testing the flows once I've confirmed the format of the email I'll replace my email address with the appropriate recipient make sure you in inserting this action inside the apply to each action for the subject line I'd like to include some Dynamic content I'll insert the count of items as well as the user's full 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 user's email address this line is just for testing purposes and when the flow is ready to go we'll remove it I'll highlight it and bold the text I prefer to mark my automated emails with a line of text next I'll add a greeting and insert the outputs from the compose action that has the user's first name I'll add a bit of text and insert the outputs from the create HTML action run a test review the email and check the content of each email the default output of the create HTML table action isn't great let's style it a bit it's important to note that the support for CSS in Outlook is very inconsistent the HTML table will look different depending on the version used also email clients prefer the use of inline Styles in the description box below I've included a link to the CSS I'm using in this tutorial add a compose action after the create HTML table action I'll paste the CSS into the compose action after the closing style tag insert the outputs from the create HTML table action in the send an email V2 action I'll remove the output from the create HTML table action and replace it with the compose action that was just added run a test this table looks slightly better with CSS customize a table to suit your needs remember that not all CSS will be displayed in Outlook if you don't like the look of the table and want to style the list of items a bit differently maybe something like this or this you'll need to make a few adjustments to your flow I've created a copy of the original flow to customize the look of the list we'll need to initialize a string variable in the root of the flow I'll delete these two actions I'm going to copy the send an email V2 action to my clipboard so I can add it back in later I'll delete it for now next we need to Loop through each user's items returned from the filter array action add and apply to each action insert the body outputs from the filter array action next add a compose action we'll use this compose action to store the details of each item returned I'll insert static text as labels for the dynamic content we'll need to use Expressions to access the dynamic content from the filter array action just like we did in the create HTML table action insert the item function a question mark square brackets and single quotes in between the single quotes insert the dynamic content key add an append to string variable action insert the outputs from the compos action above and insert a line break each time the apply to each action Loops through a user's task it will append the details of each task composed here to the string variable the string variable is nested inside the apply to each action that is looping through each user we need to clear the variable before the apply to each action Loops through the next user if we don't clear the variable each time this apply to each action Loops through a user it'll continue to append tasks to the variable add a set variable action make sure this action is inserted at the end of the apply to each action looping through each user insert the null function run a test inside this apply to each action add back the send an email V2 action I'll insert the outputs from the composed action above that contains a variable or you can simply insert the variable here if you didn't insert a compose action above run another test let's review the email the text needs a bit of styling to do this we'll need to insert some HTML in the compose action with the user item details wrap this content in a paragraph tag add a line break after each line next wrap each of the text labels with a strong tag run another test if you are familiar with HTML you can style this text any way you'd like here's one more way you could style the text customize the HTML to suit your needs after youve finalized the layout and format of your email you can replace a trigger of your flow I'll delete the manual trigger and insert the recurrence trigger adjust the settings of this trigger to suit your preferences I'd like this flow to run every Monday morning at 8:00 a.m. in the convert Time Zone Action insert the UTC Now function into the base time fi field in the send an email V2 action I'll remove my email address from the two field and insert the outputs from the compose action with the user's email address I'll also delete this first line in the email body if you have more than 100 items in your list you will need to turn on pagination and set a threshold click on the three dots of the get items action and select settings toggle on pagination and insert a thresold of items to return you can use an expression to customize the text based on the number of items returned in this compose action in the subject line I'll replace the word tasks with an expression we'll use the equals function insert the outputs from the compos action that returns a number of tasks for the user add a comma and enter a one next wrap this expression in an IF function press the up Arrow key to go to the start of the expression enter if and an opening bracket press the down arrow key to go to the end of the expression add a comma and enter the word task between single quotes press the down arrow key again to go to the end of the expression add another comma and enter the word tasks between single quotes press the down arrow key to go to the end of the expression and insert a closing bracket press okay the IF function takes three parameters first an expression then a value to return if the expression is true and a value to return if the expression is false I'm going to copy this expression and replace this text here with an expression as well I'll edit the true text value to say this task and the false text value to say these tasks this user has only two tasks due this week I'll mark one of these tasks complete so that we can see what an email would look like with a single task versus one with multiple tasks Let's test it out what other automations are you struggling to build let me know in the comments down below if you found this video helpful please consider giving this video a like don't forget to subscribe so you don't miss out on any other power automate tutorials thanks for watching

Channel: A Creative Opinion

Views: 21,697

Keywords:

Id: mmvJWKK690Y

Length: 23min 34sec (1414 seconds)

Published: Sun Oct 15 2023