Automatically email supplier when their certificate expires

As you will have a unique email address for each company it makes the flow quite simple. My SharePoint list looks like this and only Anguilla and Martinique have certificates that have expired. So no email will be sent for the contact at Guadeloupe.

Make sure you get the internal name for each column as you will need this in the flow. You get the internal name by going to list settings, select & open the column and in the address bar at the top the internal name is after Field=. 

I have used the classic designer in Power Automate as a) I prefer it and b) it is easier to do screenshots. A flow will always have only 1 trigger then multiple actions. In this case I have used a recurrence schedule trigger set to run every 1 week on a Monday at 8 am. Set your time zone as well. Most people don't like being bombarded with an email every day which is why I have set it to run only once a week.

Then initialize an array variable.

Next, add a SharePoint get items action. You don't want to bring back every single item, only those where one of the columns is less than today. So open the advanced options and in the Filter Query field type/select the following. In my list when I created the column ISO 14001  and the other ISO columns it made the internal name  OData__x0049_SO14001 and you follow that with lt (less than), then inside single quotes '' enter the expression utcNow()  from the dynamic content box. utcNow() stands for today. Always make sure that each utcNow() expression is inside single quotes and use or so that if any of the columns for that item has expired it will bring it back.

Next, add an apply to each and in the field select value from the get items section of the dynamic content box.

Next, add a Set variable and in the name field select the variable you created. Inside  square parenthesis select Current item from the dynamic content box.

Then add a create html table action. In the From field select the variable from the dynamic content box. Make sure that for the Columns field you select Custom. and then in the left field select the column header (it doesn't accept spaces). In the right field, for the company name you can just select the Title column fro the dynamic content box. But for the date columns you will want to format the date into your locale, so for me that dd/MM/yy. So you add the expression: 

formatDateTime(item()['OData__x0049_SO14001'], 'dd/MM/yy')

and then do the same for the other columns, using the internal column name.

By default Power Automate creates very basic html tables. You can make them look much better by adding a Compose action and pasting in CSS followed by the output of the Create HTML table action.

The CSS I have used is:

<style>
table {
  border: 1px solid #1C6EA4;
  background-color: #EEEEEE;
  width: 50%;
  text-align: left;
  border-collapse: collapse;
}
table td, table th {
  border: 1px solid #AAAAAA;
  padding: 3px 2px;
}
table tbody td {
  font-size: 13px;
}
table thead {
  background: #003399;
  border-bottom: 2px solid #444444;
}
table thead th {
  font-size: 15px;
  font-weight: bold;
  color: #FFFFFF;
  border-left: 2px solid #D0E4F5;
}
table thead th:first-child {
  border-left: none;
}
</style>

Finally, add a Send an email (v2) action -  although at my company we tend to use shared mailboxes so the email doesn't come from the flow creator. Add your text then select the outputs of the format table compose action you did immediately before.

This is the email that is received.  The email for Martinique was also received in that contact's email. But no email was sent for Guadeloupe which is what we expected.

Hope that helps.

Rob
Los Gallardos
Microsoft Power Automate Community Super User
Principal Consultant, Power Platform, WSP Global (and classic 1967 Morris Traveller driver)