Answered

Ignore duplicate email addresses when sending emails

(1) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi I've built a flow that looks through my spreadsheet and sends 165 managers a table of their direct reports and what training they haven't completed yet.

This is what the Excel looks like:

Manager Email	Report	Oustanding training
manager.one@hotmail.com	Billy Bob	1, 2, 3, 4
manager.two@hotmail.com	Joe Doe	2, 6, 7
manager.one@hotmail.com	Sue Moe	1
manager.one@hotmail.com	Rob Thob	1, 2
manager.three@hotmail.com	Lazy Bones	1, 2, 3, 4, 5, 6, 7, 8

This is my Flow:

The manager receives a table like:

Report	Outstanding
Billy Bob	1, 2, 3, 4
Sue Moe	1
Rob Thob	1, 2

My issue is that "manager.one@hotmail.com" will receive 3 duplicate emails instead of just the one. How can I prevent this? I've spent over an hour trying things like Initialize Variable/Union but I just get errors for that.

I appreciate any help, I'm still a novice trying to climb onto beginner so I'd appreciate if you don't assume I know what everything means as I'm still hack-jobbing things and not entirely understanding 'why' things happen yet.

Thank you

Categories:

Building flows

I have the same question (0)

All responses (25)

Answers (1)

Sort by

Pstork1 69,556 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Sort the Excel data source by manager Email. Then use a loop to process the entries. For each loop check first to see if the manager email is equal to the one from the last loop. If it is then process the entry (but don't send the email just append it to a string or array). If its a different manager then send the info in the stored string or array to the previous manager, save the current manager as the previous manager, and process the entry. Loop and repeat.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Pstork, thank you for your answer. Sadly, I just don't understand what it means.

Are you saying I should do several apply to each? I searched 'loop' and it didn't show anything. Where and at what stage am I doing all of this? I'm a novice, no humble facades here.

Thanks for your help

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

No I'm saying do one Apply to each after you have sorted the input by manager. Then as you process each line check to see if the manager is different from the last line. If it is then send the email. If its not then store the information in some way and get the next line.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you Pstork,
"Sort input by manager" - how? - I already have it sorted in the Excel file
"Process each line" - is this a component or a condition? How, where does it go in my screenshot I've provided?
"Store the information some way" - this is a manual operation or how is this done?

I've been working on this part for over 1 hour but I've been working on this Flow for the past 6.5 hours almost non-stop. I would really appreciate a short guide or screenshot because I don't understand what you're saying stemming from my inexperience and fatigue.

Thank you again

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

1) its not sorted in the file you displayed. the first and third row are manager.one and the second row is manger.two. There is an option to sort by a column when retrieving the rows from the Excel Table.

2) I'm assuming you are processing each row in the apply to each in your flow now. I'm just explaining what conditions and actions you need to apply to each row.

3) You can either store the information by appending it to a string variable or adding it to an array.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Ok, I see. That table is just an example I made in this forum to display the duplication and data examples. It is sorted in Excel.

The apply each is sending the email

I don't understand number 3.

I don't really think I'll understand what you're saying without a demonstration, you are speaking theoretically on what I could do, but as I don't possess the knowledge to make sense of it, it's really just many words for me but limited meaning. I struggle with instructions without examples in general, especially when I am lacking basic knowledge on the matter. Even a typed flow demonstration would help me, as I just don't understand where you want me to put these, at what stage, within where and with what syntax.

The flow I have now is:
Man. trig. flow -> List rows in table -> Select -> Create HTML Table -> Compose (with HTML table styling) -> Apply to each from excel value (Send an email v2).

Thanks again Pstork and I understand if you'd rather leave it here.

Was this reply helpful? Yes No
Verified answer

Pstork1 69,556 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!

Here's a sample set of screenshots

varmanager stores the last manager processed

varTraining is an array that stores the rows as they are processed.

Apply to Each processes each row. If the varmanager is empty (first row processed) or the manager equals varmanager (last manager processed), then

Append the row to the varTraining array and update the last manager processed (this is really just to set it after the first row where varManager is blank)

If the manager doesn't equal varManager do the No side

Convert the varTraining array into a table and send the email to the varmanager. Then reset the varTraining array to the current row and varManager to the current row manager. (This is still inside the Apply to Each loop)

The Apply to each loop will now have sent emails to every manager except the last one. So when you exit the loop Create one last html table and send it to the varmanager. That will finish out the processing.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi Pstork, thank you for this detailed explanation.

In this run-through, this is the behaviour I experienced:
My email has 4 duplicates but it sent no emails, ignoring them altogether
My 2nd test inbox had 2 duplicates but sent 4 duplicate emails

I would want both inboxes to receive 1 email (there are 165 people who need to receive their individual email with varying number of reports).

I have attempted using this method: https://www.flowjoe.io/2020/12/17/remove-duplicates-from-a-power-automate-array-with-one-action/ and https://flowaltdelete.ca/2020/06/03/power-automate-expression-union-return-unique-values/ but although I manage to get it to run, the duplicates still occur.

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

I'm not sure how to help you further. I created an excel table with the output you provided in your post and my example works perfectly for me. I would go back and check your logic again. You haven't followed the example I provided.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

This is what I attempted in another method, not your method. It errors when I use the duplicate output as saying it does not find the row with the email or does not recognise the array.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I thought I did... is the last two HTML and Email in the wrong place?

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

It looks like the first condition you didn't add a blank value to the check on varManager. It still says choose a value. You need to check that against an empty string.

And in Create HTML table 3 you are processing varManager. You should be processing varTraining just like you do inside the Loop.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Sorry about that, didn't realise the microsoft forums doesn't accept full screen screenshots. I will use the snipping tool:
1.
2.

3.

4.

5.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Oh right, I misunderstood that as your screenshot also says 'choose a value' so I thought I leave it blank, how do I create the blank value - do I use 'null' in the expression? Also, why am I doing that?

I tried the null expression but my condition comes back false so nothing sends. I guess I'm trying to understand why my varManager would be blank, as I don't have any blank rows in my table.

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Just use '' in the expressions tab to get a blank string

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Great thank you. I made those two changes but the behaviour I mentioned before still occurs.

I'm not sure what else could be the issue. My Excel table layout is exactly as I mentioned, albeit the training titles are actual words separated by commas, but that doesn't change anything even if I changed them to the numbers like my example.

Thanks a lot for your patience Pstork. I will pore over your screenshots and see if I missed anything else.
I also get this error for some of them
When I look at every instance of the run, all 6 was relating to the test inbox, not once of them related to my email address or the test reports in there.

I'll keep trying. Thanks again.

I wasn't supposed to do any advanced settings in my Listrows segment right?

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Try making these changes.

1) When you initialize varManager initialize it to something like 'First'. Then in the first condition check for that instead of null.

2) Look at your SetVariable2 again. It doesn't match the text in my screenshot.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Ok, so in the value part of my Initialise Variable, I type ["First"]?

My SetVariable2 is your SetVariable - I can't see what the difference is, they both say varTraining in the Name, with:
[
{
"Report": @{items('Apply_to_each')?['DirectReport']}.
"Outstanding Training": @{items('Apply_to_each')?['RemainingTraining']}
}
]

My SetVariable3 (your 2) both says varManager and ManagerEmail?

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Did you change the condition to check for ["First"]?

Also, the screenshot for your set variable 2 doesn't match mine. Your screenshot shows

[ {"Report": @{items('Apply_to_each')?['DirectReport']}. "Outstanding Training": @{items('Apply_to_each')?['RemainingTraining']}} ]

Instead of

[ { "Report": @{items('Apply_to_each')?['Report']}, "Outstanding Training": @{items('Apply_to_each')?['Oustanding training']} } ]

whitespace makes a difference.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I don't know how to "initialize varManager initialize it to something like 'First'" - so that doesn't work.

The table columns in my Excel file doesn't have spaces, so I should add it in my Excel table so the dynamic content name has spaces as it causes errors in Flow? - I keep typoing the comma and full stop, every time I edit in there it deletes things, I need to be more vigilant with that.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Is there any way this solution can be altered to show the direct report and remaining training? The op asked the same question I have but it was never answered
https://powerusers.microsoft.com/t5/Building-Power-Apps/Need-Help-Creating-Flow-that-Will-Send-Multiple-List-Items-to/td-p/447208

I replicated it and didn't get duplicate emails but it's just populated with the managers' email address in the columns.

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

I'm not sure what spaces you are talking about. The square brackets and curly braces are what denotes the string as a JSON array. That has nothing to do with spaces or no spaces in your excel file. The names of the columns themselves are just different between your file and mine. The fact that mine has spaces doesn't matter. Its the spacing of the outer []s and {}s that is different.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks a lot Pstork, I think it works now. I didn't realise the space between the curly and square brackets would cause such an issue.

I really appreciate your perseverance with me. I will be reading up on all the features you've introduced to me today and make sure to bookmark them together so I can reference back easier.

Was this reply helpful? Yes No
dtunforgettable 6 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi how would I go about this when the file are dynamic? There are alot of videos out there but they all select a specific file thus can select the column ID.

Trigger = an excel file is placed in a sharepoint site, the name is always different but the tables are the same.
emails = exist in 2 separate columns with headers - PCS Email Contact and Mgr Email
send = send an email, merge or join all the emails in 100+ rows from those two columns so that a user gets only one email even if he appears 40 times, within the rows.
Note: Row may have one email ID, or it can have multiple IDs separated by a semi colon

Any help is appreciated.

Was this reply helpful? Yes No
Pstork1 69,556 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Please post this as a new question. You'll get more responses and others will be able to find the answer more easily later.

Was this reply helpful? Yes No