Unanswered

Automate Email based on Expiration Date and Grouping Recipients

(0) Share

Report

Posted on by 7cmor8

Hello:

I new to this forum and Power Automate, but I feel like this could be a great solution for my current manual process. Every month I review my Excel file to determine who have Certificates of Insurance that are nearing expiration. The current process is to send notifications 30 days prior to that expiration. However, many of the recipients are the same and I group each of those projects that have expired within one email vs. sending out separate emails for each expired certificate. Does Power Automate allow you to group emails based on expiration and grouping recipients together. In the below example, I would like to send 1 email to Charles@gmail.GOV becuase he shows 2 expired Certificates. One of Charles's certificates is in compliance and I would like it to be excluded from the email. How would that look like in Power Automate that looks at 2 fields to run the automation? The first variable is if the Certificate shows nearing or is expired while the second variable is to send only one email to the recipient if they have multiple expired certificates? Thank you for any guidance on this!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

MichaelAnnis 5,727 Moderator on at

Like (0)

Report
Without building it for you, here is the logic:

Get Excel Data (should be sorted by e-mail prior)
Create new List %Attachments%
For each %Row% in %ExcelData%
Set %ExcelData[%Row%][3]% to %EmailAddress%
If %ExcelData[%Row%][6]% > -30
Add Attachment to %Attachments% (I don't see attachments here...if it's the Project Number, then just add %ExcelData[%Row%][1]%
EndIF
If %ExcelData[%Row + 1%][3] = %EmailAddress%
Next Loop
EndIf
Send Email with %Attachments%
Clear List %Attachments%
End #For Each

Ultimately, what this says is, start at row 1, get the email, get the attachment; if the next email is the same, go to that row, add the attachment. If the next row's e-mail is not the same, send the email with the attachments. Clear the attachment list, and start over with the next row/email.

This will work. Best of luck!

Was this reply helpful? Yes No
7cmor8 9 on at

Like (0)

Report

Thanks, Michael. I am sure that this is helpful, but I am very new to Power Automate. I am willing to learn, but I may need a refresher course in code. Can you point me in the right directions as to were how i can apply the logic. I have looked within Microsoft.com and I am a little over whelmed. Thanks in advance!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Hello @7cmor8

I have created an example flow based on the design of @MichaelAnnis.
I'm adding a bit of customization, but the basic idea is the same.

You can recreate sample flow above by copying and pasting source code below into the PAD Flow Designer.

System.TerminateProcessByName ProcessName: $'''EXCEL''' Excel.LaunchAndOpen Path: $'''C:\\Users\\User01\\Documents\\Certificates of Insurance.xlsx''' Visible: False ReadOnly: True LoadAddInsAndMacros: False Instance=> ExcelInstance Excel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData Excel.Close Instance: ExcelInstance # Email address of the first contac person SET ContactEmail TO ExcelData[1][2] Variables.CreateNewList List=> ExpiredCertificates LOOP FOREACH CurrentItem IN ExcelData IF CurrentItem['Days Until Expiration'] > (-30) THEN IF ContactEmail <> CurrentItem['Customer Info - Contact'] THEN # Simulate sending Email Message Display.ShowMessage Message: $'''%ContactEmail% %ExpiredCertificates%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed SET ContactEmail TO CurrentItem['Customer Info - Contact'] Variables.ClearList List: ExpiredCertificates ClearedList=> ExpiredCertificates END Text.FromDateTime DateTime: CurrentItem['Insurance Expiration Date'] StandardFormat: Text.WellKnownDateTimeFormat.ShortDate Result=> FormattedDateTime Variables.AddItemToList Item: $'''%CurrentItem['Project Number']% - Expiratin Date: %FormattedDateTime%''' List: ExpiredCertificates NewList=> ExpiredCertificates END END # Simulate sending Email Message for the last Contact Display.ShowMessage Message: $'''%ContactEmail% %ExpiredCertificates%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed

Please try it.
Thank you.

Was this reply helpful? Yes No