Simple example of sending SQL query result as HTML formatted table in an email using Powershell

I tried piecing together a couple scripts, one that send a single value to an email address and another that writes a query result to a text file, but I don't know Powershell well enough to get it to work.

I want to send the result of an SQL query of just a few columns as an HTML formatted table in an email message using Send-MailMessage.

Here is what I've got.  It prints the table to the console and the output file, but the email fails with an error.

<# connection string and SQL command to execute #>
[String] $now = Get-Date
[String] $to = 'dmegnin@careersourcebroward.com'
[String] $connectionString = 'Server=MSQLServ; Database=ITASync; Integrated Security=SSPI;'
[String] $query = @'
  SELECT Provider, LoginDate, DATEDIFF(day, LoginDate, GetDate()) as Diff
    FROM Logins
    WHERE DATEDIFF(day,LoginDate, GetDate()) IN (37)
'@


$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$command = $connection.CreateCommand()
$command.CommandText = $query

$result = $command.ExecuteReader()

$table = new-object “System.Data.DataTable”
$table.Load($result)

$format = @{Expression={$_.Provider};Label="Provider Name";width=30},@{Expression={$_.LoginDate};Label="Login Date"; width=25},@{Expression={$_.Diff};Label="Days"; width=8}

$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format

$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format | Out-File C:\Temp\ee\Providers.txt

Send-MailMessage -BodyAsHtml -Body "$table | Where-Object {$_.Provider -like "*PROFESSOR" -and $_.Diff -lt 100} | format-table $format" -SmtpServer mail.careersourcebroward.com -From "Contract Expiration Database <csbd@careersourcebroward.com>" -To $to -Subject "Provider: $($_.Provider) logged in $($_.Diff) days ago."

$connection.Close()

Select all Open in new window

If there's a cleaner or better with to do this, I'd like to learn it.  Thank you!