Birthday email listing all birthdays in a single email

[rwittels]

There are many tutorials and templates explaining how to send out an email to someone on their birthday. We prefer to send a single email out to the company listing all people who are celebrating their birthday on that day. I found this great template: Send wishes to employees by email as per the birth date in SharePoint list but I would like to know how to list the names of all the people celebrating birthdays in the body of the email. The email would then go out to the same distribution list each day. So instead of "Happy Birthday FirstName" I would prefer, "We would like to wish the following people happy birthday today:" and then list the full names.

[manuelstgomes]

HI @rwittels 

You need to adapt a little to the strategy. Currently, the strategy is:

1. Fetch all birthdays
2. Loop all of them
3. For each, send an email.

What you need to do is the following:

1. Create a variable that is the "body" of the text
2. Fetch all birthdays
3. Loop all of them and add them to the variable
4. Outside the loop, send one email with the body that you created.

Can you please check if and let me know if you have any questions?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[rwittels]

Thank you. This makes perfect sense. I am new to Power Automate so any guidance on how to actually do this would be appreciated.

[manuelstgomes · Accepted solution]

Hi @rwittels 

Sure let's do this :).

You can start with the same template. Remove the variables from the send email and put it outside the "For Each". Then add a variable and, in the place where the "Send Email" was put, an append variable with the person's name. Where you see "<name>" you should put the value that comes from your SharePoint. the "<br>" is to have one name per line. The result is like this (2 prints because the Power Automate would not fit in the same print).

Can you please check if and let me know if you have any questions?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[rwittels]

You have been very helpful. Thank you. I will try this solution.

UPDATE: The flow doesn't work correctly. I receive an email with only the first name that matches. There should be 4 more names in the list. The other problem is that I receive the email constantly until I cancel the test. I received the mail about 30 times before I cancelled the manual test. Do I have to tell it to only send once or is it a loop issue?

Here is my flow:

[manuelstgomes]

HI @rwittels 

Can't see correctly in the print you shared, but is the "Send an email" outside the "Apply to Each"?

This is the only thing that would explain just one name and several emails.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[rwittels]

I moved the email below Append to string variable, inside Apply to each. I only receive one email now but with only one name. The flow fails with this error though: "Action failed. An action failed. No dependent actions succeeded." All components have green ticks though.

[manuelstgomes]

Hi  

No, I mean moving the "send email" entirely outside the "Apply to Each.

The reason is that you're fetching one person, appending it, and sending the email. You need to fetch one person and append (inside the apply to each) and then outside send only one email with everything.

If I have answered your question, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.

Cheers

Manuel

[rwittels]

OK. That's how I had it but the mails kept coming. I moved the email output back to where it was but now I get this "Action failed. An action failed. No dependent actions succeeded." No idea what's wrong. I am going to build the flow from scratch and see what happens.

[rwittels]

This is so strange. I have not changed anything but I keep getting "ActionFailed. An action failed. No dependent actions succeeded." on the Apply to each step. I will look up the error. I don't know what has changed.

[rwittels]

I am getting this error:

Unable to process template language expressions for action 'Condition' at line '1' and column '14693': 'In function 'formatDateTime', the value provided for date time string '13/05/1977' was not valid. The datetime string must match ISO 8601 format.'.

My sharepoint list dates are formatted as dd/mm/yyyy and the conditional expression in the flow is

formatDateTime(outputs('Compose'), 'MM-dd') is equal to formatDateTime(utcNow(),'MM-dd')

Could my problem be with the date format in my list or can I change the conditional statement to match my date format?

[rwittels]

OK. I think I resolved the date issue by applying the ISO 8601 format to my date column. The flow runs and succeeds without errors:

The big problem is that I am getting no names returned in the email. The one name that appeared to be matching the date and returning as a result was at the top of my SharePoint list. I moved it to the bottom of the list so that the top record is not a date match. The Birthdays string is empty. Does this mean that it is not looping through the list and only checking the first record in the list?

[rwittels]

I ran it again this morning. It sent a mail with one name again.There should have been 3. The name it added was the first date match for today. The name was also in the middle of the list so it is not a top of list issue. The flow is obviously not appending Titles from subsequent date matches to the Birthdays string variable.

[rwittels]

Have read a lot on this today. Still no solution though. A few articles suggest that I should be composing the Birthdays variable prior to sending the email. I tried this inside the loop and outside but nothing changed. Other articles speak about using the JOIN or CONCATENATE expressions but I think that applies more to arrays. I'm really stuck here. No idea.

[rwittels]

I have read many articles on this and it appears that appending in a loop just does not work. Unless someone reads this and has a solution, I will take it that Power Automate is the wrong platform to configure this type of flow.

[manuelstgomes · Accepted solution]

Hi @rwittels 

How many rows you have on that list? Please note that the "Get Items" only returns the first 100, so the people that have birthdays may be outside that range.

I would recommend the following:

1. Add a sort in your "Get Items" by the birthday Date. You can check the name of the field when you run in the outputs section. The expression should be "Date asc" in the "Order By". 

2. Limit to 50. I don't think you would have 50 people with the birthday the same day, and then you'll fetch less data making your Flow faster. If you think the number is lower, you can define a lower number. 

Here's what to to do:

Fetch the information from the last run:

Copy the name exactly as you find it in the text. Some lists have spaces and other characters, so it's important to use the name that you find there so that the sort will work..

Can you please check if and let me know if you have any questions?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

[rwittels]

This works! However, "Top count" is "Total number of entries to retrieve", so I set that to my list length which is 390 and the email returns all the correct names for the day. When I set Top count to 100 initially, it only emailed 2 names which were within the first 100 records on my list.

I can confirm that this now works and I have marked it as the solution. Thank you. I have learned a lot.

[manuelstgomes]

AWESOME 🎉

[almapete21]

Hi. I have been using this one and I only have one problem: it still sends me an e-mail even if no one in the SharePoint List has a birthday on that day. Is there a workaround where it will only send an email if someone in the List has a birthday? Hoping this would be noticed. 😢

[rwittels]

Hi. I solved this by checking whether the Birthdays string has any content in it (length is greater than 0). Add a condition right at the end and only send an email if the length of the Birthdays string is greater than 0.