Problem

Asset information containing information about office equipment borrowed by employees was stored inside of an SharePoint List. (For the purposes of this blog, I will use fictional sample data).

We needed to automate a way to send emails to remind employees of the items they borrowed and when they were due back.

The problem was that one person borrowed several items and we did not want to send that one person separate emails for every item they borrowed. We needed a solution that send a summary email of all the items borrowed. So if Joe Bloggs borrowed 10 items, he should receive one email listing the 10 items.

I developed a solution using Power Automate, that I hope will help someone.

Sample Data

Firstly, I will be using sample fictional data to demo how I built this solution, see sample below

Fictional Sample Data

You can see from above jbloggs@domain.com has borrowed 4 items, but has returned one item, so he should receive an email listing only the 3 items he has borrowed – (Not the returned item)
jsloan@domain.com has borrowed 3 items, but has also returned 2, so she should only receive an email listing the one outstanding borrowed item – and so fourth.

Solution

For the purposes of this demo, I am using the Manual Trigger Action to initiate the flow; however, you can use this email to send on a recurring basis if you need to.

Using Power Automate, I connected to the SharePoint List using the Get Items Action, and then using the ODATA Filter, I filtered out assets that had already been returned, as below

Get Items Action

This only returned items from that SharePoint list where the Returned Value was set to No. (Note : I use JSON Viewer to view output of results, as below)

Directly below, I added a Select Statement to Get all the Asset borrowers from that SharePoint List and map it to a variable called ‘Email’, as below

The returned the following output :

[
  {
    "Email": "john.doe@domain.com"
  },
  {
    "Email": "jsmith@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jsloan@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  }
]

(Remember, we are returning all the Emails who have borrowed items Only, and assigning the variable Email to their Email Address.)

Next step, I added a Compose Action and added the following Expression to get a unique list of Emails (or a distinct list of emails)

This returned the following JSON,

[
  {
    "Email": "john.doe@domain.com"
  },
  {
    "Email": "jsmith@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jsloan@domain.com"
  }
]

You can see now we have a unique or distinct list of email address.

I then added an Apply to Each (Which i have renamed to ‘ForEachEmail), and added in a Filter Array inside of the Apply to Each, as below

This Apply to Each loops through each of the Unique Emails and lists all the items for that Email

For Each Unique Email

Now that we have have all of our Unique Emails and all the items they have borrowed, the hardest part is done.

Next is the Clean up and to make it look presentable.

Cleaning up the Output

I added a Create HTML table just below the Filter Array, and instead of selecting Automatic Columns, I selected Custom and defined my own columns, as below.
The reason for this is because if you select Automatic, the HTML will show every single column inside of SharePoint, such as the Created Date, Modified Date etc

Custom Columns in HTML Table

This produces the following Output for each Unique Email,

Now, for each Email, we have an output presented as a HTML Table 🙂

I didn’t quite like the output design of this HTML, so I did a quick search and came across this blog which gave fantastic examples of HTML Styling.
I created a Compose Action, which I renamed Style and copied in the HTML Style found on Ryan’s Blog, as below

HTML Style

The Next step is to put all of this into an Email.

Composing the Email

I added the Send an Email (V2). Ensure the email is sent to the right person, by inserting the Expression as body(‘Filter_array’]?[0]?[‘EmailAddress’]

An important step is to add the Styling to the body of the email. There are two ways to do this :

  1. You can edit the email in Code View and insert the styling.
  2. You can add the Styling Action directly into the email, as I have done below

Once the Styling as been added, you can compose the rest of the email as I have highlighted below

Composing Email

Now to give it a try, see below for the results of the email received and how the Email Looks

Emails Received

I hope this has helped someone, please feel free to comment below for any questions.