How to send email per batch

[Owizardo]

Hi there,

I currently have a large email list that can go up to 800 users. The list is pulled from a SharePoint list where I keep everything currently using the Office SendEmail to email those users.

However just found out that theres a limitation (on our exchange system) where you can only add 500 users per email.

So i am looking to see if anyone would know of a formula that could send emails by batch. 

Something like: 

Clear(Email_Batch);
If(
    CountRows(Sharepoint_Source) >500,
    Collect(Email_Batch, Some_Function_to_get_the-First_500_Entries_VIA_Sharepoint)
    Collect(Email_Batch, Some function_to_get_remaining_emails)
    ForAll(
      Email_Batch, 
      Office365.SendEmail( User.Email, "SubJect Line","Body Text", { BCC: EMAIL_LIST_FROM_BATCH })
    ,
    Office365.SendEmail( User.Email, "SubJect Line","Body Text", { BCC: Sharepoint_Source})

)

would someone know how to do this?

[Pstork1]

Could you do something with FirstN(Table, 500) and LastN(Table, length(Table) - 500) as long as you know you'll be less than 1000 that should get the records in two batches of 500 or less.

[Owizardo]

While this works in thoery if im always under 1000, what about when i start going over 1000 subscribers? 
How would I filter 3 batches of 500? Would you know?

[Pstork1 · Accepted solution]

Then I expect you would have to batch that out by layering the FirstN functions.  FirstN(FirstN(data,1000),500) or something like that.