Creating a flow to send a daily email of all new items in a SharePoint list in the last 24 hours

[stedtale]

I am trying to create a flow that sends a daily email of all new items in a SharePoint list in the last 24 hours. I need it to only send a list of new items added, and I need it to go out at 3pm each day. It also should not run on days when there is nothing new added to the list on any given day.

I have looked but cannot find a way to do this. Has anyone done this before?

I am very new to Power Automate and SharePoint, thank you for your help!

[OkanAT]

Hi,

Should be possible.

1. Create an automated flow triggered by a new item added to a Sharepoint List. 

2. Save this item to a new list. You need to make a new list that will contain all newly added items. 

3. you need a scheduled flow, which will run at 3pm and retrieve the items from this new list > paste it into your email > delete items from the new list so you can start fresh tomorrow.

so the solution above will contain 2 flows :). Hope the flow makes sense, if not. Ask me about it 🙂

[stedtale]

It does, this helped  a lot!

But now my table is formatting weirdly in the email when I test the flow. See below.

[OkanAT]

Hi,

you need an activity to convert html to text

[grantjenkins · Accepted solution]

@stedtale You don't need to create a new list for this and will only need a single flow that retrieves the items created in the last 24 hours.

For this example, I'm using the following list. Note that the last two items are the only ones created in the last 24 hours. Also note that the Status column is a Choice column type.

See full flow below. I'll go into each of the actions.

Recurrence is a trigger that will run the flow automatically at 3PM each day.

Get items will retrieve the items from our list. However, we have a Filter Query where we say only retrieve the items where the Created date is greater than or equal to the date 24 hours ago. The expression that you can just paste into the Filter Query is below. In this example, it will only retrieve the last two items.

Created ge '@{addHours(utcNow(), -24)}'

Next, we have a Condition to check if any items were returned. If the length of the items is greater than 0 then there is at least one item, and we can continue with our flow (in the Yes branch). The expression uses is below.

length(outputs('Get_items')?['body/value'])

The rest of the actions are within the Yes branch of the Condition (at least one item was created in the last 24 hours).

Compose Style is some CSS for styling the Table in our email. You already have your CSS that you can use - this is just some sample CSS that I use often.

<style>
    table {
        border-collapse: collapse;
    }
    table td,
    table th {
        border: 1px solid #ddd;
        padding: 6px 20px;
        text-align: left;
    }
    table th {
        background-color: #1C6EA4;
        color: white;
    }
</style>

Create HTML table is where I've mapped out my table. You already have what you would use here. Note that my Status column is a Choice column type, so I've used the Status Value from the dynamic properties to just retrieve the actual value of my Status column.

Because we've added a hyperlink in our HTML table, we need to do some extra work to get it to display correctly when we send the email. Power Automate will have issues with our < > and " so we need to replace them with the valid equivalents. To do this we can add a Compose and nested replace expressions as so.

replace(replace(replace(body('Create_HTML_table'),'&lt;','<'),'&gt;','>'),'&quot;','"')

Lastly, in our Send an email, we use the output from both the Compose Style and Compose Table (Not the Create HTML Table).

After the flow runs, we will get the following email output.

Note: If there weren't any items returned for a particular 24-hour period, then we wouldn't get an email at all.

Can you confirm what column type your Impact column is? It looks like a Multiple lines of text field set with enhanced rich text - is that correct?

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[stedtale]

Thank you! That worked for the HTML, but now I have other (JSON??) bits pooping up in my multi select cells.

Do I need to create a string variable for those entries?

[stedtale]

Oh, and yes you are correct I do have a rich text field as well as some multi select fields.

[grantjenkins]

Ok - I'll try to provide a solution for multiline and multi-select Choice columns later today.

[stedtale]

Thanks, I found this and it worked.

In the Select section, I used this function

join(xpath(xml(json(concat('{"body":{"value":', item()?['FIELDNAME'] , '}}'))), '/body/value/Value/text()'), ', ')

[ConnieKK]

Hi,

I tried this but instead of receiving the 3 list items I've added the last 24 hours into one email. I received 3 emails, one for each item and each email has duplicated the same record (pic below).

Can anyone help me on this?

Thank you

K.

[meryemElkhouS]

Thank you for sharing, but I receive multiple emails for this flow based on the number of rows in the SharePoint list that match the table.

[smfrench]

This is so helpful Grant! I was able to use this process no problem, although one of my fields is a multi-chose column. Would you be able to show how to do this when one of the columns is multi-choice?