Some times there is a need to send merged emails to users or customers, where certain placeholders are replaced by usernames, email addresses, names etc. This blog post contains a script that can do this for you, using the Microsoft Graph. You need a regular account with Office 365 and Exchange Online for this to work.
The script is configured through the first few lines, where you can defined the subject and the HTML body. Both can contain placeholders in the format [Attribute], which is an attribute provided by the input Excel file:
Quick steps to use this script:
- Download the input excel file and save it as input.xlsx on your desktop
- Add rows to the excel file, with EmailAddress being the only required column.
- Copy the below PowerShell and edit $subject and $body, adding any placeholders you’d like. No spaces allowed in the placeholder names.
- Run the script
- Open Graph Explorer and sign in with your regular account.
- When signed in, “Edit permission” adding “Send.Mail” as an allowed permission. You will be asked to sign in again in order to consent to this new permission.
- When back at the Graph Explorer, copy the URL with the access token in it and the script will automatically pick it up and begin to send emails.
$subject = "Dear [FirstName]"
$body = "<html><body><p>Hi [FirstName],</p><p>This is [Something].</p><p>Regards</p></body></html>"
$excelInputFile = "~\Desktop\Input.xlsx"
$onlyPrintToScreen = $false
$VerbosePreference = "continue"
#
# Should not need to make changes below this line
#
if(!(Test-Path -Path $excelInputFile)) {
Write-Error "Could not find Excel file: $excelInputFile" -ErrorAction Stop
}
$rows = Import-Excel -Path $excelInputFile -ErrorAction Stop | ForEach-Object {
if($_.EmailAddress -like "*@*") {
$_
} else {
Write-Warning "Not a valid row (missing email address): $($_)"
}
}
$count = ($rows|Measure-Object).Count
if($count -eq 0) {
Write-Error "Could not find any valid rows in the Excel file" -ErrorAction Stop
} else {
Write-Verbose "Found $count rows with EmailAddress in the excel file"
}
# Function to get Graph Explorer token from clipboard
function Get-AccessTokenFromGraphExplorerUrlOnClipboard
{
[CmdletBinding()]
[Alias()]
Param
(
[Parameter(Mandatory=$false,
ValueFromPipelineByPropertyName=$true,
Position=0)]
[string[]] $RequiredScopes = @()
)
Process
{
$first = $true
do {
if(!$first) {
Start-Sleep -Seconds 1
}
$first = $false
Write-Verbose "Trying to get Graph Explorer URL from clipboard, with requires scopes: $RequiredScopes"
$url = Get-Clipboard
if(![string]::IsNullOrEmpty($url) -and $url.StartsWith("https://developer.microsoft.com/en-us/graph/graph-explorer#access_token=")) {
Write-Verbose "Found relevant url on the clipboard, starting verification"
$token = $url -split "[=&]" | Select-Object -Index 1
# Fix padding length for base 64
$token2 = $token.Split(".")[1] + [String]::new("=", 4 - ($token.Split(".")[1].Length % 4))
# Converting from json
$tokenObject = [System.Text.Encoding]::UTF8.GetString(([System.Convert]::FromBase64String($token2))) | ConvertFrom-Json
# Checking for expiry (with 5 minutes minimum required)
$date = get-date "1/1/1970"
if((Get-Date).AddMinutes(5) -gt $date.AddSeconds($tokenObject.exp ).ToLocalTime()) {
Write-Verbose "Token on clipboard is expired, not using it"
$token = ""
}
# Check scopes
if($RequiredScopes.Count -gt 0) {
$tokenScopes = $tokenObject.scp.Split(" ")
$RequiredScopes | Where-Object {$_ -notin $tokenScopes} | ForEach-Object {
Write-Verbose "Token did not contain scope $($_), not using it"
$token = ""
}
}
}
} while([string]::IsNullOrEmpty($token) -or !$token.StartsWith("ey"))
Write-Verbose "Token found"
$token
}
}
# Get the access token
$token = Get-AccessTokenFromGraphExplorerUrlOnClipboard -Verbose -RequiredScopes @("Mail.ReadWrite")
# Get all replacements from the subject and body
$Replacements = @{}
[Regex]::Matches($subject, "\[[a-zA-Z0-9]+\]") + [Regex]::Matches($body, "\[[a-zA-Z0-9]+\]") | ForEach-Object {
$Replacements[$_.Value] = $_.Value.Trim("[").Trim("]")
}
$rows | ForEach-Object {
$row = $_ # $row = $rows[0]
# Copy body and subject
$mailbody = $body
$mailsubject = $subject
# Apply replacements
$Replacements.Keys | ForEach-Object {
$mailbody = $mailbody.Replace($_, $row.$($Replacements[$_]))
$mailsubject = $mailsubject.Replace($_, $row.$($Replacements[$_]))
}
Write-Host "Sending mail to $($row.EmailAddress) with subject '$mailsubject':"
Write-Host $mailbody
Write-Host ""
if(!$onlyPrintToScreen) {
$postbody = @{
message = @{
subject = $mailsubject
body = @{
contentType = "HTML"
content = $mailbody
}
toRecipients = @(
@{emailAddress = @{address = $row.EmailAddress}}
)
}
} | ConvertTo-Json -Depth 5
Invoke-RestMethod -Uri "https://graph.microsoft.com/v1.0/me/sendMail" -Method POST -Body $postbody -ContentType "application/json" -Headers @{Authorization = "Bearer $token"}
}
}
Good Workaround! 