Send Email based on a columns in a data table

[SimonSeez]

Hello, 

If you read this, hope you are having a great. I'm trying to solve a notification problem where I email people with only details specific to them from a data table. Below is a screenshot of my flow 

and the result of my first SQL query is below 

I have set up my send email notification this way 

Currently, the recipients get the entire results of my table (QueryResult2%). What I want to achieve is for user with the email address first email address in the table to receive only the first two rows and Dinesh Kumar to receive only the last row. 

Help is needed and appreciated. 

Best Regards,

Simon

cc @tkuehara @eliotcole please would appreciate your review. I've seen you have responded to issues similar to mine 

[MichaelAnnis]

DataTables are represented with the syntax %DataTable[RowIndex][ColumnIndex]%.

So, you have a bit of work yet to get what you want.  First, lets get the body of what you to send into a table variable or a text variable.

Set Variable %LoopMax% to %QueryResult2.RowsCount% #we know how many rows the table holds

Create New List %EmailBody% #we will add to this list per email and then clear it before the next email

For Each %CurrentItem% in New Input #you already have this command

Loop starting at 0 to %LoopMax% increment of 1 #we are going to loop through each row of QueryResult2

If %QueryResult2[LoopIndex][6]% = %CurrentItem%

Add %QueryResult2[LoopIndex]% to List %EmailBody% #add the row to the email body list

#this will loop through all rows adding each row to %EmailBody% if the email matches

End Loop

Send Email #update the email with %EmailBody% instead of %QueryResult2%

Clear List %EmailBody%  #need to clear the list so it starts over for the next "For Each"

End For Each

The logic works, the only thing I am not sure on is if you can add the entire row %QueryResult2[LoopIndex]% to a list item.  It may be that you have to create a datatable instead of a list, and then write each [row][column] to the corresponding row column of your new datatable, then you could post the whole new datatable to the email.  The logic would be the same, the execution would be more difficult.

Best of luck!

[eliotcole]

If the trigger is:

1. A new SQL Db item - Then in the To field of the email action place the RM Email value from the new item. Then pick the other values accordingly for the rest of the email.
2. Scheduled Check - Get SQL items, union() the entire RM Email column as an array, twice, then Apply to each result a filter on the Get SQL items with an email and an HTML table or whatever data you want to include.

I'll try to mock something up for you later, but play with that in your head, and without getting too frustrated with the danged thing, see where you get to. You'll be surprised what you figure out.

Either way you will want SQL Item values in that email, rather than written text, where possible.

[SimonSeez]

Hello @MichaelAnnis 

Thank you for responding. 

I'm trying the suggestion but I keep getting this error which subsequently hangs my Power Automate 

Best Regards,

Simon

[tkuehara]

Hi @SimonSeez 

Well, I did a code but you'll need to adapt it (I used an Excel as a data source and Outlook to send emails). Also, it needs a review (might be inefficient... but it works):

Looping each Excel row and checking the current index: the first time you can't send email, and the last row you must send email

For the other cases in between, check if the current email is the same as the previous one; if so, then it belongs to the same "datatable subgroup"In the last run, if the emails are different (previous != current), send both the previous message and the current one; if not the last iteration but emails are the different, you just need to send the previous oneNow finally if the last two records are the same, it means you just need to send the current oneThe previous value becomes the current one at the end of each loop

It sent to each one a separate email according to the Excel data table:

Data table with values from Excel file

Please also notice I had to send the emails with a HMTL body:

Here's some more details about the data table:

Good luck.

[MichaelAnnis]

My apologies...try [5].  I don't believe the number column in your %QueryResult2% counts, and the first column "Customer Name" is actually column [0], so the "Email" column would be [5].

Best of luck!