Send reminder email based on date in excel

[casandrow]

Hi!

I am relatively new to power automate. I am trying to build a flow that sends an email reminder at different times before a deadline date (e.g., 120, 60 and 30 days before the date). Here is an example table:

So for example, how would I automate an email to be sent 30 days before what is in "Expiration Date"? Would I make a new flow for each iteration (emails 120 and 60 days before the expiration date)? Help is greatly appreciated - thanks in advance!

[ccc333ab]

I've put something together, but there probably is a much simpler way, but this should work...couple of notes: 

• Excel dates when they get brought across into Power Automate get converted to integer. You can convert to date by the following: 

addDays('1899-12-30', int([DATE TO BE CONVERETED]), 'dd-MM-yyyy')​

I used integer date since I was going to just check 30, 60 and120 but you may want to instead of doing what I did and adjust your formula's appropriately.   

• I use the Parse JSON action, I'll show you also how I got the schema after the solution 
• I also converted utcNow to my timezone, but you can just use utcNow if you want to. 

The Flow: 

1. I convert todays date to integer date, so I can compare this with what comes out of Excel. 

This formula converts the Time Zone converted date to Integer Date in my Compose Statement: 

div(sub(ticks(formatDateTime(body('Convert_time_zone'), 'yyyy-MM-ddT00:00:00')), 599264352000000000), 864000000000)

2. Get the rows form Excel, and then filter out only those rows that will expire in 30, 60 and 120 days. 

 

 The filter here looks really complex but its just 3 repeated statments checking if the expiry date is equal to todays date + 30, +60 and +120): 

@or(equals(item()?['ExpirationDate'], string(add(outputs('Compose_-_Today_in_Integer'), 30))),equals(item()?['ExpirationDate'], string(add(outputs('Compose_-_Today_in_Integer'), 60))),equals(item()?['ExpirationDate'], string(add(outputs('Compose_-_Today_in_Integer'), 120))))

3. Next, Parse the JSON so we can grab out individual fields later.  

4. Now loop through the array and send an email...I am using a Compose statement instead just to show you the results. 

RUN: 

I ran through on a dummy spreadsheet and got these results (I set it up so taht I would only get 2 results, one with a 30 and with a 120 date). 

Parse JSON Schema: 

To get the JSON Schema you can do the following: 

1. WIth the above flow, just create up to the Excel "List Rows present in table". 

2. Run flow. 

3. In the  "List Rows Present in table", click the downlod link in the outputs 

4. Copy the output.

5. Go back and edit the flow and create up to the Parse JSON action. 

6. Click on Generate from Sample and paste. 

[casandrow]

Hmm, so I followed your steps and my flow fails on the parse json step. I get an error saying the schema validation failed because it saw an object and expected an array. 

Here is the schema I put in:

{
  "type": "object",
  "properties": {
    "statusCode": {
      "type": "integer"
    },
    "headers": {
      "type": "object",
      "properties": {
        "Pragma": {
          "type": "string"
        },
        "Vary": {
          "type": "string"
        },
        "x-ms-request-id": {
          "type": "string"
        },
        "OData-Version": {
          "type": "string"
        },
        "Strict-Transport-Security": {
          "type": "string"
        },
        "X-Content-Type-Options": {
          "type": "string"
        },
        "X-Frame-Options": {
          "type": "string"
        },
        "Timing-Allow-Origin": {
          "type": "string"
        },
        "x-ms-apihub-cached-response": {
          "type": "string"
        },
        "Cache-Control": {
          "type": "string"
        },
        "Date": {
          "type": "string"
        },
        "Content-Type": {
          "type": "string"
        },
        "Expires": {
          "type": "string"
        },
        "Content-Length": {
          "type": "string"
        }
      }
    },
    "body": {
      "type": "object",
      "properties": {
        "value": {
          "type": "array",
          "items": {
            "type": "object",
            "properties": {
              "ItemInternalId": {
                "type": "string"
              },
              "Name": {
                "type": "string"
              },
              "Email": {
                "type": "string"
              },
              "Expiration Date": {
                "type": "string"
              },
              "@odata.etag": {
                "type": "string"
              }
            },
            "required": [
              "@odata.etag",
              "ItemInternalId",
              "Name",
              "Email",
              "Expiration Date"
            ]
          }
        },
        "@odata.context": {
          "type": "string"
        }
      }
    }
  }
}

And here is the error:

[
  {
    "message": "Invalid type. Expected Object but got Array.",
    "lineNumber": 0,
    "linePosition": 0,
    "path": "",
    "schemaId": "#",
    "errorType": "type",
    "childErrors": []
  }
]

Additionally when I tried to make the apply to each, I got Apply to each 2...

Thanks for your help so far though!

[ccc333ab · Accepted solution]

Sorry, my mistake...was late...that's my excuse :). Don't use the output from Excel, grab the output from the Filter Array for the Parse JSON schema. 

That'll fix the 2nd issue, don't use the Email/Name etc. from the Excel action, grab it from the Parse JSON action. 

Sorry about that. 

[pmcl]

Hi, I know I might be 3 years late but I am currently running through this process. Everything runs perfectly fine up until the send an email stage where I get this error message:

I'm not sure if the changes I made would affect the flow or not but the things I changed around from the flow you showed was the Destination Time Zone (Changed to EST) and the specified reminder days (for the numbers on when the emails send out, I changed them from 30 - 1, from 60 - 2, and from 120 - 3)

If you know what the issue might be or need any more information let me know.
Thanks!