This is an interesting journey on taking a HTML email and dynamically putting entries from a CSV file into that email before sending it to recipients. Obviously in this case the requirement was an NDR style email, but you could quite easily adapt this for any case where you need to add attributes into an email based on a CSV file and send them to customers.
The Challenge
I needed to create a system that would read email flow data from a CSV file and generate Non-Delivery Report (NDR) emails that looked exactly like the ones produced by Microsoft Exchange. The twist was that these emails needed to be sent to the recipients rather than the senders, and each email had to be dynamically populated with data from the CSV.
Visual Result
This is the visual result of the mail, where the dynamics fields are added to the email from the CSV.
This was then driven from the CSV file as you can see below:
Starting with the HTML Template
The first step was creating an accurate HTML template that matched the original NDR format. I started with a basic structure and refined it using the actual HTML source from Microsoft's NDR emails:
<p>Your message to <a href="mailto:$RecipientAddress" class="email-link">$RecipientAddress</a> couldn't be delivered.</p>
<p style="text-align: center; margin: 30px 0;">
Your message was not sent because the message has a blank subject field. Please insert a descriptive subject and try again.
</p>
The key was getting the footer table structure right, which included the colored status bars:
<table class="footer-table">
<tr>
<td class="name-cell">$senderName</td>
<td class="office-cell">Office 365</td>
<td class="gmail-cell">$(Get-EmailDomain -EmailAddress $RecipientAddress)</td>
</tr>
<tr>
<td colspan="3" class="bar-container">
<table class="color-bar-table">
<tr>
<td class="gray-bar"></td>
<td class="white-spacer"></td>
<td class="gray-bar"></td>
<td class="white-spacer"></td>
<td class="red-bar"></td>
</tr>
</table>
</td>
</tr>
</table>
CSV Data Structure
My CSV file contained the essential fields needed for each NDR:
Received,SenderAddress,RecipientAddress,Subject
"2025-07-17T06:17:35.827Z","lee@bythepowerofgreyskull.com","leecroucher999@gmail.com","Well hello there....."
The Script Logic
Lets go though the logic and functions of the script in a step by step process.
Date Formatting Function
function Format-DisplayDate {
param([string]$DateString)
try {
$date = [DateTime]::Parse($DateString)
return $date.ToString("M/d/yyyy h:mm:ss tt")
}
catch {
return $DateString
}
}
Email Domain Extraction
function Get-EmailDomain {
param([string]$EmailAddress)
if ($EmailAddress -match "@(.+)$") {
return $matches[1]
}
return ""
}
Dynamic HTML Generation
The core function builds the complete HTML email by substituting variables:
function Create-NDREmailBody {
param(
[string]$RecipientAddress,
[string]$SenderAddress,
[string]$Subject,
[string]$FormattedDate
)
$senderDomain = Get-EmailDomain -EmailAddress $SenderAddress
$senderName = ($SenderAddress -split "@")[0]
$htmlBody = @"
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Email Delivery Failure</title>
<style>
body {
font-family: Segoe UI, Tahoma, Geneva, Verdana, sans-serif;
background-color: white;
margin: 0;
padding: 20px;
color: #333;
font-size: 14px;
line-height: 1.4;
}
.container {
max-width: 600px;
margin: 0 auto;
}
.email-link {
color: #0066cc;
text-decoration: none;
}
.footer-table {
width: 548px;
border-spacing: 0;
border-collapse: collapse;
margin-top: 30px;
font-family: 'Segoe UI', Frutiger, Arial, sans-serif;
}
.footer-table td {
padding: 0;
vertical-align: bottom;
}
.name-cell {
font-size: 15px;
font-weight: 600;
text-align: left;
width: 181px;
color: #000000;
}
.status-red {
background-color: #d32f2f;
color: white;
padding: 2px 6px;
font-size: 11px;
border-radius: 3px;
font-weight: normal;
}
.gray-bar {
background-color: #cccccc;
width: 180px;
height: 10px;
line-height: 10px;
font-size: 6px;
padding: 0;
}
.red-bar {
background-color: #c00000;
width: 180px;
height: 10px;
line-height: 10px;
font-size: 6px;
padding: 0;
}
.detail-row {
margin-bottom: 5px;
font-size: 14px;
}
.detail-row strong {
font-weight: bold;
}
</style>
</head>
<body>
<div class="container">
<p>Your message to <a href="mailto:$RecipientAddress" class="email-link">$RecipientAddress</a> couldn't be delivered.</p>
<p style="text-align: center; margin: 30px 0;">
Your message was not sent because the message has a blank subject field. Please insert a descriptive subject and try again.
</p>
<div style="margin-top: 30px;">
<h3>Original Message Details</h3>
<div class="detail-row">
<strong>Created Date:</strong> $FormattedDate
</div>
<div class="detail-row">
<strong>Sender Address:</strong> $SenderAddress
</div>
<div class="detail-row">
<strong>Recipient Address:</strong> $RecipientAddress
</div>
<div class="detail-row">
<strong>Subject:</strong> $Subject
</div>
</div>
</div>
</body>
</html>
"@
return $htmlBody
}
Processing the CSV Data
The main execution loop reads the CSV and processes each record:
# Read CSV file
$csvData = Import-Csv -Path $CsvFilePath
# Process each record
foreach ($record in $csvData) {
# Format the date
$formattedDate = Format-DisplayDate -DateString $record.Received
# Create email body with dynamic data
$emailBody = Create-NDREmailBody -RecipientAddress $record.RecipientAddress -SenderAddress $record.SenderAddress -Subject $record.Subject -FormattedDate $formattedDate
# Configure and send email
$mailMessage = @{
From = $FromAddress
To = $record.RecipientAddress
Subject = "Blocked message notification: $($record.Subject)"
Body = $emailBody
BodyAsHtml = $true
SmtpServer = $SmtpServer
Port = $SmtpPort
}
Send-MailMessage @mailMessage
}
Adapting for Other Values
The same approach works for any scenario where you need to send personalized HTML emails based on CSV data. You could easily modify this for:
- Customer welcome emails
- Invoice notifications
- Event reminders
- Status updates
The pattern is always the same:
- Create your HTML template with variable placeholders
- Define your CSV structure with the required fields
- Build functions to process and format the data
- Loop through the CSV records and generate personalized emails
For example, for a welcome email, you might change the template to:
<h1>Welcome $CustomerName!</h1>
<p>Thank you for signing up on $SignupDate</p>
<p>Your account ID is: $AccountID</p>
And your CSV would contain:
CustomerName,SignupDate,AccountID,EmailAddress
"John Smith","2025-07-18","ACC-12345","john.smith@email.com"
Script : Send-NDREmails.ps1
This is the complete script excluding the HTML aspect as that is custom your requirements
# PowerShell script to send dynamic NDR emails based on CSV data
param(
[Parameter(Mandatory=$true)]
[string]$SmtpServer,
[Parameter(Mandatory=$false)]
[int]$SmtpPort = 25,
[Parameter(Mandatory=$false)]
[string]$CsvFilePath = "flowdata.csv",
[Parameter(Mandatory=$false)]
[string]$FromAddress = "postmaster@yourdomain.com"
)
# Function to format date for display
function Format-DisplayDate {
param([string]$DateString)
try {
$date = [DateTime]::Parse($DateString)
return $date.ToString("M/d/yyyy h:mm:ss tt")
}
catch {
return $DateString
}
}
# Function to extract domain from email address
function Get-EmailDomain {
param([string]$EmailAddress)
if ($EmailAddress -match "@(.+)$") {
return $matches[1]
}
return ""
}
# Function to create HTML email body
function Create-NDREmailBody {
param(
[string]$RecipientAddress,
[string]$SenderAddress,
[string]$Subject,
[string]$FormattedDate
)
$senderDomain = Get-EmailDomain -EmailAddress $SenderAddress
$senderName = ($SenderAddress -split "@")[0]
$htmlBody = @"
<html-goes-here>
"@
return $htmlBody
}
# Main script execution
try {
# Check if CSV file exists
if (-not (Test-Path $CsvFilePath)) {
Write-Error "CSV file not found: $CsvFilePath"
exit 1
}
# Read CSV file
Write-Host "Reading CSV file: $CsvFilePath" -ForegroundColor Green
$csvData = Import-Csv -Path $CsvFilePath
if ($csvData.Count -eq 0) {
Write-Warning "No data found in CSV file"
exit 1
}
Write-Host "Found $($csvData.Count) record(s) to process" -ForegroundColor Green
# Process each record in the CSV
foreach ($record in $csvData) {
try {
Write-Host "Processing record for: $($record.RecipientAddress)" -ForegroundColor Yellow
# Format the date
$formattedDate = Format-DisplayDate -DateString $record.Received
# Create email body
$emailBody = Create-NDREmailBody -RecipientAddress $record.RecipientAddress -SenderAddress $record.SenderAddress -Subject $record.Subject -FormattedDate $formattedDate
# Create email message
$mailMessage = @{
From = $FromAddress
To = $record.RecipientAddress
Subject = "Blocked message notification: $($record.Subject)"
Body = $emailBody
BodyAsHtml = $true
SmtpServer = $SmtpServer
Port = $SmtpPort
}
# Send email
Send-MailMessage @mailMessage
Write-Host "✓ NDR email sent successfully to: $($record.RecipientAddress)" -ForegroundColor Green
}
catch {
Write-Error "Failed to send email for record $($record.SenderAddress): $($_.Exception.Message)"
}
}
Write-Host "NDR email processing completed!" -ForegroundColor Green
}
catch {
Write-Error "Script execution failed: $($_.Exception.Message)"
exit 1
}
Usage Examples
Basic usage with required SMTP server:
.\Send-NDREmails.ps1 -SmtpServer "mail.yourdomain.com"
With custom port and CSV file path:
.\Send-NDREmails.ps1 -SmtpServer "mail.yourdomain.com" -SmtpPort 587 -CsvFilePath "flowdata.csv"
With custom from address:
.\Send-NDREmails.ps1 -SmtpServer "mail.yourdomain.com" -FromAddress "noreply@bythepowerofgreyskull.com"
The script reads the CSV file, processes each record, and sends a personalized HTML email to each recipient with their specific data embedded in the message. Each email looks professional and contains the exact information from the CSV record that corresponds to that recipient.