Using excel list to create multiple emails

[DavidAC]

Hi,
I have a list of 7000 email addresses in excel. We can only send out to 500 users at a time and it’s time consuming to create multiple emails.
is there a way I can create multiple emails and populate them with batches of 500 email addresses from the excel spreadsheet?

 

[Crystalyzer]

Not sure what version of Excel you are using, but if 365 then you could use the following formula to accumulate 500 email addresses in a single cell in a helper column

=TEXTJOIN("; ",TRUE,OFFSET($A$2,((ROW(A2)-2)*5),0,5,1)) change "5" in this formula to 500 and also adjust the cell references to fit your data

and copy it down 7,000 - 500 = 14 rows. Then you could write code to loop through this list and send emails to the recipients in each cell

Book3
	A	B	C
1	Email address
2	1		1; 2; 3; 4; 5
3	2		6; 7; 8; 9; 10
4	3		11; 12; 13; 14; 15
5	4		16; 17; 18; 19; 20
6	5		21; 22; 23; 24
7	6
8	7
9	8
10	9
11	10
12	11
13	12
14	13
15	14
16	15
17	16
18	17
19	18
20	19
21	20
22	21
23	22
24	23
25	24
Sheet1

Cell Formulas
Range	Formula
C2:C25	C2	=TEXTJOIN("; ",TRUE,OFFSET($A$2,((ROW(A2)-2)*5),0,5,1))