Automate Email based on Expiration Date and Grouping Recipients

[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!

[MichaelAnnis]

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!

[7cmor8]

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!

[Anonymous]

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.