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
- 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
- Unzup attached file and Import the BulkEmailerwithOfficeScript.zip file into your power automate environment
- Download and save the 'Bulk Email Conversion.ost' to your OneDrive/Documents/Office Scripts' folder
- Download the 2 example files
- Edit the flow and ensure the OneDrive route directory is selected and the Office Script
- 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
