Regular group emails with Excel updates on a certain people of the group

[Anonymous]

Hi community,

I would like to ask for a help with a following task:

I have an Excel file which rows have been updated on a weekly bases. I also have a list of email adreses to whom I do email weekly, however every week happens that the email is not relevant for some of those people. The email I want to send to those people contains all the latest rows of the Excel file (which could be chosen by a column called Date which includes the latest date = I want to only present the columns with the latest date each week, let's say in a form of a "screen shot" from the whole Excel file). I hope the task is clear enough.

What is the best way how to choose the relevant people from my list of contacts and email them only the updated rows of the Excel file document on the regular basis?

Thank you very much for your suggestions.  

[selfy100]

What format is your email list? Another Excel sheet?

Also how do you determine who is a relevant person to receive the email?

[Anonymous]

I made a list in Microsoft lists and add a column with a tick (yes/no), where I can every week untick the people who would not find the email relevant.

[selfy100 · Accepted solution]

I played around with some ideas and got this flow working, hopefully it's what you need!

Edit: I forgot to mention, make sure you hit "Advanced Options" on the List Rows from Table action and change the DateTime format to ISO 8601

If this works for you make sure to set as a solution so others can solve similar problems!

[Anonymous]

Hi @selfy100, 

I did exactly what you said but got this message as the flow failed:

The 'inputs.parameters' of workflow operation 'Send_an_email_(V2)' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'emailMessage/To' is required to be of type 'String/email'. The runtime value '"{\"xxx@yyy.com\":\"\"}"' to be converted doesn't have the expected format 'string/email'.

Could you please help me to figure out where is the mistake? Thank you very much.

[selfy100]

Sorry about that! You have to change the Select action a little.

Edit: Also, it looks like you've repeated the Get Items -> Select actions? You only need to do each of them once. 

Finally, in the Create HTML action, don't type the item()?['Title'] formula directly into the Value field, you have to click on Dynamic Content and put the formula in the Expressions field, then hit ok. It should add the formula to the Value field in a pink box like you see on my example. Also the formula should use single quotes ' not double quotes.

[Anonymous]

Thank you. The flow works now but the inserted table is not doing great with the columns including numbers. For the column which includes the date does the output presents some number such as "44936" for the "10.3.2023" an so. For the column including a number the table does not show anything at all. It does also not include the relevant rows only (those with the latest date as it can not recognize the date).

Lastly, is there any option how to make the table be looking more like a table? I mean can I see the rows and columns as in the original table in the Excel file? Thank you for your reply.

[selfy100]

As I mentioned in Message 4, you have to make sure to import dates from Excel using the ISO 8601 format

Formatting the table into something more attractive is possible, but involves some CSS coding in a compose action. Here's a blog post that might help

https://ryanmaclean365.com/2020/01/29/power-automate-html-table-styling/

[Anonymous]

I have used the ISO 8601 format but the output still looks like this:

The number (Nummer) column is blank and the date (Datum) column is not presented as a date.

This is the original Excel table:

The goal is to email retrospectively the latest rows of the table (those with the most recent date).

Thanks for any tips.