Posted in : Microsoft, Office 365

2 years ago

If you’ve ever used Powershell to send HTML tables in Outlook containing CSS you’ve probably been disappointed of the outcome.
There is some archived documentation for Outlook 2007 that is still viable for Outlook 365 (https://msdn.microsoft.com/en-us/library/aa338201(v=office.12).aspx).
Basically the function accepts a csv and css file, hardcodes the css into the table and outputs a formatted HTML table that is compatible with Outlook.
Example table sent using the function and send-mailmessage
The css has odd/even for readability, bolded column 1/4 and red text for column 3.
This is by default impossible to achieve using just css in outlook.

Commandline

Convertto-OutlookHTMLTable -CSVFile 'C:\Temp\data.csv' -CSSFile 'c:\temp\style.css' -Delimiter ';'

HTML output

<table>
  <colgroup>
    <col class="col1" />
    <col class="col2" />
    <col class="col3" />
    <col class="col4" />
  </colgroup>
  <thead>
    <tr>
      <th>Name</th>
      <th>Surname</th>
      <th>Birthdate</th>
      <th>EmploymentDay</th>
    </tr>
  </thead>
  <tbody>
    <tr style="  background-color: #D0D3D4;">
      <td style="font-weight:bold;">john</td>
      <td>doe</td>
      <td style="font-weight:bold;">
        <font color="#ff0000">1967-01-23</font>
      </td>
      <td style="font-weight: bold;">2008-01-01</td>
    </tr>
    <tr style="  background-color: #ffffff;">
      <td style="font-weight:bold;">jane</td>
      <td>doe</td>
      <td style="font-weight:bold;">
        <font color="#ff0000">1977-01-23</font>
      </td>
      <td style="font-weight: bold;">2010-03-11</td>
    </tr>
    <tr style="  background-color: #D0D3D4;">
      <td style="font-weight:bold;">bill</td>
      <td>gates</td>
      <td style="font-weight:bold;">
        <font color="#ff0000">1987-01-23</font>
      </td>
      <td style="font-weight: bold;">2012-05-21</td>
    </tr>
    <tr style="  background-color: #ffffff;">
      <td style="font-weight:bold;">melinda</td>
      <td>gates</td>
      <td style="font-weight:bold;">
        <font color="#ff0000">1997-01-23</font>
      </td>
      <td style="font-weight: bold;">2014-07-05</td>
    </tr>
  </tbody>
</table>

CSS

Since the CSS does not work perfectly the style.css file imported needs some specific configuration..

  • classes has some specific name structure”
    • columns are named .coln
      • n is the number of the column starting with 1 to infinity. .col1 .col2 and so on
    • one whitespace is required between class name and the curlybrackets.
      • Curlybrackets must be on the same row as class name
      • Ending curlybrackets must be on a separate line
    • Data must be on separate rows
  • Odd/even css is the only tr handled code.
    • Must be named exactly
      • tbody tr:nth-child(odd) {
      • tbody tr:nth-child(even) {

Example style.CSS

body {
    color:#000000;
    font-family:Calibri;
    font-size:11pt;
}
h1 {
    font-size:12pt;
    font-weight:bold;
    color:#000000;
}
th {
    font-weight:bold;
    color:#ffffff;
    background-color:#009530;
    cursor:pointer;
}
/*
css in outlook has limitations, so there is some code to handle the col / table row
As such only some elements are handled, col elements *should* have higher priority than tbody tr
col is dynamic and has the same number of elements (1 to n) as there are columns in the csv file
 */
.col1 {
  font-weight:bold;
}
.col3 {
  color: #ff0000;
  font-weight:bold;
}
.col4 {
    font-weight: bold;
}
tbody tr:nth-child(odd) {
  background-color: #D0D3D4;
}
tbody tr:nth-child(even) {
  background-color: #ffffff;
}

Function

Function Convertto-OutlookHTMLTable {
Param(
    [string]$CSVFile,
    [string]$Delimiter,
    [string]$CSSFile
)
    $rawcsvresults = Get-Content $CSVFile
    $css = Get-Content $CSSFile
    #Generate Column CSS (as col1, col2, col3 etc)
    $colcount = $rawcsvresults[0].Split($Delimiter).Count
    $colgroups = 1..$colcount | ForEach-Object{"<col class=`"col$_`" />"}
    $colgroup = "<colgroup>$colgroups</colgroup>"
    #Create table headers
    $tableheaders = $rawcsvresults[0].Split($Delimiter).Trim('"') | ForEach-Object{"<th>$_</th>"}
    $thead = "<thead><tr>$tableheaders</tr></thead>"
    # Since css/html in outlook is bad (https://msdn.microsoft.com/en-us/library/aa338201(v=office.12).aspx)
    # The below code takes the css for col(n) and "tbody tr:nth-child(odd/even)" and tries to combine them
    # The priority is col > tr
    $csssplit = $css.Split("`r`n") | Where-Object{$_ -ne ''}
    If($csssplit -contains "tbody tr:nth-child(odd) {"){
        $i = 0
        $s = 0
        $e = 0
        $csssplit | ForEach-Object{
            $i++
            If($_.Trim() -eq "tbody tr:nth-child(odd) {"){
                $s = $i
            }
            If(($e -lt $s) -and ($_.Trim() -eq "}")){
                $e = $i-2
            }
        }
        $oddstyle = "style=`"" + ($csssplit[$s..$e] -join '') + '"'
    }
    If($csssplit -contains "tbody tr:nth-child(even) {"){
        $i = 0
        $s = 0
        $e = 0
        $csssplit | ForEach-Object{
            $i++
            If($_.Trim() -eq "tbody tr:nth-child(even) {"){
                $s = $i
            }
            If(($e -lt $s) -and ($_.Trim() -eq "}")){
                $e = $i-2
            }
        }
        $evenstyle = "style=`"" + ($csssplit[$s..$e] -join '') + '"'
    }
    #Fill rows
    $i = 0
    $tablerows = $rawcsvresults[1..($rawcsvresults.Count-1)] | ForEach-Object{
        $i++
        $c = 0
        $thisRow = $_.Split(';').Trim('"') | ForEach-Object{
            $c++
            $ci = 0
            $s = 0
            $e = 0
            $font = $null
            $style = $null
            If($csssplit -contains ".col$c {"){
                $csssplit | ForEach-Object{
                    $ci++
                    If($_.Trim() -eq ".col$c {"){
                        $s = $ci
                    }
                    If(($e -lt $s) -and ($_.Trim() -eq "}")){
                        $e = $ci-2
                    }
                }
                $csssplit[$s..$e] | ForEach-Object{
                    If($_.Trim().Startswith("color:")){
                        $font = $_.Trim().replace(';','')
                        $font = $font.Split(':')[0].trim()+"=`""+$font.Split(':')[1].trim()+"`""
                    }
                    Else{$style+=$_.Trim()}
                }
                If($font -ne $null){
                    $fstyle = "<font $font>"
                }
                $cstyle = "style=`"$style`""
            }
            if($font -ne $null){$t = "$fstyle$_</font></td>"}
            else{$t = "$_</td>"}
            If($style -ne $null){$s = "<td $cstyle>"}
            else{$s = "<td>"}
            "$s$t"
        }
        If($i%2 -eq 1){"<tr $oddstyle>$thisRow</tr>"}
        else{"<tr $evenstyle>$thisRow</tr>"}
    }
    $tbody = "<tbody>$tablerows</tbody>"
$table = "<table><colgroup>$colgroups</colgroup><thead><tr>$tableheaders</tr></thead>$tbody</table>"
    Return $table
}

 

Tags : css, html, Microsoft, Office 365, Outlook, PowerShell, send-mailmessage

Add comment

Your comment will be revised by the site if needed.