Send an email with multiple items from a sharepoint list

[jamesbetteridge]

Hi,

I'm relatively new to MS Flow and am by no means a developer but I am keen to learn so please bear with.

I have a sharepoint list (of customer related info) which has a number of columns but the important ones in this example are the customer name, date of next call and Account Manager fields.

In short, I want to build a Flow that, only a daily or weekly basis, sends an email to a member of staff which lists the accounts that they need to call that day/week. I have built a flow which does this for the most part however it sends a seperate email for every account rather than one email for all accounts that are due a call.

I tried the usual Google/YouTube searches and have found a couple of solutions but nothing I have put in works :-S

As per my opening statement, I'm pretty new to all this so if anyone would like to take pity on a Noob then please reach out 🙂

[Pstork1]

I assume that you have your send email inside an Apply to each loop.  That's why you are getting multiple emails.  The fix is to build an array of values inside the loop and then send the email once after the loop finishes.  You'll need to use the array to create an HTML table to put inside the email.

Also, you may need two loops, one to loop through each user and an inner loop to loop through all the customers for that user.  The email would then be outside the inner loop, but inside the out loop (user loop).

[jamesbetteridge]

Hi @Pstork1 

Thanks for the quick reply, really appreciated. I'm afraid it looks like you might have to dumb it down a bit for me, sorry. I have tried to combine what you suggested (I think), with what I found from Google and YouTube and have (not sure how to add an image so hopefull this link will work) Flow 1 

Basically, I have a scheduled trigger, followed by an "Initialize Variable" with a name of ListItems and a type of String. After that I have a GetItems which references the sharepoint site and list name (Customer Record). GetItems is filter by the customers status equalling "Live". This then goes to an Apply to Each which has the value of the GetItems output but then passes to a condition that checks to see if a call is due and if "Yes" has an apply to each containing the "Append To String Variable" which has the columns I want to display, then sends an email. The body of the email now contains the ListItems Variable.

What is happening is that I get an email, with every customer thats due a call listed (partial win - still need to work on the second loop you were refering to in order to have one email per account manager) but I then start receiving further emails an each subsequent email adds the contents of the list the the last email so, email one has 387 items, email two 774, email three 1161, etc., etc.

[Pstork1]

Sorry, but the link didn't work.  You can post an image by clicking on the camera icon in the editing toolbar or by adding the image as an attachment using the Choose file button below your reply.  Please post a screenshot of your flow, particular the loop and I'll try to explain where to put things.

[jamesbetteridge]

Hi, 

Please see screenshots below. I have been tinkering a little so it is not exactly as originally stated but it's not too far off. Hopefully this is now a little closer to what I need but still requires improvement. In this current state, I get 38 emails (no idea why 38) but each one has the list of 427 customer listed - which is good 🙂

[Pstork1]

1) you need to sort your Get Items by the person you want to email.  I assume that is DTNC.

2) Use an out Apply to Each Loop to process each unique value for DTNC in your Get Items.  I would do this using either an array or a list that I get using a different Get Items, but you want the loop to run once for each person.

3) Inside that loop do another Apply to Each Loop to build your string of values.  

4) When you exit the inner loop send the email and reset the string to empty.  Then let the outer loop process the next person.

[jamesbetteridge]

I am struggling at step one i'm afraid. There are fields I need to work with, Account Reference, Customer Name, Date of Next Call and Account Manager. The Account Manager is selected from a dropdown with a power app and so the sharepoint list's column is a choice field. When I try to sort by a choice field in the initial GetItems I get an error and the flow fails :-S.

I was wondering, I can create a View of the Sharepoint List that has the data I want, in the order I want. Is there a way to get Flow to reference a view rather than the list as a whole?

[Pstork1]

Views in Power Automate limit the number of columns to the ones in the view, but don't filter the number of records.  YOu have to recreate the view filter in the Get Items action.  You should be able to filter using the dropdown.  One of the things that people usually get wrong is not using the text value from the dropdown.  The dropdown provides an object, but you need to drill down to filter based on the value in the object.  Can you show the statement you are using for filtering

[jamesbetteridge]

Yeah Sure, but i've been tinkering again and think I might be getting somewhere - maybe :-S. So, this is my flow currently... 

The output of which is.....

So, I am getting the Account Reference at the beginning, but then all of the odata stuff, rather than just the Account Managers Name (at the end). Is there an easy way to tell the GetItems part to only pull back the value rather than all of the odata stuff?

As I now have the data I want all in one email, after this formatting issue, it'll just be a case of emailing each account manager their own list but i'll tackle that bridge when I get to it

[Pstork1]

Take another look at your dynamic content.  YOu are grabbing the account manager object.  There should be one that is something like 'Sage Ref. Name'.  That will have just the name in it rather than the whole object.

[jamesbetteridge]

Hi @Pstork1 

Well that was more simple than I expected it to be! Thank you! I guess next time I should look through the whole list rather than just selecting the first item that looks like what i am after lol - leasson learnt.

OK, so, thanks to you I am almost there I think. I now have a list of Customer that are due. The last two things I am still struggling to comprehend are:

1 - The "Date of Next Call" field is coming through as "2021-03-03T00:00:00Z", is there a way to make this a friendlier date?

2 - I can't get my head around how to group the list item by account manager then send an email for each person?

I appreciate I have taken up a lot of your time already but would really appreciate any further guidance you can offer.

Cheers

James

[jamesbetteridge]

OK so I have sussed the date formatting problem out so now all I need help with, is sending one email to each account manager that includes there list of calls to make!

Nearly there and thank you so much for all your help thus far!

[Pstork1]

To do one email per manager you want to put most of what you built inside an Apply to Each Loop.  Each loop would be for one account manager and then you filter the inner loop to collect information for just that account manager.

[jamesbetteridge]

OK, I will have a play with this over the next few days. My current flow (one email with all info) worked but when I added an extra field to the email (Contact Name) it now times out. I've removed the field again and it still times out so I think i'm going to have to start from a fresh flow as what I am doing surely cant be maxing out any resource limits - it's a fairly standard sort of thing they I imagine a lot of people will be doing?

[jamesbetteridge]

Would you be able to tell me exactly how to create the loop that sends an email for each account manager? I've had a look and it's beyond my skill set at the moment

[jamesbetteridge]

Hi @Pstork1 

Sorry it's been so long in replying but I am back to this project now and must confess to being a little embarrased that I still cannot get this to work. I think I am going to need more specific help. The screenshot below is where I am. This gives me one email with every "Primary Owner"'s calls. What I now want to achieve is for the flow to send each Primary Owner a list of there own. This project is soooo close to being done that its frustrating the heck out of me! 🙂

[Pstork1]

@jamesbetteridge I'm tied up on something else right now.  But give me a day or two and I'll put together a sample of what it should look like in general.