/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Powershell script that reads from Excel sheet sends out e-mails

06/03/2015 5119 views
Another tricky one! I did a powershell script that successfully extracted list of computers and users that are currently logged to them in a specific Domain )then output data to an Excel sheet with three columns ( Name , Computer and e-mail address) 

Now I need to send each user in spreadsheet an individual e-mail requesting an action on the machines. I have the script below

---------------------------------------------------------------------------------------------------------------------------------------
$emailFrom = me@company.com

$subject= Urgent action on *machine* 

$body = Please return  *Machine* by given time etc etc 
$emailTo= recepient@company.com
$smtpServer = smtp.company.com
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom,$emailTo,$subject,$body)
----------------------------------------------------------------------------------------------------------------------------------------

How do I modify it to perform a loop action and read through the Excel sheet and; 

1)pick out the user's e-mail address
2)add the corresponding  machine in body of the e-mail 
3)Send out e-mails one by one

Many thanks in advance!
Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
So this isn't really a K1000 question, but PS is fun anyway!

Maybe add something like this? This assumes you have a .csv that has at least these three columns: Name , Computer and e-mail.

$import=import-csv c:\yourdoc.csv
$smtpServer = smtp.company.com
$emailFrom = me@company.com
$subject= "Urgent action on " ## add machine later in the loop
$now=get-date

foreach ($person in $import)
{
$computer=$person.computer
$subject+="$computer"
$body="Please return $computer by $now.adddays(7)." # 7 days from today, for example.
$emailTo=$person.email
$smtp=new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($emailFrom,$emailTo,$subject,$body)
}

Answered 06/09/2015 by: bknorr
Purple Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ