Using PowerShell to bulk email your users

I was recently working on a migration project with a customer and volunteered to help find a solution to a challenge the Organizational Change Management (OCM) team were facing. The OCM team had been communicating with the business to keep them informed about the upcoming changes and what impact these changes would have on their day to day operations. This communication had all taken place via email and they now needed to send out a new notification to several thousand users that contained specific information that would be different for each recipient. Since this was a single-use scenario and all recipients were internal users, they were not terribly interested in investing in a third-party application or service to do this so we decided to explore other options.

Inspired by Pat Richard’s “New-WelcomeEmail.ps1” script, I figured it would be pretty easy to achieve this using a PowerShell script and .CSV input file, it works great! To illustrate this, I’ll use the fictitious example of gooseLabs, Inc who are relocating to a new office building and would like to send a notification email to all their users that contains their new desk location and phone number. The first and most important step is to ensure that you have an accurate input file. For this particular scenario, the input file looks something like this:

1

Once we have our input file created, we can use the magic of PowerShell to generate our notifications using this data. The following script imports the .CSV file and generates a simple email notification that is then sent to everyone in the list:

# Function to create report email
function SendNotification{
 $Msg = New-Object Net.Mail.MailMessage
 $Smtp = New-Object Net.Mail.SmtpClient($ExchangeServer)
 $Msg.From = $FromAddress
 $Msg.To.Add($ToAddress)
 $Msg.Subject = "Announcement: Important information about your office relocation."
 $Msg.Body = $EmailBody
 $Msg.IsBodyHTML = $true
 $Smtp.Send($Msg)
}

# Define local Exchange server info for message relay. Ensure that any servers running this script have permission to relay.
$ExchangeServer = "yourexchange.domain.com"
$FromAddress = "Office Relocation Team <relocation@domain.com>"

# Import user list and information from .CSV file
$Users = Import-Csv UserList.csv

# Send notification to each user in the list
Foreach ($User in $Users) {
 $ToAddress = $User.Email
 $Name = $User.FirstName
 $Level = $User.Level
 $DeskNum = $User.DeskNumber
 $PhoneNum = $User.PhoneNumber
 $EmailBody = @"
 <html>
 <head>
 </head>
 <body>
 <p>Dear $Name,</p>

 <p>As you know we will be relocating to our new offices at 742 Evergreen Terrace, Springfield on July 1, 2015. This email contains important information to help you get settled as quickly as possible.</p>

 <p>Your existing access card will grant you access to the new building and your desk location is as follows:</p>

 <p><strong>Level:</strong> $Level<br />
 <strong>Desk Number:</strong> $DeskNum<br />
 <strong>Phone Number:</strong> $PhoneNum</p>

 <p>Your new phone will be connected and ready for use when you arrive.</p>

 <p>If you require any assistance during the move please contact the relocation helpdesk at relocation@gooselabs.net or by calling 555-555-1234</p>

 <p>Regards,</p>

 <p>Office Relocation Team</p>
 </body>
 </html>
"@
 Write-Host "Sending notification to $Name ($ToAddress)" -ForegroundColor Yellow
 SendNotification
}

2

The resulting notification looks like this:

3

4

This method could also be used to distribute other information, like temporary passwords prior to a Cutover Exchange Migration.

Post navigation


Comments

  • Jethro

    Nice script! Thank you…

  • Chris

    Thanks!

  • Thomas

    I have a problem getting error 5.7.1. How do i implement credentials into my script? please help

  • Chris

    Hi Thomas,

    You could try adding the following:
    $Smtp.Credentials = New-Object System.Net.NetworkCredential(“username_here”,”password_here”)

    I’d also suggest checking out this post on credential encryption: https://www.cgoosen.com/2016/05/using-a-certificate-to-encrypt-credentials-in-automated-powershell-scripts-an-update/

    Cheers,

    Chris

  • gvk

    Hi Chris , Thanks for the great work .

    Can you please help me on how can I restrict below :

    Once a user who is part of this list ( ( like excel file above which you used is the output file which is generated each time a job runs ) ) receives email once a condition is met , in the next run if the same user exists how can I stop this auto generated email task to send him email again if the same condition is applied ? Kindly help . Thanks .

  • Chris

    Hello,

    The script above is really for single use and wasn’t specifically written to be scheduled but it could be easily adapted for that. Could you provide a little more detail about your use case or what you are trying to accomplish?

    Cheers,

    Chris

  • gvk

    Hi Chris ,

    Below is my code :

    $Query = “SQL Code Here”
    $cr = Invoke-Sqlcmd -ServerInstance . -Database abc -Query $Query

    foreach ($i in $cr){
    if ($i.a -eq “1” -and $1.b -eq “Yes”){

    $user = $i.user
    $a = $i.a
    $b = $i.b
    }

    Send-MailMessage -To “$user” -Cc “User1” -From “Fromuser” -Body ”

    My Email Body

    In this case ,

    for ex :
    $user = Mike , Chris , John

    if $user has $i.a -eq “1” -and $1.b -eq “Yes” has met the condition

    now the mail is triggered to them since the condition is met by those users .

    As this is a scheduled code to run every 1 hour , in the next hour how can I stop the task to generate the email to Chris, John , Mike as email is already sent to them if at all their records are still in $user .

  • Sridhar

    Hi –
    how to include attachment into the email message

  • Chris

    Hi Sridhar,

    Try this:
    $File = ‘C:\temp\file.txt’
    $Msg.Attachments.Add($File)

    Cheers,

    Chris

  • Tisoy

    Thank you.

  • Nishant

    Hi Chris,

    How can I add a Bcc to my recipients, I tried using $Msg.Bcc.Add($BccAddress) in the main function SendNotification{}
    and in the Foreach {} body I am declaring the Bcc email addres, now for each To email address it is sending 3 Bcc copy to the Bcc address, kindly help me with this.

  • Chris

    Hi Nishant,

    What happens when you add it only to the function?

    Cheers,

    Chris

  • Nishant

    Hi Chris,

    I figured it out by hard coding the Bcc address in the for loop and worked
    Foreach ($User in $Users)
    {
    $ToAddress = $User.E_ADD
    $BccAddress = “M_Testing@xyz.com”;

    Now I have a different challenge.
    I am using similar code as yours, for mass mailing the only difference is, instead of sending one to one email, I am putting all the recipients in To field at once and sending only one mail. My condition is to send the email only if the imported CSV file contains the email addresses otherwise do not send the email.

    What is happening is.. if there is no email address to import in To field then it is sending the email to CC address, I want to avoid this

    below is my code:

    function SendNotification
    {
    $Msg = New-Object Net.Mail.MailMessage
    $Smtp = New-Object Net.Mail.SmtpClient($smtpServer)
    $Msg.From = $FromAddress

    If ($To_Addresses -ne ‘ ‘)
    {
    # Send notification to each user in the list
    Foreach ($Recipient in $Recipients)
    {
    If($receiver.Length -eq 0)
    {
    $Msg.to.add($Recipient.CREATER_EMAIL_ADDRESS)
    }
    Else
    {
    $Msg.to.add($Recipient.CREATER_EMAIL_ADDRESS)

    }
    }

    #$Msg.To.Add($ToAddress)
    $a = Get-Date
    “Date: ” + $a.ToShortDateString()
    $Msg.CC.Add($CC1)
    $Msg.Subject = “TEST report on “+ $a.ToShortDateString() ;
    $file = “F:\***.htm”
    $att = new-object Net.Mail.Attachment($file)
    $Msg.Attachments.Add($att)
    $Msg.Body = $EmailBody
    $Msg.IsBodyHTML = $true
    $Smtp.Send($Msg)
    }
    else
    {exit}
    }

    # Define local Exchange server info for message relay.
    $smtpServer = “*********”;
    $FromAddress = “dhdhnjsk@fnfj.com”;
    $receiver = “”;

    # Import user list and information from .CSV file
    $Recipients = Import-Csv “F:\Userlist.csv”;
    $To_Addresses = $Recipients.CREATER_EMAIL_ADDRESS;
    $CC1 = “bsmddm@fhf.com”;

    $EmailBody = @”

    ***TEST***

    Hi User,

    TEST

    “@
    Write-Host “Email Sent Successful” -ForegroundColor Yellow
    SendNotification

  • Nishant

    Hi Chris,

    I found the solution 🙂

    below is my solution code:

    # Define local Exchange server info for message relay.
    $smtpServer = “********”;
    $FromAddress = “******”;
    $receiver = “”;
    $receiver2 = “*******”;

    # Import user list and information from .CSV file
    $Recipients = Import-Csv “F:\list.csv”;
    $To_Addresses = $Recipients.CREATER_EMAIL_ADDRESS

    If ($To_Addresses)
    {
    $Msg = New-Object Net.Mail.MailMessage
    $Smtp = New-Object Net.Mail.SmtpClient($smtpServer)
    $Msg.From = $FromAddress
    $Cc1 = “********”;

    # Send notification to each user in the list
    Foreach ($Recipient in $Recipients)
    {
    If($receiver.Length -eq 0)
    {
    $Msg.to.add($Recipient.CREATER_EMAIL_ADDRESS)
    }
    Else
    {
    $Msg.to.add($Recipient.CREATER_EMAIL_ADDRESS)
    }

    }

    $a = Get-Date
    “Date: ” + $a.ToShortDateString()
    $Msg.Subject = “report” ;
    $file = “F:\list.csv”
    $att = new-object Net.Mail.Attachment($file)
    $Msg.Attachments.Add($att)
    $Msg.Body = $EmailBody
    $Msg.IsBodyHTML = $true
    $Smtp.Send($Msg)
    }

    Else
    {
    $a = Get-Date
    “Date: ” + $a.ToShortDateString()
    $Msg = New-Object Net.Mail.MailMessage
    $Smtp = New-Object Net.Mail.SmtpClient($smtpServer)
    $Msg.From = $FromAddress
    $Msg.To.Add($receiver2)
    $Msg.Subject = “report”;
    $Msg.Body = $EmailBody_2
    $Msg.IsBodyHTML = $true
    $Smtp.Send($Msg)
    }

    $EmailBody = ”

    &#9888 AUTOMATED REPORT

    Hi Team, . Thank you.

    Regards,

    “;

    $EmailBody_2 = ”

    Hi Team, *****

    Regards,

    “;
    Write-Host “Email Sent Successful” -ForegroundColor Yellow
    **********************************************************************************************************
    Regards,
    Nishant

  • Chris

    Thanks for the info Nishant, glad you got it done!

  • Kannan

    This is Very Nice Script and Simplified my workload.Could you please let me know how to add the multiple Email address in the To and CC address location. Thanks!

  • Chris

    Thank you Kannan. To add additional addresses, just add them to the “SendNotification” function, e.g

    function SendNotification{
    $Msg = New-Object Net.Mail.MailMessage
    $Smtp = New-Object Net.Mail.SmtpClient($ExchangeServer)
    $Msg.From = $FromAddress
    $Msg.To.Add($ToAddress)
    $Msg.To.Add($ToAddress2)
    $Msg.To.Add($ToAddress3)
    $Msg.To.Add(ToAddress4@domain.com)
    $Msg.CC.Add(ccaddress@domain.com)
    $Msg.CC.Add($ccaddressvariable)
    $Msg.BCC.Add(bccaddress@domain.com)
    $Msg.BCC.Add($bccaddressvariable)
    $Msg.Subject = “Your Subject”
    $Msg.Body = $EmailBody
    $Msg.IsBodyHTML = $true
    $Smtp.Send($Msg)
    }

  • Kannan

    Thank You so much for your help!!

    🙂

  • Kannan

    I need your help. I want the Mail body content table format output details

    For your example:

    ServerName Description
    SAP File server
    Unix DC server
    Windows File server

    I have prepared the html format code but this code output servers name in the column servers details showing the single line instead of one by one details.

    $HTML’
    #Header{font-family:”Trebuchet MS”, Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
    #Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:2px 6px 1px 6px;}
    #Header th {font-size:14px;text-align:center;padding-top:5px;padding-bottom:4px;background-color:#EDC9AF;color:#0000A0;}
    #Header tr.alt td {color:#000;background-color:#EAF2D3;}

    $HTML Servers Details

    Server Name
    Description

    $($user.ServerName)
    $($user.Decription)

    $HTMl

  • Chris

    Try this:

    <table>
    <thead><tr>
    <th>Server Name</th>
    <th>Description</th>
    </tr></thead>
    <tbody>
    <tr>
    <td>$user.ServerName</td>
    <td>$user.Decription</td>
    </tr></tbody>
    </table>
    
  • Kannan

    Sorry Chris. I have already tried this method but not working.

    In the email body server names details showing the single line. I need server name will show one by one in the mail body .

  • Nishant

    Hi Chris,

    I have another request for you
    I am trying to embed image in the email body but when I receive the email the image shows up with a small box with cross mark in it, and image do not appear in the mail body, I have tried using the .png format image instead of .jpg but did not work.

    the code is pretty same as previous one now I am trying to embed inline image but it’s not happening, please help me with this 🙂

    # Defining CSV file to be displayed in the email body with table style

    $style = “”
    $style = $style + “BODY{background-color:#FFFFFF;font-family:Verdana;}”
    $style = $style + “TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;font-size:12px;}”
    $style = $style + “TH{border-width: 1px;text-align: center;padding: 2px;border-style: solid;border-color: black;background-color:#D3D3D3 }”
    $style = $style + “TD{border-width: 1px;text-align: center;padding: 2px;border-style: solid;border-color: black;background-color:#FFFFFF }”
    $style = $style + “”

    $Report = Import-Csv F:\hfjdl.csv | ConvertTo-Html -Head $style

    # Define Image source

    $Image = “F:\test\folder\IMAGES\One.jpg”
    $att1 = new-object Net.Mail.Attachment($Image)
    $att1.ContentType.MediaType = “image/jpg”
    $att1.ContentId = “Attachment”
    $att1.ContentDisposition.Inline = $true
    $att1.ContentDisposition.DispositionType = “Inline”

    # Define local Exchange server info for message relay.

    [string] $smtpServer = “*******.COM”;
    [string] $sender = “******.com”;
    [string] $receiver = “********.com”;
    [string] $subject = “REPORT:”;
    [string] $Else_subject= “REPORT”;
    [string] $body = ”

    &#9888 REPORT:

    Date:”+ (Get-Date).ToString(‘dd/MM/yyyy hh:mm:ss’) + ” GMT

    Dear Team, This is an automated report to acknowledge you that…….

    Please find below screenshot for your reference

    Regards,XYZ

    “;

    [string] $Else_Body = ”

    REPORT

    Date:”+ (Get-Date).ToString(‘dd/MM/yyyy hh:mm:ss’) + ” GMT

    Dear Team, This is to let you know that as…….

    Regards,XYZ

    “;

    # Attachment definition

    $file = “F:\test\folder\REPORT.csv”;

    # Defining condition when to send email to the recipient

    $data = Import-Csv “F:\test\folder\REPORT.csv”
    $NAME = $data.NAME

    if ($NAME -ne”)
    { $smtpClient = New-Object Net.Mail.SmtpClient($smtpServer);
    $emailFrom = New-Object Net.Mail.MailAddress $sender, $sender;
    $emailTo = New-Object Net.Mail.MailAddress $receiver, $receiver;
    $att = new-object Net.Mail.Attachment($file);
    $mailMsg = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $subject, $body);
    $mailMsg.Attachments.Add($att)
    $mailMsg.IsBodyHtml = $true;
    $smtpClient.Send($mailMsg)
    $att1.dispose()

    Write-Host “Mail sent successfully”;
    Write-Host “Finished”; }
    else
    {$smtpClient = New-Object Net.Mail.SmtpClient($smtpServer);
    $emailFrom = New-Object Net.Mail.MailAddress $sender, $sender;
    $emailTo = New-Object Net.Mail.MailAddress $receiver, $receiver;
    $Else_mailMsg = New-Object Net.Mail.MailMessage($emailFrom, $emailTo, $Else_subject, $Else_Body);
    $Else_mailMsg.IsBodyHtml = $true;
    $smtpClient.Send($Else_mailMsg)
    Write-Host “Mail sent successfully”;
    Write-Host “Finished”;}

  • Chris

    Hi Kennan,

    Could you post your full HTML code? Using tables in the HTML works for me, I used it all the time so I’d like to see the rest of yours.

    Thanks,

    CG

  • Chris

    Hi Nishant,

    You cannot link to a local path for the image. You need to put the image somewhere that is accessible to everyone receiving your email. If you have recipients external to your organization it should be a public url but something to consider is that Outlook is very good at blocking external images in emails.

    You could try this:

    <img src="http://path to your web server/One.jpg" alt="Image" height="20" width="200">
    

    You could also try to convert the image to base64 and include that. I haven’t personally had much success with that method.

    Cheers,

    CG

  • Kannan

    Thanks Chris. After reduced the Table width size from 100% to 10 % now all the servers is showing in the mail body table one by one.

  • Paul

    Hi Chris,

    Do you know of any solutions out there that could work for email marketing, for external contacts?

    Different users will receive different content for each email etc.

    Thanks for your time,
    Paul

  • Chris

    Hey Paul,

    I’ve never personally used any of these types of solutions, but I’ve come across a few in my time.

    Hope this helps!

  • Nishant

    Hi Chris,

    Could you help me, How I can send email from PS1 using the alias name and not the email addresses.
    if you could demonstrate this in the sample code which I posted here on January 15, 2018 at 9:02 am
    It would good for me 🙂

    Regards,
    Nishant

  • Chris

    I don’t know of a way to do that. It might be possible using the outlook com object method, but that would require outlook on the machine sending the email which makes the script a lot less useful and isn’t something I’ve tested.

  • Ilari

    How about taking data from csv1 and the email address list from csv2 ?

  • Chris

    It really depends on what your CSV columns are named, but it would be something like this:

    $CSV1 = Import-Csv csv1.csv
    $CSV2 = Import-Csv csv2.csv

    Foreach ($User in $CSV1) {
    $ToAddress = $CSV2.Email
    $Name = $CSV1.FirstName
    $Level = $CSV1.Level
    $DeskNum = $CSV1.DeskNumber
    $PhoneNum = $CSV1.PhoneNumber
    ……

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>