PowerShell Send-MailMessage format column in message body

Question

Warning - I'm new to PowerShell. There are two outcomes I would like to achieve with this script. The first is to include the output in an email and format the columns in the message body so they align with the headers similar to Out-Host. Second is when out-csv, out-gridview or export-excel, how do I order the columns?

$VolArray = @();


$Volumes = Get-Ncvol | Where-Object {$_.VolumeMirrorAttributes.IsDataProtectionMirror -match 'False' -and $_.VolumeStateAttributes.IsVserverRoot -match 'False' -and -not $_.VolumeCloneAttributes.VolumeCloneParentAttributes}
    ForEach ($Volume in $Volumes){
        #get properties
        $vol = Get-Ncvol $Volume
        #create object with values
        $volobj = New-Object -TypeName PSObject -Property @{
            'Controller' = $vol.NcController
            'Vserver' = $vol.Vserver
            'Aggregate' = $vol.VolumeIdAttributes.ContainingAggregateName 
            'Name' = $vol.VolumeIdAttributes.Name 
            'Type' = $vol.VolumeIdAttributes.Type 
            'TotSizeGB'= $vol.VolumeSpaceAttributes.Size/1gb
            'Used' = $vol.VolumeSpaceAttributes.SizeUsed/1gb
            '%Used' = $vol.VolumeSpaceAttributes.PercentageSizeUsed
            'AvailableGB' = $vol.VolumeSpaceAttributes.SizeAvailable/1gb
            'SSResSizeGB' = $vol.VolumeSpaceAttributes.SnapshotReserveSize/1GB 
            'IsDPMirror' = $vol.VolumeMirrorAttributes.IsDataProtectionMirror 
            'IsReplicaVol' = $vol.VolumeMirrorAttributes.IsReplicaVolume 
            'IsDPSource' = $vol.VolumeMirrorAttributes.IsSnapmirrorSource 
            'DPInProgress' = $vol.VolumeMirrorAttributes.MirrorTransferInProgress
            'SSPolicy' = $vol.VolumeSnapshotAttributes.SnapshotPolicy 
            'AutoSSEnabled' = $vol.VolumeSnapshotAttributes.AutoSnapshotsEnabled 
            'SSCount' = $vol.VolumeSnapshotAttributes.SnapshotCount
            '%SSReserve' = $vol.VolumeSpaceAttributes.PercentageSnapshotReserve 
            '%SSResUsed' = $vol.VolumeSpaceAttributes.PercentageSnapshotReserveUsed
            'SSSpaceUsed' = $vol.VolumeSpaceAttributes.SizeUsedBySnapshots/1GB;

        }
        #add to array outside opf for-loop
        $VolArray += $volobj

    } 


    #$VolArray | Export-Csv -Path c:\temp\file.csv
    #$VolArray | Export-Excel -Path c:\temp\exceldump.xlsx
    $VolArray | Out-String

#Send-MailMessage -To $mailto -Subject $subject -Body (-join $message) -Port $port -SmtpServer $smtp -from $emailfrom 
Send-MailMessage -To $mailto -Subject $subject -Port $port -SmtpServer $smtp -from $emailfrom -Attachments c:\temp\file.csv 

Message body:

Answer 1

[Ordered] worked perfectly.

I modified the message body parameter: Body = ($emailBody | Out-String)

Because of this error:

Send-MailMessage : Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'Body'. Specified method is not supported.

Would you have any recommendations for setting the number of decimal places? ...124.994548797607421875

Answer 2

Column ordering

In PowerShell, for performance reasons, there is no guarantee of order for properties of common hashtables. Thankfully you can use the [ordered] keyword to create ordered dictionaries (a hashtable is a form of dictionary) since version 3.

[PSCustomObject][ordered]@{
    FirstColumn  = 1
    SecondColumn = 2
    ThirdColumn = 3
}

This will ensure the order of the properties in subsequent operations like Export-Csv. Note that I also used the [PSCustomObject] accelerator, which is more efficient and than New-Object -TypeName PSObject.

Getting the data efficiently

In your code there are unnecessary calls to Get-Ncvol in the foreach loop. You already have the data you need form earlier:

$Volumes = Get-Ncvol |
Where-Object {
    $_.VolumeMirrorAttributes.IsDataProtectionMirror -match 'False' -and
    $_.VolumeStateAttributes.IsVserverRoot -match 'False' -and
    -not $_.VolumeCloneAttributes.VolumeCloneParentAttributes
}

# Store results in a variable to use later
$reportData = foreach ($Volume in $Volumes) {
    # Create object with values
    [PSCustomObject][ordered]@{
        'Controller'    = $Volume.NcController
        'Vserver'       = $Volume.Vserver
        'Aggregate'     = $Volume.VolumeIdAttributes.ContainingAggregateName 
        'Name'          = $Volume.VolumeIdAttributes.Name 
        'Type'          = $Volume.VolumeIdAttributes.Type 
        'TotSizeGB'     = $Volume.VolumeSpaceAttributes.Size / 1gb
        'Used'          = $Volume.VolumeSpaceAttributes.SizeUsed / 1gb
        '%Used'         = $Volume.VolumeSpaceAttributes.PercentageSizeUsed
        'AvailableGB'   = $Volume.VolumeSpaceAttributes.SizeAvailable / 1gb
        'SSResSizeGB'   = $Volume.VolumeSpaceAttributes.SnapshotReserveSize / 1GB 
        'IsDPMirror'    = $Volume.VolumeMirrorAttributes.IsDataProtectionMirror 
        'IsReplicaVol'  = $Volume.VolumeMirrorAttributes.IsReplicaVolume 
        'IsDPSource'    = $Volume.VolumeMirrorAttributes.IsSnapmirrorSource 
        'DPInProgress'  = $Volume.VolumeMirrorAttributes.MirrorTransferInProgress
        'SSPolicy'      = $Volume.VolumeSnapshotAttributes.SnapshotPolicy 
        'AutoSSEnabled' = $Volume.VolumeSnapshotAttributes.AutoSnapshotsEnabled 
        'SSCount'       = $Volume.VolumeSnapshotAttributes.SnapshotCount
        '%SSReserve'    = $Volume.VolumeSpaceAttributes.PercentageSnapshotReserve 
        '%SSResUsed'    = $Volume.VolumeSpaceAttributes.PercentageSnapshotReserveUsed
        'SSSpaceUsed'   = $Volume.VolumeSpaceAttributes.SizeUsedBySnapshots / 1GB;
    }
}

Exporting & emailing

Since we already took care of the column ordering you just need to use $reportData | Export-Csv c:\temp\file.csv -NoTypeInformation or the Export-Excel equivalent.

Emailing is going to be a bit more difficult. Your best bet to convert the data to an HTML table and include it as the body of the email.

# The CSS is neccesary to make the table look nicer, adjust as needed
$css = @'
<style>
  body {background-color: powderblue;}
  h1 {color: red;}
  p {color: blue;}
  th, td { 
    padding: 15px;
    text-align: left;
  }
</style>
'@

$emailBody = $reportData | ConvertTo-Html -Head $css

# Use parameter splatting for redability
$emailParameters = @{
    To         = "jdoe@company.com" 
    Subject    = "NetApp report for $(Get-Date -Format 'd')"
    Body       = $emailBody
    BodyAsHtml = $true
    SmtpServer = "smtp.company.com"
    Credential = Get-Credential 
}
Send-MailMessage @emailParameters