Summary: How to use Microsoft Flow (Power Automate) to create an email report on SharePoint Online list items.
Date: Around 2020
Refactor: 1 March 2025: Checked links and formatting.

It can be very beneficient to create an email report on all items that were created in a SharePoint Online list in the previous month. To do so, I created two flows, one to create the email and another one to intercept that email to store it on sharepoint online as a bonus (and as a evidence case).

Step 1 and 2: a monthly recurrence trigger and get items from the sharepoint list:

Step 3 and 4: Filter the list of items to only select the items that were created last month and then compose the remaining items into an html table:

Note the following expressions and input:

• Created contains expression: addDays(utcNow(), -30, 'yyyy-MM')
• If the list contains more than 100 listitems you need to change the settings of the “Get Items” step to enable pagination and configure the threshold to whatever fits your needs, for example 2000.
• Html table:

    <table style="width:100%; border: 1px solid #dddddd; text-align: left;"><tr>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="width: 200px; text-align: left;"> EXPRESSION BELOW </td>
    <td style="text-align: left;"> EXPRESSION BELOW </td>
    </tr></table>

• Expressions:
  • items('Apply_to_each')?['Check']?['Value']
  • items('Apply_to_each')?['Created']
  • items('Apply_to_each')?['Author']?['DisplayName']
  • items('Apply_to_each')?['Editor']?['DisplayName']
  • items('Apply_to_each')?['Approval']?['Value']
  • items('Apply_to_each')?['TOPdesk']
  • items('Apply_to_each')?['Comments']

Note that you need to know the names of the fields and their values to display them correctly in the html table. If you don't know the name, add “Current item” of the dynamic content so all data will be displayed in the email.

Step 5: Send an html email out to the postmaster mailbox:

Note the following expressions and input:

• Email subject expression: addDays(utcNow(), -30, 'yyyy-MM')
• Email advanced properties: HTML: Yes
• Body email:

    All Infra checks that were done last month: <br>
    <br>
    <table style="width:100%; border: 1px solid #dddddd; text-align: left;"><tr>
    <th style="width: 200px; text-align: left;">Check</th>
    <th style="width: 200px; text-align: left;">Created</th>
    <th style="width: 200px; text-align: left;">Created By</th>
    <th style="width: 200px; text-align: left;">Modified By</th>
    <th style="width: 200px; text-align: left;">Status</th>
    <th style="width: 200px; text-align: left;">TOPdesk</th>
    <th style="text-align: left;">Comments</th>
    </tr></table>
    EXPRESSION BELOW

• Expression to join the composed data: join(actionOutputs('Compose'),'')

This is a quite simple flow:

• https://flow.microsoft.com/en-us/blog/email-digest-date-manipulations/
• https://docs.microsoft.com/en-us/flow/data-operations