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 | 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:
$DataSource – Path 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
Hello! Thanks for posting this. It works wonders 🙂
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?
Author
Absolutely! You just need to modify the lines Mail.Body to Mail.HTMLBody. I’ll Add a quick example in the article
Can I password protect the attachment?
Author
If you password protect the office document or put it in a password protected zip file.