PowerShell email as body

Django x2 · 15 Jun 2015 at 13:50

Hi all,

Ignoring the SharePoint element of this, all I'm really trying to do is make the result display in the body of the email. it doesn't The attachment is fine, renders fine and pulls the CSS fine.

I have to assume that it is because I'm not really closing off the HTML anywhere, but not sure.....

Anyway, here's the code, sanitised slightly, but you get the idea:

PHP:

# Add SharePoint snap-in
Add-PSSnapin Microsoft.SharePoint.PowerShell

# Set Variables
$Today = Get-Date -Format "dd-MM-yyy_hh-mm-ss"
$OutputPath = "E:\SiteCollectionSize"
$OutputFileName = "SPSCsizes-$Today"
$OutputFile = "$OutputPath\$OutputFileName.html"
$RetentionTimeDays = "14"
#SMTP Settings

$smtpsettings = @{
To = "[email protected]"
From = "[email protected]"
Subject = "SharePoint Site Collection Sizes"
SmtpServer = "my.smtp.server"
}

#HTML and CSS Var
$style = "
<style>
body { font-family:verdana; background-color: #b0c4de; }
table { font-size: 0.7em; width: 100%; border-width: 1px; border-style: solid; border-color: #000; border-collapse: collapse; }
th { border-width: 1px; padding: 2px 6px 1px 6px; border-style: solid; border-color: #000; background-color: #778899; }
td { border-width: 1px; padding: 2px 6px 1px 6px; border-style: solid; border-color: #000; }
tr:nth-child(odd) { background-color: #d3d3d3; }
tr:nth-child(even) { background-color: #fff; }
h2 { clear: both; font-size: 0.9em; }
</style
"

# PowerShell Query for Site Collection Sizes
$query = Get-SPSiteAdministration -Limit All | select Url, @{label="GB";Expression={[System.Math]::Round($_.DiskUsed/1024MB, 0)}} | Sort-Object -Descending -Property "GB" | ConvertTo-Html -Head $style -PreContent '<h2>SharePoint Site Collection by Size</h2>' | Out-File $OutputFile

#Send email message
Send-MailMessage @smtpsettings -Attachments $OutputFile -BodyAsHtml $style -Priority Normal

Washout · 15 Jun 2015 at 16:50

I might be reading it wrong but is the above not just setting the body as $style which doesn't actually have any non html text? Also looks like you're missing a '>' off the end of style?

Django x2 · 16 Jun 2015 at 08:24

Yes, essentially, the PowerShell query grabs the table and then outputs it to HTML using the CSS style I created. This all works fine and produces a very nice HTML report, which, when viewed in IE looks lovely.

However, I'm most satisfied with least number of clicks possible, so I want the nice formatted HTML to appear in the body of the email (and as an attachment).

Ignore the > on the end of style, I just missed it off the copy when adding it to the post.

Django x2 · 16 Jun 2015 at 08:57

Solved it. I added the body parameter to the SMTP settings section and used Get-Content $OutputFile | out-string and then moved the SMTP settings below the query, but above the send-mailmessage

Outlook HTML / CSS formatting is not the same, but it's near enough

Washout · 16 Jun 2015 at 10:32

Nice one!

HungryHippos · 16 Jun 2015 at 10:44

I do some stuff with this if you are interested in seeing another way. I store the HTML in a here-string in this example, and then send it down the bottom using Send-MailMessage with some parameters (clearly just dummy data here).

You can do similar, but interject data in the <body> section by adding lines like:

$HTML += "<p>New Paragraph</p>"

PHP:

$HTML = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Title Here</title>

<style type="text/css">

body {
  margin: 0;
  font-family: "Segoe UI", "Helvetica Neue", Helvetica, Arial, sans-serif;
  font-size: 13px;
  line-height: 20px;
  color: #333333;
  background-color: #ffffff;
  width: 800px;
}

table {
  background-color: transparent;
  border-collapse: collapse;
  border-spacing: 0;
  margin-bottom: 20px; 
  padding: 0px;
  width: 800px;
}

table th,
table td {
  margin: 0px;
  padding: 4px;
  text-align: left;
  border-top: 1px solid #dddddd;
}

table th {
  font-weight: bold;
}

h1,
h2,
h3,
h4,
h5,
h6 {
  margin: 10px 0;
  font-family: inherit;
  font-weight: bold;
  line-height: 1;
  color: inherit;
  text-rendering: optimizelegibility;
  font-family: "Segoe UI", "Helvetica Neue", Helvetica, Arial, sans-serif;  
}

h1 small,
h2 small,
h3 small,
h4 small,
h5 small,
h6 small {
  font-weight: normal;
  line-height: 1;
  color: #999999;
}

h1 {
  font-size: 36px;
  line-height: 40px;
}

h2 {
  color: #f47c3c;
  font-size: 28px;
  line-height: 40px;
}

h3 {
  font-size: 22px;
  line-height: 40px;
}

h4 {
  font-size: 17px;
  line-height: 20px;
}

h5 {
  font-size: 14px;
  line-height: 20px;
}

h6 {
  font-size: 12px;
  line-height: 20px;
}

h1 small {
  font-size: 24px;
}

h2 small {
  font-size: 18px;
}

h3 small {
  font-size: 14px;
}

h4 small {
  font-size: 14px;
}

p {
  margin: 0 0 10px;
}

.Error
{
	color: red;
	font-weight: bold;
}

.Bold
{
	font-weight: bold;	
}

</style>

</head><body>

<h1>Heading Here</h1>

<p>Text Here</p>

</body></html>
"@

#Mail Message
$Sender = "[email protected]"
$Recipient = "[email protected]"
$CC = "[email protected]"
$Server = "mail.server"
$Subject = "Email Subject"

Send-MailMessage -Subject $Subject -From $Sender -To $Recipient -CC $CC -SmtpServer $Server -BodyAsHTML ([string]$HTML) -UseSSL

Django x2 · 16 Jun 2015 at 13:01

Cool, thanks for the above.

I've got another issue now where I want to add the date to the H2 string here:

PHP:

# PowerShell Query for Site Collection Sizes
$query = Get-SPSiteAdministration -Limit All | select Url, @{label="GB";Expression={[System.Math]::Round($_.DiskUsed/1024MB, 0)}} | Sort-Object -Descending -Property "GB" | ConvertTo-Html -Head $style -PreContent '<h2>SharePoint Site Collection by Size</h2>' | Out-File $OutputFile

As I have the data as $today earlier in the script, I was hoping I could just escape out the PowerShell as:

PHP:

ConvertTo-Html -Head $style -PreContent @'<h2>SharePoint Site Collection by Size $today</h2>'@ | Out-File $OutputFile

But that doesn't work. I get an error about passing a string before or similar.

I want the date to pick up the h2 CSS formatting and obviously be today's date (or the date the script was run).

Any ideas?

HungryHippos · 16 Jun 2015 at 14:20

Well like in mine just declare an initial variable for everything (including style etc) down to the end of the </head> and start of <body> tag, then you can just append in HTML content as you go, and lastly just close it off with </body></html>.

You don't really need to keep it in a file, you can just put HTML into memory in a variable.

Something like this should work.

PHP:

$HTML = @"
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Title Here</title>

<style type="text/css">

body {
  margin: 0;
  font-family: "Segoe UI", "Helvetica Neue", Helvetica, Arial, sans-serif;
  font-size: 13px;
  line-height: 20px;
  color: #333333;
  background-color: #ffffff;
  width: 800px;
}

table {
  background-color: transparent;
  border-collapse: collapse;
  border-spacing: 0;
  margin-bottom: 20px; 
  padding: 0px;
  width: 800px;
}

table th,
table td {
  margin: 0px;
  padding: 4px;
  text-align: left;
  border-top: 1px solid #dddddd;
}

table th {
  font-weight: bold;
}

h1,
h2,
h3,
h4,
h5,
h6 {
  margin: 10px 0;
  font-family: inherit;
  font-weight: bold;
  line-height: 1;
  color: inherit;
  text-rendering: optimizelegibility;
  font-family: "Segoe UI", "Helvetica Neue", Helvetica, Arial, sans-serif;  
}

h1 small,
h2 small,
h3 small,
h4 small,
h5 small,
h6 small {
  font-weight: normal;
  line-height: 1;
  color: #999999;
}

h1 {
  font-size: 36px;
  line-height: 40px;
}

h2 {
  color: #f47c3c;
  font-size: 28px;
  line-height: 40px;
}

h3 {
  font-size: 22px;
  line-height: 40px;
}

h4 {
  font-size: 17px;
  line-height: 20px;
}

h5 {
  font-size: 14px;
  line-height: 20px;
}

h6 {
  font-size: 12px;
  line-height: 20px;
}

h1 small {
  font-size: 24px;
}

h2 small {
  font-size: 18px;
}

h3 small {
  font-size: 14px;
}

h4 small {
  font-size: 14px;
}

p {
  margin: 0 0 10px;
}

.Error
{
	color: red;
	font-weight: bold;
}

.Bold
{
	font-weight: bold;	
}

</style>

</head><body>
"@

# PowerShell Query for Site Collection Sizes
$HTML += "<h2>SharePoint Site Collection by Size</h2>"
$query = Get-SPSiteAdministration -Limit All | select Url, @{label="GB";Expression={[System.Math]::Round($_.DiskUsed/1024MB, 0)}} | Sort-Object -Descending -Property "GB" | ConvertTo-Html -Fragment
$HTML += $query

$HTML += "</body></html>"

Note I use -Fragment for the Convert HTML line, this tells it just to create the table in HTML without including additional head, body, html tags etc.