How to write a script to send single mail to multiple users with auto-populated fields?

Hello Experts,

I have a list which contains e-mail id, name, designation and company name of the users.

I want to send an e-mail to all of them at once using a script or something else. Here is what i want to achieve-

The mail contains 3 fields which are changing every time i send a mail.
1. Mail ID filed
2. Subject
3. Name in the e-mail body.

The format will be like this -
T0 - "E-mail ID"
Subject - "Name - Designation@company name".
Mail Body -

Hello "Name"

Standard Message.

Usual way is to draft each mail by replacing the above fields and send one to one.

How do i automate this process and send mail to multiple recipients?

I need a script or any other solution which will replace above fields automatically as per the list (Excel sheet) and send mail to all of them.

Any help / inputs will be highly appreciated.

Best Regards
Raghav.
LVL 7
RaghavIT SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Thomas WheelerCommented:
you want a script? What languages are preferred?
RaghavIT SpecialistAuthor Commented:
Hi Thomas,

Thanks for your reply.

Some additional info on the problem -

I am using Gmail to send the mails. I can also use an e-mail client if needed.

I am open to any language as long as it solves the problem and is compatible with Gmail / mail clients.

Best Regards
Raghav.
Thomas Zucker-ScharffSolution GuideCommented:
You are welcome to adapt my excel to outlook VBA script. It does a lot more than you are asking but you can adjust it.

http://www.experts-exchange.com/articles/17839/Mailmerge-using-Visual-Basic-MS-Excel-2010-and-MS-Outlook-2010.html
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Thomas WheelerCommented:
here I wrote something that will do this for you you can download it here
http://wheelerwire.com/projects/powershell-emailer

it is a powershell script that reads an excel file for names and addresses then uses a textfile as a body template to compose emails. Works with gmail. Sounds like what you are looking for.
RaghavIT SpecialistAuthor Commented:
Thanks a lot.

I will check and revert today.

Best Regards
Raghav.
RaghavIT SpecialistAuthor Commented:
Hi Thomas Wheeler,

Just a quick question,

Do i need to create an excel file in a specific manner or the script just reads the fields?

Also how do i create a message body?

Thanks

Best Regards
Raghav.
Thomas WheelerCommented:
The excel file needs to have a tab named sheet1 with name in column a and email in column b . To edit the email text there is a text file named body.txt
RaghavIT SpecialistAuthor Commented:
Thanks.

So can i modify the script (and excel file) to add new fields.. Designation and Company Name?

Best Regards
Raghav.
Thomas WheelerCommented:
Yes. You can see the values in the for loop you can add columns 3 and 4 and assign the names to them. Let me know if you need help I can modify it for you
RaghavIT SpecialistAuthor Commented:
I have modified the script and tested it.

It is giving me an error - Exception calling "Open" with "1" argument(s): "'data.xlsx' could not be found. Check the spelling of the file name, and verify that the file location is correct.

I have created a folder and kept all the files in it. Do i need to save the data file somewhere else?

Best Regards
Raghav.
Thomas WheelerCommented:
you need to have the absolute path to the data file c:\data.xlsx
RaghavIT SpecialistAuthor Commented:
I tried but it didn't work. Following is the error i get -

Exception calling "Open" with "1" argument(s): "'data.xlsx' could not be found. Check the spelling of the file name,
and verify that the file location is correct.
If you are trying to open the file from your list of most recently used files, make sure that the file has not been
renamed, moved, or deleted."
At C:\Users\Raghavendra\Desktop\emailer\Emailer.ps1:20 char:1
+ $WorkBook=$objExcel.Workbooks.Open($strPath)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ComMethodTargetInvocation

You cannot call a method on a null-valued expression.
At C:\Users\Raghavendra\Desktop\emailer\Emailer.ps1:21 char:1
+ $worksheet = $workbook.sheets.item("Sheet1")
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Best Regards
Raghav.
Thomas WheelerCommented:
Here download this Download extract it to c:\Temp\PSmailer and it should work. I also added the 2 extra fields
Thomas WheelerCommented:
or you can check yours it should look like this
#
#
# Options
#

$username = 'USER@gmail.com'
$password = "GMAIL PASSWORD"
$From = "test <$username>"
$DataFile = "C:\Users\Raghavendra\Desktop\emailer\data.xlsx"

#
#

$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ($username, $secpasswd)

$strPath=$DataFile
$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$false
$WorkBook=$objExcel.Workbooks.Open($strPath)
$worksheet = $workbook.sheets.item("Sheet1")
$intRowMax =  ($worksheet.UsedRange.Rows).count


for($intRow = 1 ; $intRow -le $intRowMax ; $intRow++)
{
 $name = $worksheet.cells.item($intRow,1).value2
 $email = $worksheet.cells.item($intRow,2).value2
 $designation = $worksheet.cells.item($intRow,3).value2
 $company = $worksheet.cells.item($intRow,4).value2

 "Name $name Email $email"

 $Subject = "$name - Test Message"

 $body = Get-Content body.txt | out-string
 $body = $body -replace "###NAME###", $name
 $body = $body -replace "###EMAIL###", $email

 #Write-Host $name $email $designation $company
 send-mailmessage -to $email -from $From -subject $subject -smtpServer smtp.gmail.com -useSSL -credential $mycreds -Body $body


}
$objexcel.quit()

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RaghavIT SpecialistAuthor Commented:
It is working for me now. Thank you so much.

Wondering if i can add a CC field into it. How can i do it?

Best Regards
Raghavendra.
Thomas WheelerCommented:
you can add the -Bcc user@domain.com to the send-mailmessage command
Thomas WheelerCommented:
sorry it would be -Cc user@domain.com so
send-mailmessage -to $email -from $From -subject $subject -smtpServer smtp.gmail.com -useSSL -credential $mycreds -Body $body -Cc user@domain.com

Open in new window

RaghavIT SpecialistAuthor Commented:
Thank you so much for your script and help. It worked :-)

Best Regards
Raghav.
RaghavIT SpecialistAuthor Commented:
Hi Thomas,

I was trying to use HTML body by giving .doc or .docx file as an input (Get-Content). But i get  garbage in the mail body.

Is there any way that i can use HTML body rather than plain text?

Thank you.

Best Regards
Raghav.
Thomas WheelerCommented:
It would need to be saved as a .html file to work
RaghavIT SpecialistAuthor Commented:
I tried but it seems it is not working. I get a lot of text with html tags.

 Thank you. Any other way??

Best Regards
Raghav.
Thomas WheelerCommented:
Ohh ya you need to change the -body to -bodyashtml
RaghavIT SpecialistAuthor Commented:
Thanks a lot. It worked.

Best Regards
Raghav.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Internet / Email Software

From novice to tech pro — start learning today.