Export personal report and send to multiple recipients

[Anonymous]

Hi, 

I want to create a monthly report that is exported monthly from my SP list and send to multiple recipient.

The report should only contains the items that the recipient created.

I've done some research but still can't figure out how to generate the personalized report properly.

[grantjenkins · Accepted solution]

Hopefully this is what you're looking for.

For this example, I'm using the following List which has items created by Grant and Adele.

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

Recurrence is set to run once a month.

Get items returns all the items from the SharePoint List.

Select takes in the data (value) from Get items and Maps the Created By field. Note that I'm using Text mode (see arrow on screenshot below) to get a simple array of Created By objects.

Apply to each iterates over each of the Created By objects. However, we need to loop over each person once, so we use union and pass in the output of our Select twice so it removes any duplicates. The expression is below:

union(body('Select'), body('Select'))

Filter array filters the output (value) from Get items so only items that were created by the current Created By are returned.

Create HTML table uses the output from Filter array and maps the fields we want to show in our table. In this example, I'm just showing the Title and Created date. The expressions used are below. Note that I've also formatted the Created date.

//Title
item()?['Title']

//Created
formatDateTime(item()?['Created'], 'dd MMM yyyy')

//To get other fields you would use the following format
item()?['YOUR_FIELD_NAME']

Compose Style is some CSS we will use to make the HTML table look nicer in our email.

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

Send an email will send an email to the current Created By with the Table contains their data. The expression to get the Created By Email is below. And the two outputs are from Compose Style and Create HTML table.

items('Apply_to_each')?['Email']

//If you wanted to get the Display Name to use in the body of the email you would use
items('Apply_to_each')?['DisplayName']

The two emails sent would look like this:

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

[Anonymous]

Hi grantjenkins,

Thank you so much!

The process works as a charm. 

I'm now working further to optimize the flow for the following subject:

1. Changing the email sender to a shared mailbox.

2. Limited the extracted items to the ones submitted within a month.

3. Convert the HTML table to PDF and send as attachment.

Will share further ones having a finalized one 🙂