0

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.
0

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

0

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,email1@example.com
person2,email2@example.com

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

3
  • How would I find my SMTP server IP address? Or is that something I would need to set up? – Ababwa May 9 '19 at 20:33
  • @Ababwa Are you sending this from a company/work PC? or your own personal PC? – Lawrence May 10 '19 at 1:09
  • These will be sent from my personal PC. – Ababwa May 14 '19 at 19:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.