Mail Merge with Attachment

There is not native way to add an attachment when doing an mail merge in Microsoft Office (Outlook/Word/Excel).  However, there are 3rd party apps that allow you to add attachments when doing a mail merge, but these programs usually cost $.  If you’re like me and don’t want to spend money on an application you’ll probably use once, then I came up with an alternative free solution with Powershell.  This method works similar to a Microsoft Office mail merge because it will require a data source, email template, and an Outlook profile.

Step 1 – Create Data Source

I will use a users.csv as the data source of users we want to email.  The users.csv file will look as follows:

FirstName LastName Email Username Password
Joseph smith [email protected] jsmith VideoGame01
Bill Contoso [email protected] bcontoso LilyFlower18
Jim Rufus [email protected] jrufus StuffedAnimal23

Step 2 – Create Outlook Profile to Send Mail Merge From

Now that you have your data source, you’ll need to make sure you have an Outlook profile.  This profile should be setup with the email address you wish to send the mail merge from.

Step 3 – Create Powershell Script

In the below powershell script, you’ll need to modify the following variables:

$DataSourcePath to the data source (users.csv) file created in step 1
$AttachFile – Path to the file to attach to the email
$EmailSubject – Subject of the email

In addition to modifying the above variables, you’ll need to modify $Mail.Body, which is the body of the email.  The below example is referencing data fields in users.csv for the mail merge, which you may want to modify.  These correspond as follows:

$($_.FIRSTNAME) FIRSTNAME field in users.csv

$($_.LASTNAME) LASTNAME field in users.csv

$($_.USERNAME) USERNAME field in users.csv

$($_.PASSWORD) PASSWORD field in users.csv

Special Note:  To add a new line in the body text use a backtick + n ( `n

$DataSource = "C:\mailmerge\users.csv"
$AttachFile = "C:\mailmerge\Setup Email Directions.docx"
$EmailSubject = "How to Setup Email"

Import-CSV $DataSource | Foreach-Object {
	$ol = New-Object -comObject Outlook.Application  
	$Mail = $ol.CreateItem(0)
	$Mail.Recipients.Add($_.EMAIL)	
	$Mail.Attachments.Add($AttachFile)
	$Mail.Subject = $EmailSubject	
	$Mail.Body = "$($_.FIRSTNAME) $($_.LASTNAME),`n`n"
	$Mail.Body += "The attached directions will guide you through setting up your email account.  Your username and password are as follows:`n`n"
	$Mail.Body += "Username: $($_.USERNAME)"
	$Mail.Body += "Password: $($_.PASSWORD)`n`n"
	$Mail.Body += "If you have any issues installing, please contact support"
	$Mail.Send()
}

Extra Special Note:  If you want to have the email body be HTML formatted instead of Plain Text, just modify $Mail.Body to $Mail.HTMLBody and add your HTML tags in the text.  Using the example above:

$DataSource = "C:\mailmerge\users.csv"
$AttachFile = "C:\mailmerge\Setup Email Directions.docx"
$EmailSubject = "How to Setup Email"

Import-CSV $DataSource | Foreach-Object {
	$ol = New-Object -comObject Outlook.Application  
	$Mail = $ol.CreateItem(0)
	$Mail.Recipients.Add($_.EMAIL)	
	$Mail.Attachments.Add($AttachFile)
	$Mail.Subject = $EmailSubject	
	$Mail.HTMLBody = "<html><body>"
        $Mail.HTMLBody += "<h1>$($_.FIRSTNAME) $($_.LASTNAME),</h1>"
	$Mail.HTMLBody += "<p>The attached directions will guide you through setting up your email account.  Your username and password are as follows:</p>"
	$Mail.HTMLBody += "<p>Username: $($_.USERNAME)</p>"
	$Mail.HTMLBody += "<p>Password: $($_.PASSWORD)</p>"
	$Mail.HTMLBody += "<p>If you have any issues installing, please contact support<p></body></html>"
	$Mail.Send()
}

 

Step 4 – Send the Mail Merge

Open Microsoft Outlook with the profile created in step 2 (It is required for Outlook to be open in order for the powershell script to work!)  Then open Powershell, and run the powershell script in step 3.  You can confirm the emails are sending by looking in the “Sent Items” in Outlook.

Note: – If you’re using User Account Control (UAC), Outlook and Powershell must be running at the same security level.  This simply means, if you open powershell using “Run as administrator” you must open Outlook with “Run as administrator”.  Alternatively, if you open powershell normally (not elevated) you must open Outlook normally (not elevated).

5 comments

Skip to comment form

    • Gerry on September 19, 2015 at 8:19 am

    Hello! Thanks for posting this. It works wonders 🙂

    • Ryan on November 19, 2015 at 6:19 am

    Really good looking script, however, before I try it, can the email message be written separately as a html, then using this script send the message with the required attachment attached to the required message?

    1. Absolutely! You just need to modify the lines Mail.Body to Mail.HTMLBody. I’ll Add a quick example in the article

    • Sangeeta on March 20, 2018 at 7:46 am

    Can I password protect the attachment?

    1. If you password protect the office document or put it in a password protected zip file.

Comments have been disabled.