Powershell HTML body converting issue

June 13, 2022

I have a script that reads a list on a Sharepoint 2019 OnPremise site. The idea is to capture certifcates that are about to expire.

Problem is I can’t conver it properly to a html-table however I do. I done converting before but not in PNP and with camel querys.

Script:

$smtpserver = "-"
$mailsender ="-"
$mailReceiver = "-"
$mailSubject = "Certificates about to expire"

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


    Import-Module "C:\PNP\SharePointPnPPowerShell2019\3.6.1902.2\SharePointPnPPowerShell2019.psd1"
    if ($cred -eq $null)
    { 
    $cred = Get-Credential
    }
    Connect-PnPOnline "-" -Credentials $cred
    $list = Get-PnPList "-"
        
$listitems = Get-PnPListItem -List $list -Query "<View><Query><Where><Lt><FieldRef Name='Utg_x00e5_r' Type='DateTime'/><Value Type='DateTime'><Today OffsetDays='60'/></Value></Lt></Where></Query></View>" | Select-object {$_["Title"], $_["Farm"], $_["Server"] , $_["Utg_x00e5_r"]} | ConvertTo-Html -Head $header | Out-String


Send-MailMessage -From $mailsender -to $mailReceiver -subject $mailSubject -body ($listitems | Out-String) -SmtpServer $smtpServer -Port 25 -BodyAsHtml

What am I missing?

>Solution :

The Select-Object statement currently creates objects with only 1 property, hence the single column in your output.

Change it to something like:

...| Select-Object @{Name='Title';Expression={$_["Title"]}},@{Name='Farm';Expression={$_["Farm"]}},@{Name='Server';Expression={$_["Server"]}},@{Name='Utg_x00e5_r';Expression={$_["Utg_x00e5_r"]}} |ConvertTo-Html ...

… which will create objects with 4 separate properties, which will in turn translate into a 4-column HTML table