Send Email from PowerShell with SQL Server and Email Queue¶
In previous section, I introduced how to send email from PowerShell script. In this section, I will introduce how to send HTML email to specified recipients in SQL server using PowerShell.
Sections:
Create Test Data in SQL Server¶
We need to create a table in your SQL database like this:
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Email] [nchar](255) NOT NULL,
[Age] [int] NOT NULL,
[Department] [nvarchar](255) NOT NULL
) ON [PRIMARY]
GO
Then input some data like this:
Note
Remarks: All of samples in this section are based on first section: Send email from PowerShell. To compile and run the following example codes successfully, please click here to learn how to create the test project and add reference of EASendMail to your project.
Connect SQL Server in PowerShell¶
Now we can connect SQL server by the following codes:
function OpenConnection () {
# Please change the connection string to yours. You can specify user and password like this:
# $connectionString = "Server=localhost\AdminSystem;Database=dbname;User Id=user;Password=yourpassword;MultipleActiveResultSets=True;"
# https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx
$connectionString = "Server=localhost\AdminSystem;Database=test;Integrated security=SSPI;MultipleActiveResultSets=True;"
Write-Host(("Connecting database {0} ..." -f $connectionString))
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString
$sqlConnection.Open()
Write-Host 'Connected'
return $sqlConnection
}
Read Data from SQL Server in PowerShell¶
read data like this:
$sqlConnection = OpenConnection
$sqlQuery = "SELECT ID, Name, Email, Age, Department FROM users"
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConnection);
$reader = $sqlCommand.ExecuteReader()
while ($reader.Read()) {
$name = $reader.GetString(1).Trim()
$address = $reader.GetString(2).Trim()
}
Build HTML Template¶
I also want to build a HTML body based on the table data, so I added the following codes:
function BuildHtmlBody ($sqlConnection) {
Write-Host "Building HTML body based on database ..."
$sqlQuery = "SELECT ID, Name, Email, Age, Department FROM users"
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConnection);
$reader = $sqlCommand.ExecuteReader()
$html = "<!DOCTYPE html><html><body>"
$html += "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
$html += "Dear {name}, <br /><br />This is a test email from Powershell and SQL server. <br />"
$html += "Here is full data in table:<br /><br />"
$html +="<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
while ($reader.Read()) {
$name = $reader.GetString(1)
$address = $reader.GetString(2)
$age = $reader.GetInt32(3)
$department = $reader.GetString(4)
$html += "<tr>"
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $name
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $address
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $age
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $department
$html += "</tr>"
}
$reader.Close > $null
$reader.Dispose > $null
$sqlCommand.Close > $null
$sqlCommand.Dispose > $null
return $html
}
Above codes can generate a HTML table based on current table data like this:
Send HTML from PowerShell with SQL Server Data¶
Now we combine all functions:
[reflection.assembly]::LoadFile("C:\Program Files (x86)\EASendMail\Lib\net20\EASendMail.dll")
function SendMailTo($sender, $name, $address, $subject, $body, $htmlFormat) {
$mail = New-Object EASendMail.SmtpMail("TryIt")
$mail.From.Address = $sender
$recipient = New-Object EASendMail.MailAddress($name, $address)
$mail.To.Add($recipient) > $null
$mail.Subject = $subject
if($htmlFormat) {
$mail.HtmlBody = $body
}
else {
$mail.TextBody = $body
}
# please change server, user, password to yours
$server = New-Object EASendMail.SmtpServer("mail.emailarchitect.net")
$server.User = "test@emailarchitect.net"
$server.Password = "yourpassword"
# If your 25 port is blocked by ISP, you can try to use 587 port
$server.Port = 25
# Using TryTLS,
# If smtp server supports TLS, then TLS connection is used; otherwise, normal TCP connection is used.
# https://www.emailarchitect.net/easendmail/sdk/?ct=o_smtpconnecttype
$server.ConnectType = [EASendMail.SmtpConnectType]::ConnectTryTLS
# If your server is Exchange 2007 or later version, you can use EWS protocol.
# https://www.emailarchitect.net/easendmail/sdk/?ct=o_serverprotocol
# Set Exchange Web Service Protocol - EWS - Exchange 2007/2010/2013/2016
# $server.Protocol = [EASendMail.ServerProtocol]::ConnectTryTLS
$smtp = New-Object EASendMail.SmtpClient
$smtp.SendMail($server, $mail)
}
function OpenConnection () {
# Please change the connection string to yours. You can specify user and password like this:
# $connectionString = "Server=localhost\AdminSystem;Database=dbname;User Id=user;Password=yourpassword;MultipleActiveResultSets=True;"
# https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx
$connectionString = "Server=localhost\AdminSystem;Database=test;Integrated security=SSPI;MultipleActiveResultSets=True;"
Write-Host(("Connecting database {0} ..." -f $connectionString))
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString
$sqlConnection.Open()
Write-Host 'Connected'
return $sqlConnection
}
function BuildHtmlBody ($sqlConnection) {
Write-Host "Building HTML body based on database ..."
$sqlQuery = "SELECT ID, Name, Email, Age, Department FROM users"
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConnection);
$reader = $sqlCommand.ExecuteReader()
$html = "<!DOCTYPE html><html><body>"
$html += "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
$html += "Dear {name}, <br /><br />This is a test email from Powershell and SQL server. <br />"
$html += "Here is full data in table:<br /><br />"
$html +="<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
while ($reader.Read()) {
$name = $reader.GetString(1)
$address = $reader.GetString(2)
$age = $reader.GetInt32(3)
$department = $reader.GetString(4)
$html += "<tr>"
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $name
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $address
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $age
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $department
$html += "</tr>"
}
$reader.Close > $null
$reader.Dispose > $null
$sqlCommand.Close > $null
$sqlCommand.Dispose > $null
return $html
}
function SendMailFromDB() {
try {
#Please change sender address to yours
$sender = "test@emailarchitect.net"
$subject = "Test email from Powershell and SQL server"
$sqlConnection = OpenConnection
$bodyTemplate = BuildHtmlBody($sqlConnection)
$sqlQuery = "SELECT ID, Name, Email, Age, Department FROM users"
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConnection);
$reader = $sqlCommand.ExecuteReader()
while ($reader.Read()) {
$name = $reader.GetString(1).Trim()
$address = $reader.GetString(2).Trim()
# build body based on body template
$body = $bodyTemplate.Replace("{name}", $name)
Write-Host(("Start to send email to {0} ..." -f $address))
SendMailTo $sender $name $address $subject $body "html"
Write-Host(("Email to {0} has been submitted to server!" -f $address))
}
$reader.Close > $null
$reader.Dispose > $null
$sqlCommand.Close > $null
$sqlCommand.Dispose > $null
$sqlConnection.Close > $null
$sqlConnection.Dispose > $null
}
catch [System.Exception] {
"Failed to send email: {0}" -f $_.Exception.Message
}
}
SendMailFromDB
Run the script like this:
.\SendEmailFromPowershell.ps1
You will see the status and result in PowerShell Console Output.
Send Email to Specified Recipients based on Table Data¶
If you only want to send email to specified recipients, you can add some conditions in SQL query statement. For example, you can only send email to the person in IT department.
$sqlQuery = "SELECT ID, Name, Email, Age, Department FROM users WHERE Department='IT'"
Send Email using EASendMail Service Queue¶
EASendMail Service is a light and fast email delivery service which works with EASendMail SMTP Component to enable your application to send mass emails in background queue service.
Along with its ability to picking recipients from database in background and sending email in specified datetime, it eases your task in developing featured email application such as newsletter application. We strongly recommend you to use EASendMail Service with your ASP/Web Application.
Important
To work with EASendMail Service, please download EASendMail and EASendMail Service at first, and then install both on your machine. If you are using web hosting service and you don’t have permission to install service on that server, EASendMail service is not suitable for you.
With EASendMail email queue feature, you do not have to code for multiple threadings. EASendMail Service can send email in background with multiple threadings automatically. You just need to adjust the maximum worker threads in EASendMail Service Manager to increase the performance. Please click here to learn more detail about EASendMail Service.
If your networking connection to your SMTP server is not very fast, EASendMail Service is absolutely solution for you. You just need to submit the email to EASendMail service queue, it is very fast because EASendMail service uses shared memory to accept email from EASendMail component, and then the service will send email in background service. It is very important to improve the response time for ASP.NET web application.
Now we can simply use the following codes to send email using EASendMail Service Queueu:
[reflection.assembly]::LoadFile("C:\Program Files (x86)\EASendMail\Lib\net20\EASendMail.dll")
function SendMailToQueue($sender, $name, $address, $subject, $body, $htmlFormat) {
$mail = New-Object EASendMail.SmtpMail("TryIt")
$mail.From.Address = $sender
$recipient = New-Object EASendMail.MailAddress($name, $address)
$mail.To.Add($recipient) > $null
$mail.Subject = $subject
if($htmlFormat) {
$mail.HtmlBody = $body
}
else {
$mail.TextBody = $body
}
# please change server, user, password to yours
$server = New-Object EASendMail.SmtpServer("mail.emailarchitect.net")
$server.User = "test@emailarchitect.net"
$server.Password = "yourpassword"
# If your 25 port is blocked by ISP, you can try to use 587 port
$server.Port = 25
# Using TryTLS,
# If smtp server supports TLS, then TLS connection is used; otherwise, normal TCP connection is used.
# https://www.emailarchitect.net/easendmail/sdk/?ct=o_smtpconnecttype
$server.ConnectType = [EASendMail.SmtpConnectType]::ConnectTryTLS
# If your server is Exchange 2007 or later version, you can use EWS protocol.
# https://www.emailarchitect.net/easendmail/sdk/?ct=o_serverprotocol
# Set Exchange Web Service Protocol - EWS - Exchange 2007/2010/2013/2016
# $server.Protocol = [EASendMail.ServerProtocol]::ExchangeEWS
$smtp = New-Object EASendMail.SmtpClient
# To use this method, please download and install
# https://www.emailarchitect.net/webapp/download/easendmailservice.exe
$smtp.SendMailToQueue($server, $mail)
}
function OpenConnection () {
# Please change the connection string to yours. You can specify user and password like this:
# $connectionString = "Server=localhost\AdminSystem;Database=dbname;User Id=user;Password=yourpassword;MultipleActiveResultSets=True;"
# https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx
$connectionString = "Server=localhost\AdminSystem;Database=test;Integrated security=SSPI;MultipleActiveResultSets=True;"
Write-Host(("Connecting database {0} ..." -f $connectionString))
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = $connectionString
$sqlConnection.Open()
Write-Host 'Connected'
return $sqlConnection
}
function BuildHtmlBody ($sqlConnection) {
Write-Host "Building HTML body based on database ..."
$sqlQuery = "SELECT ID, Name, Email, Age, Department FROM users"
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConnection);
$reader = $sqlCommand.ExecuteReader()
$html = "<!DOCTYPE html><html><body>"
$html += "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
$html += "Dear {name}, <br /><br />This is a test email from Powershell and SQL server. <br />"
$html += "Here is full data in table:<br /><br />"
$html +="<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"
while ($reader.Read()) {
$name = $reader.GetString(1)
$address = $reader.GetString(2)
$age = $reader.GetInt32(3)
$department = $reader.GetString(4)
$html += "<tr>"
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $name
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $address
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $age
$html += "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>{0}</td>" -f $department
$html += "</tr>"
}
$reader.Close > $null
$reader.Dispose > $null
$sqlCommand.Close > $null
$sqlCommand.Dispose > $null
return $html
}
function SendMailFromDBToQueue() {
try {
$sender = "test@emailarchitect.net"
$subject = "Test email from Powershell and SQL server"
$sqlConnection = OpenConnection
$bodyTemplate = BuildHtmlBody($sqlConnection)
$sqlQuery = "SELECT ID, Name, Email, Age, Department FROM users"
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand($sqlQuery, $sqlConnection);
$reader = $sqlCommand.ExecuteReader()
while ($reader.Read()) {
$name = $reader.GetString(1).Trim()
$address = $reader.GetString(2).Trim()
$body = $bodyTemplate.Replace("{name}", $name)
Write-Host(("Start to send email to {0} ..." -f $address))
SendMailToQueue $sender $name $address $subject $body "html"
Write-Host(("Email to {0} has been submitted to easendmail service!" -f $address))
}
$reader.Close > $null
$reader.Dispose > $null
$sqlCommand.Close > $null
$sqlCommand.Dispose > $null
$sqlConnection.Close > $null
$sqlConnection.Dispose > $null
}
catch [System.Exception] {
"Failed to send email: {0}" -f $_.Exception.Message
}
}
SendMailFromDBToQueue
Run the script like this:
.\SendEmailFromPowershell.ps1
You will see the status and result in PowerShell Console Output.
Download Sample PowerShell script¶
You can download SendEmailFromPowershell.ps1 here, all example codes in this tutorial are included.
Not enough? Please contact our technical support team.
Remarks
We usually reply emails in 24hours. The reason for getting no response is likely that your smtp server bounced our reply. In this case, please try to use another email address to contact us. Your Gmail, Hotmail email account is recommended.