Send email 30 days and when expired to an individual or a group.

[Ikaika]

I'm still new to Power Automate and SharePoint I have a SharePoint list of certificates that have an expiration date that I would like to do the following:

Expiry Date: Date and time
Status: Choice [Pending, Active, Revoked]
Certificate Name: Single-line text
Agent: Person or Group

Highlight the certificate yellow when the certificate is 30 days out from the Expiry Date and the Status is NOT revoked. Email an Agent (individual or group) and display in the body of the message what certificate is about to expire.

Highlight the certificate in red when it has expired and its Status is NOT revoked and email an Agent (individual or group) and display in the body of the message what certificate had expired.

[AndreasN]

@Ikaika 
Hello, I have a flow very similar to the one you describe. I'll walk you through it.

Let's assume you want to run this everyday, so your trigger would be a "Recurrence" with interval of 1 day.

Create a "Get files (properties only)" action, in the Filter Query your expression should look like this:
ExpiryDate le 'formatDateTime(addDays(utcNow(), +30), 'yyyy-MM-dd')' and Status ne 'Revoked'

Now I will assume that the agent column will hold the e-mails you would like to send to, furthermore, I will assume that each agent might have several different documents.
Create a select action, where you map the Agent corresponding to their e-mail. In my case, my column is called "Contact E-mail", you would pick the "Agent" column here, given that this column holds their e-mail.

Next we want to get all unique agents (e-mails). Create a compose, with the following expression, using your select action as the input. My select action is called "Select all agents", therefore, the expression will be:

Union(body('Select_all_agents'), body('Select_all_agents'))

Create a Parse JSON action using the output of your "get all unique agents" compose.

This Schema should work:

{

    "type": "array",

    "items": {

        "type": "object",

        "properties": {

            "email": {

                "type": "string"

            }

        },

        "required": [

            "Agent"

        ]

    }

}

So what we have currently, we are going through all the individual files, looking for any file where the expiry date is within 30 days and the status is NOT revoked. We then select all agents, since we need to find out how many different unique agents we have. We do this by composing the agents, and then passing it through a parse JSON so that we can go through each individual agent.

Now we need to figure out, how many files each agent has, which fits our criteria. We do this, because if one agent has multiple documents which fits our criteria, we do not want to spam their inbox by sending an e-mail with each document, instead we want to go through each agent and attach the documents to an HTML table so we only send 1 e-mail per agent rather than 1 e-mail per document.

Create this Apply To Each, choose your parse JSON as the input for the apply to each. First compose called "Each Agent", has the following expression items('Apply_to_each_2')['Agent']

This basically creates a compose, so that the agent e-mail is isolated every time we run through each individual agent, now we want to check how many files that fits our criteria, has this agent associated with them. We do this by creating a filter array.

The input to the filter array should be the output of the "Get files" action we created to begin with, we want all documents where the agent column = the compose output we just created called "each agent". Remember, our "get files" action will only include the documents which fits our criteria of 30 days expiry and status is NOT revoked.

The output of this filter array will be all the documents, that fits that criteria and is associated to that agent, now we want to send the agent an e-mail. We do this by creating an HTML table.

The input of the HTML table should be your output of your filter array. I have chosen "Document" "Expiration date" and "Type", as my headers. For document I use the following expression in value:
item()?['{FilenameWithExtension}']  this gives you the full file name. 
Expiration date I have chosen item()?['ExpiryDate']

My flow runs for ISO certificates, so I've just hard coded "ISO", as this only runs for ISO certificates.

If you want to be fancy, and have a space between ExpiryDate do the following: 
Write Expiry^Date -> create a compose action with the following expression: replace(body('Create_HTML_table'), '^', ' ')

Lastly I want to format my HTML table, you can do that yourself in an HTML formatting website. This is not really necessary, I use this one:

Then you just create a "Send an email", the "To" destination should be your "Each Agent" compose, in the body of your e-mail text you want to use the dynamic content of your HTML table, or the compose if you choose to remove the "^" and create a space.

To highlight the certificate, just create a "Update file properties", use the output of your "get files", in SharePoint set the default color of e.g. "pending" to yellow. In the update file properties, just change them to "Pending" or some other choice which is yellow. This should be done through the choice column configuration in SharePoint.


Create a copy of the flow, change the filter query in the "get files" from this:
ExpiryDate le 'formatDateTime(addDays(utcNow(), +30), 'yyyy-MM-dd')' and Status ne 'Revoked' 

To:

ExpiryDate ge 'formatDateTime(utcNow(), 'yyyy-MM-dd')' and Status ne 'Revoked' 

And change the e-mail body text as well as the "update file properties" accordingly to what you need it to do.

Hope this makes sense, let me know if you need help.

Best regards,
AndreasN

[Ikaika]

I'll look it over and give it a try. I'll let you know how it turns out.

Thank you

Ikaika