Sending email based on excel date in HTML table but only send one email

[justindee]

Hi PA Community, 

I have built a flow that sends an HTML table to selected emails when a specific column has the current day's date in it. The flow works perfectly fine and creates and sends the email, however, it sends an email for each instance of the date inputted into the Review Date column. For example, 10 rows have a review date for today, it sends 10 emails with the HTML table. 

I just need one email sent with all data relevant to the day's date. 

The flow set up as below:

Most recent flow run as below:

The compose step checks for every instance where a column has today's date and then composes 10 emails. How can I make it so the flow checks for the date then only composes one email with all data

Thanks in advance

Justin

[CFernandes]

@justindee - The send to email should be outside the "Apply to each loop".

• You need to initialize a variable of type string at the start of the Flow...
• Set  the variable to HTML inside the "Apply to each" loop.
• Use the variable in the "Outlook" action outside the loop.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

P.S. take a look at my blog here and like & subscribe to my YouTube Channel thanks.

[justindee]

Hi @CFernandes, thank you for this, that seemed to work in terms of sending me only one email. However the data the flow is picking up is a bit odd. It only sends the very last instance of where the date is set and puts it in every row of the HTML table:

Do you know why this would be?

I can see from the test run data it does pick up every instance of data with today's date in:

{
    "from": [
        {
            "@odata.etag": "",
            "ItemInternalId": "0320f742-93d4-4604-95ca-fecbeb236352",
            "L1": "",
            "L2": "",
            "L3": "",
            "L4": "Land Information Certificate",
            "L5": "",
            "L6": "",
            "URL": "https://www.frankston.vic.gov.au/My-Property/Rates/Admin-and-certificates/Land-Information-Certificate",
            "Page_title": "Land Information Certificate",
            "Sync with KMS\n(Y/N)": " ",
            "Reviewdate": "2023-05-30T00:00:00.000Z",
            "New_publish_date\n": "",
            "Business Unit\n": "Rates & Revenue",
            "Stakeholder": "",
            "Other\nStakeholders / Areas ": "",
            "Date page first went live / published ": "",
            "Lifecycle stage\nNew\nContent rewrite project\nMajor review \nArchive ": "",
            "Notes\n": "",
            "Hybrid - SME has CMS access": ""
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "65dcc36e-f1bd-49b0-80b9-c5b7b09406a3",
            "L1": "",
            "L2": "",
            "L3": "",
            "L4": "Change of mailing address",
            "L5": "",
            "L6": "",
            "URL": "https://www.frankston.vic.gov.au/My-Property/Rates/Admin-and-certificates/Change-of-mailing-address",
            "Page_title": "Change of mailing address",
            "Sync with KMS\n(Y/N)": " ",
            "Reviewdate": "2023-05-30T00:00:00.000Z",
            "New_publish_date\n": "",
            "Business Unit\n": "Rates & Revenue",
            "Stakeholder": "",
            "Other\nStakeholders / Areas ": "",
            "Date page first went live / published ": "",
            "Lifecycle stage\nNew\nContent rewrite project\nMajor review \nArchive ": "",
            "Notes\n": "",
            "Hybrid - SME has CMS access": ""
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "ecfc9f0e-859a-4b31-9684-1b9a2b39fef1",
            "L1": "",
            "L2": "",
            "L3": "",
            "L4": "Letter of authorisation",
            "L5": "",
            "L6": "",
            "URL": "https://www.frankston.vic.gov.au/My-Property/Rates/Admin-and-certificates/Letter-of-authorisation",
            "Page_title": "Letter of authorisation",
            "Sync with KMS\n(Y/N)": " ",
            "Reviewdate": "2023-05-30T00:00:00.000Z",
            "New_publish_date\n": "",
            "Business Unit\n": "Rates & Revenue",
            "Stakeholder": "",
            "Other\nStakeholders / Areas ": "",
            "Date page first went live / published ": "",
            "Lifecycle stage\nNew\nContent rewrite project\nMajor review \nArchive ": "",
            "Notes\n": "",
            "Hybrid - SME has CMS access": ""
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "266342de-9aa1-47e8-977d-5a0596681a2c",
            "L1": "",
            "L2": "",
            "L3": "",
            "L4": "",
            "L5": "Change to Letter of Authorisation form - Managing Agent",
            "L6": "",
            "URL": "https://www.frankston.vic.gov.au/My-Property/Rates/Admin-and-certificates/Letter-of-authorisation/Change-to-Letter-of-Authorisation-form-Managing-Agent",
            "Page_title": "Change to Letter of Authorisation form - Managing Agent",
            "Sync with KMS\n(Y/N)": " ",
            "Reviewdate": "2023-05-30T00:00:00.000Z",
            "New_publish_date\n": "",
            "Business Unit\n": "Rates & Revenue",
            "Stakeholder": "",
            "Other\nStakeholders / Areas ": "",
            "Date page first went live / published ": "",
            "Lifecycle stage\nNew\nContent rewrite project\nMajor review \nArchive ": "",
            "Notes\n": "",
            "Hybrid - SME has CMS access": ""
        },
        {
            "@odata.etag": "",
            "ItemInternalId": "db689a70-2328-4e3b-95fa-9c18bb6247ae",
            "L1": "",
            "L2": "",
            "L3": "",
            "L4": "",
            "L5": "Change to Letter of Authorisation form - Property Owner",
            "L6": "",
            "URL": "https://www.frankston.vic.gov.au/My-Property/Rates/Admin-and-certificates/Letter-of-authorisation/Change-to-Letter-of-Authorisation-form-Property-Owner",
            "Page_title": "Change to Letter of Authorisation form - Property Owner",
            "Sync with KMS\n(Y/N)": " ",
            "Reviewdate": "2023-05-30T00:00:00.000Z",
            "New_publish_date\n": "",
            "Business Unit\n": "Rates & Revenue",
            "Stakeholder": "",
            "Other\nStakeholders / Areas ": "",
            "Date page first went live / published ": "",
            "Lifecycle stage\nNew\nContent rewrite project\nMajor review \nArchive ": "",
            "Notes\n": "",
            "Hybrid - SME has CMS access": ""
        }
    ],

And then the output shows data that is then even more different to what is put into the email:

For context, I am trying to set up a web page review schedule. We have a list of all pages in an Excel file, with review dates and other information. When a review date in the Excel file is today's date, I need to have an automated email sent to a shared inbox for content officers to action. I only want an email sent on days when there is data in the review date column matching today's date

As always, all your help is very appreciated

Cheers, 

[CFernandes]

@justindee - Not sure why - It seems to be looping.. Do you have HTML table action inside the Loop?

[justindee · Accepted solution]

Hi @CFernandes, I managed to figure out the flow to work for my scenario which is great. Flow below for anyone who might come across this:

Will mark this reply as the solution. 

Thanks again for all your assistance, greatly appreciated.