I was recently working with a client who needed to send out an email from Office 365 that was built from various fields in a CSV file to recipients also held in the CSV. A good example would be a class list where you have the students’ details, along with their course name and grade stored in a table similar to the below:
FirstName | Surname | EmailAddress | Course | Grade |
John | Smith | john.smith@outlook.com | Philosophy | B- |
Jane | Smith | smithjane@outlook.com | Chemistry | A* |
In this scenario, the School would want to use a PowerShell script to send the following email:
Recipient: $EmailAddress
Subject: $FirstName $LastName – $Course Exam Result
Body:
Hello $FirstName,
We have marked your recent exam and the results are as follows:Student Name: $LastName, $FirstName
Course: $Course
Result: $GradeThank you for taking a course at our school,
The Faculty
In the above scenario you would save the path to your CSV file in the variable $csv and use the script below to send the custom email to each recipient in the file:
$csv = Import-Csv -Path $csv $Credential = Get-Credential Foreach($Message in $csv){ $Recipient = $Message.EmailAddress $FirstName = $Message.FirstName $LastName = $Message.Surname $Course = $Message.Course $Grade = $Message.Grade $Subject = "$FirstName $LastName - $Course Exam Result" Write-Host "Sending email to $FirstName $LastName" Write-Host "Email Address: $Recipient" $mailBody = @" Hello $FirstName,</br> We have marked your recent exam and the results are as follows:</br> </br> Student Name: $LastName, $FirstName</br> Course: $Course</br> Result: $Grade</br> </br> Thank you for taking a course at our school,</br> The Faculty "@ Send-MailMessage -Body $mailBody -BodyAsHtml ` -From "faculty@greatschool.com" -To $Recipient ` -Subject $subject -Encoding $([System.Text.Encoding]::UTF8) ` -Credential $Credential -SmtpServer "smtp.office365.com" -UseSSLcopy }
When you run the script it will prompt you for credentials for a user that can send as the From address, and you will then see the following output:
Sending email to John Smith Email Address: john.smith@outlook.com Sending email to Jane Smith Email Address: smithjane@outlook.com
And the emails will be sent and they’ll look like this:
If you have any questions or comments please either use the comments section below, Tweet me@MikeParker365 or via email blog@mikeparker365.co.uk.