Sending merged emails through the Microsoft Graph using PowerShell

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:

  1. Download the input excel file and save it as input.xlsx on your desktop
  2. Add rows to the excel file, with EmailAddress being the only required column.
  3. Copy the below PowerShell and edit $subject and $body, adding any placeholders you’d like. No spaces allowed in the placeholder names.
  4. Run the script
  5. Open Graph Explorer and sign in with your regular account.
  6. 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.
  7. 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"}
    }
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s