How To Generate An Email With Powershell Containing Unknown Table Sizes With Office-specific Color Schemes?
Dear fellow programmers, I have tried my best to find the solution to this, but I finally decided to create a stackoverflow account and ask for help. What I am trying to do: 1. Get
Solution 1:
Another way is to convert html to XmlDocument and then add style attributes. By using XPath you can efficiently give it style.
filter Add-InlineStyle {
$doc = [xml]$_
# body
$doc.SelectNodes("//body").SetAttribute("style", "font-size: 12pt")
# tables
$doc.SelectNodes("//table").SetAttribute("style", "font-size: 11pt; border: 2px solid black; border-collapse: collapse; overflow-x: auto")
# column headers
$doc.SelectNodes("//th").SetAttribute("style", "border: 0.5px solid white; text-align: center; padding: 0px 5px; font-weight: bold; color: white; background: black")
# row headers
$doc.SelectNodes("//td[1]").SetAttribute("style", "border: 0.5px solid white; text-align: center; padding: 0px 5px; font-weight: bold; color: white; background: black")
# even rows
$doc.SelectNodes("//tr[position() mod 2 = 0]/td[position() != 1]").SetAttribute("style", "border: 0.5px solid white; text-align: right; padding: 0px 5px; background: #CCCCCC")
# odd rows
$doc.SelectNodes("//tr[position() mod 2 = 1]/td[position() != 1]").SetAttribute("style", "border: 0.5px solid white; text-align: right; padding: 0px 5px; background: #999999")
$doc.OuterXml;
}
The usage is below.
$data1 = @"
Table1,Column1,Column2
Row1,1,2
Row2,3,4
Row3,5,6
Row4,7,8
Row5,9,10
"@
$data2 = @"
Table2,Column1
Row1,1
Row2,2
Row3,3
"@
$data3 = @"
Table3,Column1,Column2,Column3,Column4,Column5
Row1,1,2,3,4,5
Row2,6,7,8,9,10
"@
$html = @(
"<html><body>"
"This is table1"
$data1 | ConvertFrom-Csv | ConvertTo-Html -Fragment
"This is table2"
$data2 | ConvertFrom-Csv | ConvertTo-Html -Fragment
"This is table3"
$data3 | ConvertFrom-Csv | ConvertTo-Html -Fragment
"</body></html>"
) | Out-String | Add-InlineStyle
Solution 2:
As commented, you need to do a lot more styling to mimic the "Grid Table 5 Dark Accent 5" table styling from Word. Because Outlook does not handle the rendering of HTML like a modern browser can, the code requires quite a number of inline style definitions.
Since ConvertTo-Html -Fragment cannot do that you need to manually build the HTML tables.
The code below does that using in function ConvertTo-HtmlTable.
It expects as parameter either a System.Data.DataTable object or an array of PSCustomObjects.
# needed for [System.Web.HttpUtility]::HtmlEncode()
Add-Type -AssemblyName System.Web
functionConvertTo-HtmlTable{
[CmdletBinding()]
param(
[Parameter(Mandatory = $true, Position = 0)]
[object]$Table
)
if ($Table -is [System.Data.DataTable]) {
# convert to array of PSCustomObjects$Table = $Table | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors
}
# manually build the HTML table$tdFirst = '<td style="background: black; color: white; font-weight: bold;">'$tdOdd = '<td style="background: #999999;">'$tdEven = '<td style="background: #CCCCCC;">'# add the headers row$headers = @($Table[0].PSObject.Properties | Select -ExpandProperty Name)
$tbl = New-Object -TypeName System.Text.StringBuilder
[void]$tbl.Append('<table><thead><tr>')
foreach ($col in $headers) {
[void]$tbl.Append("<th>$col</th>")
}
[void]$tbl.Append('</tr></thead><tbody>')
# next add the data rows$row = 0$Table | ForEach-Object {
[void]$tbl.AppendLine('<tr>')
for ($col = 0; $col -lt $headers.Count; $col++) {
[string]$val =$_.$($headers[$col])
$td = if ($col -eq 0) { $tdFirst } elseif ($row -band 1) { $tdOdd } else { $tdEven }
[void]$tbl.Append($td)
$data = if ([string]::IsNullOrWhiteSpace($val)) { ' ' } else { [System.Web.HttpUtility]::HtmlEncode($val) }
[void]$tbl.AppendLine("$data</td>")
}
[void]$tbl.AppendLine('</tr>')
$row++
}
[void]$tbl.Append('</tbody></table>')
return$tbl.ToString()
}
Now that we have this function in place, you then use it in your TableX_creation functions like this:
functionTable1_creation{
$Table = New-Object System.Data.DataTable
$col1 = New-Object system.Data.DataColumn Table1_Column1,([string])
$col2 = New-Object system.Data.DataColumn Table1_Column2,([string])
$col3 = New-Object system.Data.DataColumn Table1_Column3,([string])
$Table.columns.add($col1)
$Table.columns.add($col2)
$Table.columns.add($col3)
$Tablerows = "Table1_Column1_Row2","Table1_Column1_Row3","Table1_Column1_Row4","Table1_Column1_Row5","Table1_Column1_Row6"foreach ($row in $Tablerows) {
$Table.Rows.Add($row) | Out-Null
}
$result = ConvertTo-HtmlTable $Table$Table.Dispose()
return$result
}
functionTable2_creation{
$Table = New-Object System.Data.DataTable
$col1 = New-Object system.Data.DataColumn Table2_Column1,([string])
$col2 = New-Object system.Data.DataColumn Tabl2_Column2,([string])
$Table.columns.add($col1)
$Table.columns.add($col2)
$Tablerows = "Table2_Column1_Row2","Table2_Column1_Row3","Table2_Column1_Row4"foreach ($Row in $Tablerows) {
$Table.Rows.Add($row) | Out-Null
}
$result = ConvertTo-HtmlTable $Table$Table.Dispose()
return$result
}
functionTable3_creation{
$Table = New-Object System.Data.DataTable
$col1 = New-Object system.Data.DataColumn Table3_Column1,([string])
$col2 = New-Object system.Data.DataColumn Table3_Column2,([string])
$col3 = New-Object system.Data.DataColumn Table3_Column3,([string])
$col4 = New-Object system.Data.DataColumn Table3_Column4,([string])
$col5 = New-Object system.Data.DataColumn Table3_Column5,([string])
$col6 = New-Object system.Data.DataColumn Table3_Column6,([string])
$Table.columns.add($col1)
$Table.columns.add($col2)
$Table.columns.add($col3)
$Table.columns.add($col4)
$Table.columns.add($col5)
$Table.columns.add($col6)
$Tablerows = "Table3_Column1_Row2","Table3_Column1_Row3"foreach ($Row in $Tablerows) {
$Table.Rows.Add($row) | Out-Null
}
$result = ConvertTo-HtmlTable $Table$Table.Dispose()
return$result
}
Next, I did a function to put all these HTML fragments together:
function Generate_Html {
Write-Host -ForegroundColor Green "Generating Email Body..."
# main style settings mimicing "Grid Table 5 Dark Accent 5"
$style = @'
<head><style>BODY { font-size: 12pt; font-family: calibri, Arial, Helvetica, sans-serif; }
TABLE { font-size: 11pt; font-family: calibri, Arial, Helvetica, sans-serif;
border: 0.5px solid white; border-collapse: collapse; overflow-x:auto; color: white; width: auto; }
TH { border: none;background: black; padding: 08px08px; font-weight: bold; text-align: left; }
TD { border: 0.5px solid white; padding: 08px08px; color: black; text-align: left; }
</style></head>
'@
$sb = New-Object -TypeName System.Text.StringBuilder
[void]$sb.AppendLine("<html>")
[void]$sb.AppendLine($style)
[void]$sb.AppendLine("<body>")
if (!$tables) {
[void]$sb.AppendLine('<h3>THIS IS TABLE 1</h3>')
[void]$sb.AppendLine((Table1_creation))
[void]$sb.AppendLine('<h3>THIS IS TABLE 2</h3>')
[void]$sb.AppendLine((Table2_creation))
[void]$sb.AppendLine('<h3>THIS IS TABLE 3</h3>')
[void]$sb.AppendLine((Table3_creation))
}
else {
Write-Host -ForegroundColor Red "Error...";
}
[void]$sb.AppendLine("</body></html>")
return $sb.ToString()
}
So at the end you do your Outlook stuff like this:
# I'm guessing you create your `$Outlook` variable sort of like belowif(([System.Diagnostics.Process]::GetProcessesByName("OUTLOOK")).length -gt 0){
$Outlook = [Runtime.InteropServices.Marshal]::GetActiveObject("Outlook.Application")
}
else {
$Outlook = New-Object -comObject Outlook.Application
}
$mail = $Outlook.CreateItem(0)
$mail.HTMLBody = Generate_Html
$inspector = $mail.GetInspector
$inspector.Display()
The final outcome should then look like
Post a Comment for "How To Generate An Email With Powershell Containing Unknown Table Sizes With Office-specific Color Schemes?"