Automate mass personalized email sending from spreadsheet

Question

I have a spreadsheet with 1000+ names and email addresses, and an email template with a slot for the recipient's name. Looks something like this:

Dear [name],
...

How can I automate filling in the name slot, and sending the email for each row in my spreadsheet?

I have programming skills, so I am open to code solutions, but I assume a tool/method already exists for this sort of thing.

Some constraints:

• These need to be sent from an email which I can access via the outlook web interface.
• I would prefer that the recipients do not see the emails of the other recipients. The answer to this question explained how to achieve that, but I need an automated solution that also personalizes the email.

Answer 1

As far as I know, this could be done via Mail Merge feature of Word. Emails will be sent via Outlook and you can track sent items in Sent Items folder. If you are Exchange account, sent items will sync to the web mail.

Please check the steps in following articles: Mail merge using an Excel spreadsheet https://support.office.com/en-us/article/mail-merge-using-an-excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3

You can also refer to following threads which discussed in TechNet fourm:

Mail Merge https://social.technet.microsoft.com/Forums/office/en-US/6a56200c-d3ba-4453-85f4-b477edc72149/mail-merge?forum=word

Mail Merge https://social.technet.microsoft.com/Forums/office/en-US/5a15fb38-c7f6-46d7-b208-b38d05580005/mail-merge?forum=officeitproprevious

Answer 2

You could use Powershell to loop through the addresses and send the email, assuming you know the details of your Exchange server

If the spreadsheet is in a CSV, it's easier than if it's in an Excel file. The CSV would look something like this

name,email
person1,[e-mail]
person2,[e-mail]

And then you'd run a script similar to this one - I haven't tested this code, but it's a base to work off

$csv = import-csv \path\to\csv

foreach($person in $csv) {
    $body = "Dear $person.name, blah blah"

    Send-MailMessage -smtpserver your.server.ip.address -From 'your@email' -To $person.email -Subject 'subject' -body $body
}

That would be the easiest way without using Mail Merge since Mail Merge will require Outlook on your PC.

Reference:

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/send-mailmessage?view=powershell-6