Mail Merge Email via PowerShell’s Send-MailMessage

Posted byjasonpearce

Here’s a script that will help you learn how to use PowerShell to conduct a mail merge via email. While I once used Microsoft Excel, Word, and Outlook to perform this task; PowerShell makes the process better.

My script will even create an example CSV file that contains three columns: Email, First Name, Last Name. When you create your own file, it’s important to have the column headers, for PowerShell will read them and let you target their data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

# This script will perform a mail merge for email from a CSV file
# The CSV file must contain a column of email addresses, other columns with other data are optional
# Created by Jason Pearce, 2016 February
 
# ####################
# BEGIN Example CSV File (optional)
# ####################
 
# CSV Example: A CSV file that could serve as an example (maybe replace with [email protected] to test)
$CSVExample = @"
Email,FirstName,LastName
[email protected],Aaron,Anders
[email protected],Betty,Blue
[email protected],Charlie,Cook
"@
 
# CSV File: Optionally create a CSV file to play with
$CSVExample = Out-File -FilePath "C:\temp\example-csv-file.csv"
 
# ####################
# END Example CSV file (optional)
# ####################
 
# ####################
# BEGIN Variables 
# ####################
 
# From: Name and email of sender
$EmailFrom = "Your Name <[email protected]>"
 
# Reporting: Report on Success and Failure (optional)
$EmailDeliveryNotificationOption = "onSuccess, onFailure"
 
# Server: Your Exchange or SMTP server
$EmailSMTPserver = "smtp.example.com"
 
# Users: Tab-delimited list with columns named Name, Email, SamAccountName
$SourcePath = "C:\temp\example-csv-file.csv"
 
# Import: Import the comma-delimited list of users (if tab-delimited, add '-Delimiter "`t"')
$Users = Import-Csv -Path $SourcePath
 
# ####################
# END Variables
# ####################
 
# Begin Loop: Do the following with each row of the file you imported, referencing columns by their header
foreach ($User in $Users) {
 
# To: User's email address
$EmailTo = $User.Email
 
# Subject: Email subject (may merge variables)
$EmailSubject = "A personalized example for " + $User.FirstName + " " + $User.LastName + "."
 
# Body: Email body, with HTML formatting
$EmailBody = "<!DOCTYPE html PUBLIC ""-//W3C//DTD XHTML 1.0 Transitional//EN"" ""https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"">"
$EmailBody += "<html xmlns=""https://www.w3.org/1999/xhtml""><head>"
$EmailBody += "<meta http-equiv=""Content-Type"" content=""text/html; charset=UTF-8"" />"
$EmailBody += "<meta name=""viewport"" content=""width=device-width, initial-scale=1.0""/>"
$EmailBody += "<title>" + $EmailSubject + "</title>"
$EmailBody += "</head><body bgcolor=""#FFFFFF"" style=""font-family: sans-serif; color: #000000"">"
$EmailBody += "<p>Dear " + $User.FirstName + ":</p>"
$EmailBody += "<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit.</p>"
$EmailBody += "<p><ul><li>Your First Name: <strong>" + $User.FirstName + "</strong></li>"
$EmailBody += "<li>Your Last Name: <strong>" + $User.LastName + "</strong></li>"
$EmailBody += "<li>Your Email Address: <strong>" + $User.Email + "</strong></li></ul></p>"
$EmailBody += "<p>Phasellus nec sapien sit amet mi maximus venenatis.</p>"
$EmailBody += "<p>Sincerely,</p>"
$EmailBody += "<p>Your Name</p>"
$EmailBody += "</body></html>"
 
# Merge: Conduct the email merge, sending emails (remove -WhatIf)
Send-MailMessage -To $EmailTo -From $EmailFrom -Subject $EmailSubject -Body $EmailBody -BodyAsHTML -SmtpServer $EmailSmtpServer -DeliveryNotificationOption $EmailDeliveryNotificationOption
 
}
# End Loop and Script