bulk mail over several days

[MartinJ123]

Hey All,

I am looking for help setting up a bulk email sending automation from my Outlook using an Excel list. I can do this so that it sends it to the whole list.  However, if I have a list of say 4000 emails, I would like to send only 400 emails per day over say ten days I cannot figure out how to get it to only send 400 emails per day and also a different 400 emails the second day, etc.  

Basically on the Excel sheet day one, it would send lines 2-401, on day 2 it would send lines 402-802, and so on until the list is completely sent over 10 days.  Can someone help me figure it out or point me in the right direction?  Thank you so much in advance. 

[Rhiassuring]

Hi there, 

This is the solution you are looking for:

https://powerusers.microsoft.com/t5/Building-Flows/Chunking-an-array-down-to-manageable-groups/m-p/9...

And @eliotcole has done it step by step, too!

[MartinJ123]

I appreciate your help but i dont understand most of what its saying or how to even apply it. 

[Rhiassuring · Accepted solution]

Okay - now that I'm sat down and looking, I have a much easier way for you, anyway.

Here are screenshots.. overview first:

(Where I have "Manually Trigger", you'd have a scheduled daily trigger.)

First thing to know - I have a list (I am using "ExpenseSubmissions" because it's one of my demo lists..) where I am holding the value to start from in a column called "StartAt". 

You could make a whole new list for this, it will only ever have one value, and you can delete it when you're done.

I am using "Get Item" to get the "StartAt" value, using the hardcoded ID since it won't change.

Then, I am setting my 'intStart' variable with it, but I am wrapping it in an int() expression, to make sure Power AUtomate doesn't choke on it.

Then, List Rows Present from a Table - however - note that I've got the intStart variable in the "Skip Count" here. This means that every time the flow starts, it is going to skip the total amount of items we have already processed. (Ie, 400 day 1, 800 day 2, etc.) 

Next, I am initializing an array called "arrExcel".  To set it, I am using an expression called take() which let's us grab as many items as we'd like from our array. It looks like this:

take(outputs('List_rows_present_in_a_table')?['body/value'],400)

So we will only have 400 items to iterate through. 

At this point, you will want to run a test of your flow. It won't do much, but you need to copy the Output from your new arrExcel, to do the next step - which is to create a Parse JSON, and use the "Generate From Sample" button, paste in your output from arrExcel, and point content to the arrExcel variable itself. 

Now, you're going to compose your email. (I only have a "Compose" in mine as an example.) 

As I am guessing you are pretty new - the easiest way to do this is to create your email first, and then when you go to associate a piece of dynamic content (like the email address, the name, etc)  from the Parse JSON output, it's going to turn into an Apply to Each automatically - so you don't need to do it manually.

A word on this .. don't use the "Send an Email Notification" action. I think that's limited to 100 a day. Use the O365 ones.

Finally... just use "Update Item" to update the list where your intStart is held, to add 400 to it. You can do that by using the add() expression, point it at the variable, 4.

 

Let me know if you have any questions.

[MartinJ123]

Thank you so much, i will test this and let you know how i get on. I really appreciate the time and effort you put into this. 

[MartinJ123]

I know might sound silly, but this is very new to me.  If I do not use SharePoint how would i tell it to find the file before i go to the excel sheet? or would i get the excel sheet from my one drive and then do the initialize variable part?

[Rhiassuring]

Oh, it's not silly. All this stuff is super confusing when you're brand new to it or all the different pieces it connects to. 

If you want it to find it in your OneDrive, you'll find that for the source (in the "List Rows Present in Table" action) there is "OneDrive for Business" and then the Library is "OneDrive".

[MartinJ123]

for the pars JSON do i type out what you had written in that or should that appear when i click the button to generate a sample?  When i click the button i get "Insert a sample JSON Payload".

Thanks for all the help and being patient with me!

[Rhiassuring]

When  you're about to add the Parse JSON action - stop, and save the flow. Run the flow once. The "output" from your previous action (the arrExcel) - copy that output. Then, edit your flow again - add the Parse JSON - in the content field, click once, and select the previous 'Parse JSON' output.  Then - click the "Generate from Sample" button. Paste in what you had just copied from the output of your previous run. Click the button (I can't think of what it says right now..) and then save the action like that.  It does it for you, based on your output -- it will write the "format" out for you.

[MartinJ123]

So I got it to run successfully, i copied the output text and went back into the edit area. I added the Parse JSON and added what I believe is the Previous output into the content field (aarExcel). but i still get the same message. i tried pasting the copied text into the field and nothing. I must be missing something.