Mike Parker on 365

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:

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: $Grade

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