Help! Sending an email to a list of people in a spreadsheet.

[dayuaryanthi]

Hi there, 

I'm trying to build a flow that sends a registration reminder email to people who are not registered for an event yet. This will need to be a bi-weekly [every two weeks] email. Basically, I need the flow to navigate to a dynamic Excel file in SharePoint and send an email to people if they have "no" listed in column D of the file. The email address for that person is in column C. 

Here's the desired behavior as an example. 

• The flow sends a registration reminder email to Nora Klein but does NOT send one to Bob Smith. 

This is what I have so far... 

I'm a novice at this and would appreciate any guidance you have on how to build this out!

Thank you, 

Dayu

[annajhaveri]

@dayuaryanthi , how you want the flow to trigger automatically, or you want user to start the flow to send the reminders?

To get the rows from excel where Registered is no, you can use "List rows present in a table" with filter query like below to get the unregistered items. Please note if you plan to use the filter query, then please remove the spaces in column names in excel, as filter query does not allow that, you can put underscore in place of space.

Registered eq 'No'

Then you can loop through the rows using apply to each action and add Send Email action in the Apply to each action to send emails.

[dayuaryanthi]

Hi there! I want it to be triggered by a recurring flow. The below is what I have built out so far, the problem I'm experiencing now is that the registration reminder email sends to my email and not Nora Klein's.. Any insight into how to fix this? 

Part 1:

Part 2:

Part 3

 

 

[annajhaveri]

@dayuaryanthi , i see that you have update Get a Row action to get data from Excel, i would suggest to use "List rows present in a table" action to get all rows where Registered for Immersion is no, see below action:

[dayuaryanthi]

Hi Annajhaveri, 

I tried this and now I'm getting the following error. Also just btw, the step "get a row" worked but it only picks up the email in the first row.

[annajhaveri]

@dayuaryanthi You need to replace Message_Text with your column name. Message_text is the column in my excel spreadsheet. Same way i think the column name on which you want to filter is Registered for Immersion, so please do following:

1. You need to change the column name in excel spreadsheet from Registered for Immersion to Registered_for_Immersion (why you need to do this is because Filter query does not support column name with spaces)

2. After changing the column name in spreadsheet, edit the flow and change the filter query to use following filter query

Registered_for_Immersion eq 'No'

[dayuaryanthi]

Thank you, Anna. It seems to be working now. I would like to add an additional filter but I understand that the filter query field does not support more than one operation. Do you have guidance on how I might go about filtering on another field as well? I only want the email sent to people who have not registered and were already invited to a kickoff call. In the example below, only Dayu Aryanthi would be sent an email. 

And here's is what my action looks like now, with the additional filter that is causing the error ["AND (Kickoff_call_invite eq '11-Feb']:

[annajhaveri]

@dayuaryanthi  use only one filter in the List rows present in table, and for second filter that is Kickoff call invite, add Filter Array action, Select the value (list of items) from dynamic content of List rows action in the filter array, then select the kick off call column on left of condition, and provide the 11-Feb value on right, sample shown below:

Then in apply to each action use the output of Filter Array action i.e. is Body from dynamic content.

[dayuaryanthi]

Hi Anna - Thank you! 

A few other questions: 

Power Automate is reading my Date column from Excel as text and in my email flow, it lists the serial number instead of date [example below]. How can make the dates show up as dates formatted in Excel and not serial numbers?