Daily consolidated email alert before expiry

Most of the time we are getting requirement for a consolidated email alert for before expiring the important documents like Employee contracts, Lease agreements and many other documents which need continuous monitoring to avoid financial impact on the company.

So here I am showing you for Employee contracts expiry, an alert will trigger automatically before 10 days of expiry date which we have entered in our document Library

My Library which I used for this blog:

My email output will show as below :

Let’s start :

Open your power automated home page > Now click on the “Create” button from the left navigation and click on the “Scheduled flow” option.

Once you click on the “Scheduled flow” option, then on the next screen enter the flow name and schedule information. In my case flow name is “Contract Expiry Alert” and the schedule is daily 10:00 AM.

Now add New Step:- First initialize the variable, here I am initializing a variable to calculate the date and which is 10 days later from the day when scheduler ran. To get the items that are going to expire in the next 10days.

Click on “Add an action” and search for Initialize variable, once you will click on it just set the variable name and expression like below

Expression : addDays(utcNow(),10,’yyyy-MM-ddT00:00:00′)

Click on New step and add action “Get files (properties only)” and configure like below

*To get the alert for an item which is expiring in the next 10 days, you need to use a filter query. It’s the same as the OData query which you are using in rest. So in the previous step, we have calculated the date and in this step, we are filtering the list item based on the date which we have calculated.

Limit Column by view, if you have many columns and only a few you wanted to display then simply create a view in SharePoint List and select the same view.

Filter query: ContractEndDate eq datetime'<Varible>’

Now add a new step and initialize one more variable to store the filter item result in the required format. We have to set the default value to null.

Add a new step and search for action “Apply to each” and configure like below,

So here total 3 actions we are doing,

  • Select an output from the previous step
  • Composing the HTML table content
  • Append to a string: Use to append the string

Add a new step and check null for the variable which set in the previous step.

And add “Send an email notification” action to the “Yes” section and configure like below

You need to enter the target email id, subject, and configurable body.

Source code for the email body:

<style>
    table.emailtbl {
        background-color: #EEEEEE;
        width: 100%;
        text-align: left;
        border-collapse: collapse;
    }

    table.emailtbl td,
    table.emailtbl th {
        border: 1px solid #AAAAAA;
        padding: 3px 2px;
    }

    table.emailtbl tbody td {
        font-size: 13px;
    }

    table.emailtbl thead {
        background: #1C6EA4;
        color: white;
    }

    table.emailtbl thead th {
        font-weight: bold;
    }
</style>
<hr>
<p>Greetings, </p>
<p>Please find below the list of contract expiring shortly. Please do the needful.</p>
<br />
<table class="emailtbl">
    <thead>
        <tr>
            <th>Employee Name</th>
            <th>Employee No</th>
            <th>Contract Start Date</th>
            <th>Contract Expiry Date</th>
        </tr>
    </thead>
    <tbody>
        @{variables('htmlTable')}
    </tbody>
</table>
<br />
<hr>

and that’s it, flow is ready. You can run and test the flow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s