Bulk Email Sender

The flow allows the user to send bulk emails with a dynamic html template. With the addition of a 2 email test run to validate before approval to send all emails 

Setup

1. To run the flow you will require: OneDrive for Business, Shared Outlook Mailbox, Excel Online (with Office Script) - though the flow could be edited to work withoot OneDrive for Business or Shared Outlook mailbox
2. Unzup attached file and Import the BulkEmailerwithOfficeScript.zip file into your power automate environment
3. Download and save the 'Bulk Email Conversion.ost' to your OneDrive/Documents/Office Scripts' folder
4. Download the 2 example files 
5. Edit the flow and ensure the OneDrive route directory is selected and the Office Script

    

   
6. Setup your list of email address, parameters/variables and your html template. The flow will take each header from the table and use it to replace that value in the template e.g below the {Name} header will replace {Name} with Dan in the template

    

   

The Flow completes the followin steps

• Creates a copy of the excel list file on your one drive to ruin the script against
• Runs the 'Bulk Email Conversion' script to pivot the table into a row per variable to replace and gives each email a integer ID

function main(workbook: ExcelScript.Workbook) {
	let wb1 = workbook.getWorksheet("Sheet1");
	let wb2 = workbook.addWorksheet();
	let i = 1;
	let r=1;
	let c=3;
	let rowCount = wb1.getUsedRange().getRowCount();
	let colCount = wb1.getUsedRange().getColumnCount();
	console.log(rowCount+" "+colCount);
	let headersMain =[["Email","Subject"]]
	wb1.getRange("A1:B1").setValue(headersMain);
	let headers=[["ID","Email","Change","With"]]
	wb2.getRange("A1:D1").setValue(headers);

	for (r=1;r <rowCount;r++){

		for(c=2;c<colCount;c++){
		
			wb2.getCell(i, 0).setValue(r);
			wb2.getCell(i, 1).setValue(wb1.getCell(r, 0).getValue());
			wb2.getCell(i, 2).setValue(wb1.getCell(0, c).getValue());
			wb2.getCell(i, 3).setValue(wb1.getCell(r, c).getValue());
			i++;
		}
	}
	wb1.getRange("A:A").insert(ExcelScript.InsertShiftDirection.right);
	wb1.getRange("A1").setValue("mainID");
	wb1.getRange("A2").setFormulaLocal("=row(A1)");
	wb1.getRange("A2").autoFill();

	let table1 = workbook.addTable(wb1.getRange("A1:F"+rowCount), true);
	table1.setName("mainTable");
	let table2 =workbook.addTable(wb2.getRange("A1:D" + i), true);
	table2.setName("PivotTable");
	wb2.setName("Pivot");

	return(rowCount-1);
}

• Gets the pivoted table rows
• The rows are then filtered by id using sequential integer, with the 'Do Until' only doing the first 2 emails (Preview) and changing send to the user who triggered the flow
• The flow then loops over the filtered rows and carries out a find and replace on the html template

   

  

replace(variables('sEmailbody'), items('Apply_to_each_3')?['Change'], items('Apply_to_each_3')?['with'])​

•  Sends 2 test emails and then an approval request 
• If sample is correct the emails are approved the the flow will repeat preview stage but send all emails and to the listed email address