How to- Power Automate Bulk Email
Tags: powerautomate, powerplatform, rpa, email
Published: 2023-03-08 08:20:51

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ed3ngli2wr7z9r0hefiv.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nw1qx9lxddemzvx2qvaz.png)

## 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:
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z5ra6s7xv60x45vd8sve.png)

## 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']
)​
```

## 5. 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ndhg0m0xh0tlfr6ucaz3.png)

## 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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eg59p50mv8kr23g57fg1.png)

**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**

- [sending-limits-in-outlook-com](https://support.microsoft.com/en-us/office/sending-limits-in-outlook-com-279ee200-594c-40f0-9ec8-bb6af7735c2e)
- [exchange-online-service-description](https://learn.microsoft.com/en-us/office365/servicedescriptions/exchange-online-service-description/exchange-online-limits)
- [throttling-limits](https://learn.microsoft.com/en-us/graph/throttling-limits)