PowerShell Display Table In HTML Email

Question

Ok, this may have been answered, but I'm new and trying to learn, so I guess I'm not "getting" it. I have a variable that contains a table of information (I got it from a SQL query). I can output the variable to the screen and see the table, but when I try to build an HTML email with it in the body, it gets garbled.

Here is the code:

# This code sets up the HTML table in a more friendly format
$style = "<style>BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px; }"
$style = $style + "TD{border: 1px solid black; padding: 5px; }"
$style = $style + "</style>"

# This code defines the search string in the IssueTrak database tables
$SQLServer = "Blah"
$SQLDBName = "Blah"
$SQLUsername = "Blah"
$SQLPassword = "Blah"
$SQLQuery = "SELECT u.FirstName,u.LastName,u.EMail,COUNT(UserID) as Count
        FROM dbo.Issues i with(nolock)
        JOIN DBO.Users u with(nolock) on u.UserID = i.NextActionBy
        where i.Status='Open'
        group by u.FirstName,u.LastName,u.EMail
        order by Count(*) desc"

# This code connects to the SQL server and retrieves the data
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; uid = $SQLUsername; pwd = $SQLPassword"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

# This code outputs the retrieved data
$DataSet.Tables | Format-Table -Auto

# This code emails a report regarding the results of the retrieved data
$smtpServer = "Blah"
$smtpFrom = "Blah"
$smtpTo = "Blah"
$messageSubject = "IssueTrak Open Issues By Next Action Report"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Body = "Here is a listing of open issues in IssueTrak, sorted by Next Action.<br><br>"
$message.Body = $message.Body + $html

$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)

The output on the screen looks like this:

35

FirstName   LastName   EMail                                Count
---------   --------   -----                                -----
John        Doe        [email protected]                       51
Jane        Doe        [email protected]                       20

...but the email body looks like this:

Here is a listing of open issues in IssueTrak, sorted by Next Action.

Microsoft.PowerShell.Commands.Internal.Format.FormatStartData Microsoft.PowerShell.Commands.Internal.Format.GroupStartData

Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData

...and so on. What am I doing wrong?

Elijah W. Gagne · Accepted Answer · 2012-06-29 14:51:48Z

17

Here's how I'd do it:

# Create a DataTable
$table = New-Object system.Data.DataTable "TestTable"
$col1 = New-Object system.Data.DataColumn Name,([string])
$col2 = New-Object system.Data.DataColumn Dept,([string])
$table.columns.add($col1)
$table.columns.add($col2)

# Add content to the DataTable
$row = $table.NewRow()
$row.Name = "John"
$row.Dept = "Physics"
$table.Rows.Add($row)
$row = $table.NewRow()
$row.Name = "Susan"
$row.Dept = "English"
$table.Rows.Add($row)

# Create an HTML version of the DataTable
$html = "<table><tr><td>Name</td><td>Dept</td></tr>"
foreach ($row in $table.Rows)
{ 
    $html += "<tr><td>" + $row[0] + "</td><td>" + $row[1] + "</td></tr>"
}
$html += "</table>"

# Send the email
$smtpserver = "smtpserver.domain.com"
$from = "[email protected]"
$to = "[email protected]"
$subject = "test"
$body = "Hi there,<br />Here is a table:<br /><br />" + $html
Send-MailMessage -smtpserver $smtpserver -from $from -to $to -subject $subject -body $body -bodyashtml

answered Jun 29, 2012 at 14:51

Elijah W. Gagne

2,8414 gold badges33 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1491315 Over a year ago

I tried that, but I'm getting errors. I shared the full code, maybe you can see what I'm doing wrong?

Elijah W. Gagne Over a year ago

OK, the first thing is get rid of: $DataSet.Tables | Format-Table -Auto because that doesn't do anything. You need to take $DataSet and get a DataTable, $table = $DataSet.Tables[0]. Once you have the DataTable use my example to manually create the $html string.

LowPolyCorgi · Accepted Answer · 2015-03-24 16:39:05Z

7

I think this is a better solution (see ref 1)

$DataSet.Tables[0] |select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | ConvertTo-Html

With the ExcludeProperty we can all exclude the noisy columns from our table without the need of specify specialy what columns we want it

(1) http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/25/reinventing-the-wheel-automating-data-consistency-checks-with-powershell.aspx

edited Mar 24, 2015 at 16:39

LowPolyCorgi

5,1852 gold badges21 silver badges38 bronze badges

answered Mar 24, 2015 at 16:12

YourPadre

1011 silver badge6 bronze badges

1 Comment

Rich Moss Over a year ago

FYI - the link is broken. Your solution is what I used for a quick email. I found that ConvertTo-Html supports aligning columns: Get-Process | ConvertTo-Html -Property Name, @{L='Handles';E={$_.Handles};A='Right';W='20'} Unfortunately Outlook and other email clients don't respect the <colgroup> syntax.

CB. · Accepted Answer · 2012-06-29 14:29:55Z

3

You can try (not tested):

$message.Body = $message.Body + ($DataSet.Tables | format-Table -auto |  convertto-html)

answered Jun 29, 2012 at 14:29

CB.

61.3k9 gold badges170 silver badges165 bronze badges

2 Comments

user1491315 Over a year ago

That did not seem to help, now all I am getting in the email is a list of GUID-like numbers...

Scott Lundberg Over a year ago

this will work, but you have select the specific columns you want in your table because the datatable object includes all sorts of properties built-in, besides your custom columns. i.e. $table | select col1,col2,colN | convertto-html

Emrah Saglam · Accepted Answer · 2018-06-28 12:40:23Z

I use like this:

Import-Module C:\dbatools\dbatools.psd1

$HostName = Get-WMIObject Win32_ComputerSystem | Select-Object -ExpandProperty name
$DiskResult = Get-DbaDiskSpace -ComputerName $HostName | Sort-Object $_.FreeInGB | Out-DbaDataTable

# Create column headers of Table1
$HtmlTable1 = "<table border='1' align='Left' cellpadding='2' cellspacing='0' style='color:black;font-family:arial,helvetica,sans-serif;text-align:left;'>
<tr style ='font-size:13px;font-weight: normal;background: #FFFFFF'>
<th align=left><b>ComputerName</b></th>
<th align=left><b>Name</b></th>
<th align=left><b>Label</b></th>
<th align=left><b>SizeInGB</b></th>
<th align=left><b>FreeInGB</b></th>
<th align=left><b>PercentFree</b></th>
</tr>"

# Insert data into Table1
foreach ($row in $DiskResult)
{ 
    $HtmlTable1 += "<tr style='font-size:13px;background-color:#FFFFFF'>
    <td>" + $row.ComputerName + "</td>
    <td>" + $row.Name + "</td>
    <td>" + $row.Label + "</td>
    <td>" + $row.SizeInGB + "</td>
    <td>" + $row.FreeInGB + "</td>
    <td>" + $row.PercentFree + "</td>
    </tr>"
}
$HtmlTable1 += "</table>"

# Send Mail Inputs
$smtpserver = "smtp.domain.com"
$from = "Powershell Mail <" + $HostName + "@domain.com>"
$to = "<[email protected]>", "<[email protected]>"
$cc = "<[email protected]>" , "<[email protected]>"
$subject = "Disk Check for SQL Server"
$body = "Disk info for SQL Server like below:<br/><br/>" + $HtmlTable1

Send-MailMessage -smtpserver $smtpserver -from $from -to $to -cc $cc -subject $subject -body $body -bodyashtml

TheGameiswar · Accepted Answer · 2018-08-26 11:22:41Z

For someone looking for a simple table,here is what i have found

add below variable first

$Header = @"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@

You can add like below then

Get-PSDrive | ConvertTo-Html -Property Name,Used,Provider,Root,
CurrentLocation 
-Head $Header | Out-File -FilePath PSDrives.html

While reading the file,ensure you use -raw as a parameter like bellow

Get-ChildItem Cert:\LocalMachine\My  | Where-Object {$_.NotAfter -ge (get-date -DisplayHint Date) } |ConvertTo-Html -Property Issuer,Thumbprint,Notafter -Head $Header | Out-File C:\Iris\Cert.html
$body=Get-Content -Path C:\Iris\cert.html -Raw

then while sending mail, you can do

send-emailmessage -body $body -bodyashtml #other values

References:
https://4sysops.com/archives/building-html-reports-in-powershell-with-convertto-html/

Chris · Accepted Answer · 2020-06-15 09:36:23Z

1

To build on user1491315 answer.

$html = "<table border='1'><tr>"
$tableCol = $table.Columns | select Caption
foreach($col in $tableCol){
    $html += "<td>" + $col.Caption + "</td>"
}
    $html += "</tr>"
$tableColCount = $table.Columns.Count
foreach ($row in $table.Rows)
{
    $html += "<tr>"
    For ($i=0; $i -le $tableColCount; $i++) {
        $html += "<td>" + $row[$i] + "</td>"
    }
    $html += "</tr>"
}
$html += "</table>"

This does the same thing but will automatically set column names.

answered Jun 15, 2020 at 9:36

Chris

111 bronze badge

Comments

nd34567s32e · Accepted Answer · 2018-10-25 13:20:03Z

0

generalized answer:

$body+=($variable|Select-Object property1,property2|ConvertTo-Html)

just use you own variables and properties. When other people search for solutions, this is what we want to see.

edited Oct 25, 2018 at 13:20

answered Jul 20, 2017 at 13:21

nd34567s32e

1861 silver badge7 bronze badges

Collectives™ on Stack Overflow

PowerShell Display Table In HTML Email

7 Answers 7

2 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

1 Comment

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related