How to- Power Automate Bulk Email

Often we create Power Automate flows for specific tasks, but having reusable, dynamic flows can be incredibly powerful.

One of the most useful reusable flows I built was a bulk emailer sender.
The flow could:

Take any template
Any email specific variables
Send any quantity of emails to any address

1. Requirements

Connectors

OneDrive for Business
Shared Outlook Mailbox
Excel Online (with Office Script)

Though Shared Mailbox can easily be swapped out for personal or any other email provider

Files

Excel spreadsheet with email addresses
HTML email template

The Excel File should have the email addresses in the first column, then Subject column, then additional columns for every variable that needs to be replaced (there can be as many columns/variables as required). The variables need to be unique and wrapped in {} brackets.

e.g.

Hi {Name},
In regards to your request, reference {Ref}. The outcome is {Outcome}.
Kind regards

The HTML file should be the email body, with the matching variables within. This is then stored as a variable sEmailBody to be updated within the loops.

2. Process

3. Script

The script is used to pivot the input data and create a table of every variable for every email address. That way we just filter the list to get every variable (allowing as many variables as required). It also converts the input data into a table, so that it can be used as a parent table for the main loop.


 TypeScript
function main(workbook: ExcelScript.Workbook) {
    let tableRange = workbook.getWorksheet("Sheet1").getUsedRange().getValues();
    let rowCount = tableRange.length;
    let colCount = tableRange[0].length;
    let rowObject: child[]=[];
    let rowObject2: parent[] = [];

    for (let r=1;r<rowCount;r++){
        for(let c=2;c<colCount;c++){
            rowObject.push({
                ID: r,
                Email: tableRange[r][c].toString(),
                Change: tableRange[0][c].toString(),
                With: tableRange[r][c].toString()
            })      
        }
    }

    for(let i=1;i<rowCount;i++){
        rowObject2.push({
            mainID: i.toString(),
            Email: tableRange[i][0].toString(),
            Subject: tableRange[i][1].toString()
        })
    }

    let oTables: response= {
        parentTable: rowObject2,
        childTable: rowObject,
        rows: rowCount-1
    };
    return oTables;
}

interface child {
    ID: number,
    Email: string,
    Change: string,
    With: string
}
interface parent {
    mainID: string,
    Email: string,
    Subject: string
}
interface response{
    parentTable: parent[],
    childTable:child[],
    rows: number
}

If the the parentTable and childTable were represented in Excel they would look like below:

4. Variables

Within the forEach email address step the variable table is filtered by the id. The filtered table is then looped over and a replace expression used to replace the variable into the email body:



replace(variables('sEmailbody'), 

   items('Apply_to_each_3')?['Change']

, 

   items('Apply_to_each_Email_Variable')?['with']

)

Send the Email

The email body is then passed to the Send Email action. The ForEach step then resets the email body and then repeats for next email address.

6. Additional Steps

To improve the flow there are a couple of optional additional steps.

Test Loop
Using a DoUntil we can send the first x emails to the runner of the flow. That way they can see what the end email will look like. The flow will then send an approval/email with options that asks the runner if they wish to continue.

Add Attachment
If an attachment was needed an extra column with path to a attachment file (SharePoint or OneDrive) could be added. An extra action would then be added to get file content from that path and attach it to the send email.

Delay
Although currently the Outlook limit is 10,000 emails per day (more than enough), in the future this might change, an addition of a delay might be needed. A condition that if there are over x emails to send, delay by y seconds could be added (slowing the flow so that it never gets throttled).

Further Reading

Blog