How To Send Emails To Multiple Users Using PnP PowerShell

In this article, we are going to see how to send emails to multiple users via Office 365 SMTP using PnP-PowerShell. It is a community-driven platform that enables fast development of components that define your application infrastructure and the content to some extent.

Prerequisite

Before you begin utilizing PowerShell to oversee SharePoint Online, ensure that the SharePoint Online Management Shell is installed. You can install the SharePoint Online Management Shell by downloading and running the SharePoint Online Management Shell. You only need to do this once for each computer from which you are running SharePoint Online PowerShell commands.

Connect to SharePoint site using Connect-PnPOnline cmdlet. The required parameters are,

URL

The SharePoint site URL (Eg: https://hubflysoft.sharepoint.com/sites/Hubfly).

The following code snippet helps to connect SharePoint sites.

$siteurl="https://<tenant-name>.sharepoint.com"
Connect-PnPOnline -Url $siteurl

Here, we used Excel to store the user's email details. From PnP PowerShell, we can read data from Excel. To know how to read the data from Excel using PowerShell, visit this detailed blog.

The below snapshot shows how users email details are stored in an Excel worksheet.

Create a new Excel.Application Object instance from Com interface like the below code snippet.

excelObj = New-Object -ComObject Excel.Application

Open the Excel workbook using Excel object. Now, you can access the Excel properties.

$filePath ="F:\Ravishankar\email_details.xlsx"
$workBook = $excelObj.Workbooks.Open($filePath)

Select the worksheet from the Excel workbook using Index.

$workSheet = $workBook.sheets.Item(1)

Here, we have sent an email via Office 365 SMTP and that requires an email address and password. Email can be sent from the user and to both internal and external addresses. We can select the email details from the Excel using ranges like below.

$from=”[email protected]”
$subject=”Test Email”
$body=”This is an Test Email”
$password=”****”
$range= 3
for($i=1;$i-le $range;$i++)
{
$toEmail= $workSheet.Columns.Item(1).Rows.Item($i).Text
Send-PnPMail -To $toEmail -From $from -Subject $subject -Body $body -Password $password
}

Finally, close the Excel workbook like the below snippet.

$excelObj.Workbooks.Close()

Final Code

$filePath ="F:\Ravishankar\email_details.xlsx"
# Create an Object Excel.Application using Com interface
$excelObj = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$excelObj.Visible = $false
#open WorkBook
$workBook = $excelObj.Workbooks.Open($filePath)
#Select worksheet using Index
$workSheet = $workBook.sheets.Item(1)
#Select the range
$from=”[email protected]”
$subject=”Test Email”
$body=”This is an Test Email”
$password=”****”
$range= 3
for($i=1;$i-le $range;$i++){
$toEmail= $workSheet.Columns.Item(1).Rows.Item($i).Text
Send-PnPMail -To $toEmail -From $from -Subject $subject -Body $body -Password $password
}
$excelObj.Workbooks.Close()

Thus, we have learned how to send multiple emails from Office 365 SMTP programmatically using PnP PowerShell scripting. Feel free to fill up the comment box below if you need any assistance.