Link to home
Start Free TrialLog in
Avatar of jack jones
jack jones

asked on

Convert output into html mail body using powershell , not able to generate output in mail body

Below is the exchange script that check the queue length and sends a output in the mail, not able to generate output in the mail body
$snapins = Get-PSSnapin
$snapins | foreach-Object {

 if ($_.name -match “Exchange”)
 {
  $exchloaded = $TRUE
 }
}
if ($exchloaded -eq $TRUE)
{
 if ($showgui)
 {
  Write-Host -ForegroundColor Green “Exchange 2010 Snapin already loaded.”
 }
}
else
{
 Add-PSSnapin *Exchange*
 if ( $showgui ) { Write-Host -ForegroundColor Green “Exchange 2010 Snapin had to be loaded.” }
}

$filename = “C:\temp\q.html"
Start-Sleep -s 10 
if (Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | get-queue | Where-Object { $_.MessageCount -gt 30 }) 

{ 
$a = "<style>"
$a = $a + "BODY{background-color:white;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;foreground-color: black;background-color: LightBlue}"
$a = $a + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;foreground-color: black;background-color: white}"
$a = $a + "</style>"

Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | get-queue | Where-Object { $_.MessageCount -gt 30 } | Format-Table -Wrap -AutoSize | ConvertTo-Html -Head $a  | out-file $filename
$smtpserver = "servername"
$to = "abc@abc.com"
$from = "sender"
$body = Get-Content $filename
Send-MailMessage -SmtpServer $smtpserver -Subject "Queue test" -Body $body -To $to -From $from 
}

Open in new window

Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

The only problem I can see (so far) is that $body needs to be a string where you'll have an array.
$body = Get-Content $filename -Raw

Open in new window

Or:
$body = Get-Content $filename | Out-String

Open in new window

Extending upon this, you don't need the intermediate file at all.
if (Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | get-queue | Where-Object { $_.MessageCount -gt 30 }) 

{ 
$a = "<style>"
$a = $a + "BODY{background-color:white;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;foreground-color: black;background-color: LightBlue}"
$a = $a + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;foreground-color: black;background-color: white}"
$a = $a + "</style>"

$body = Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | get-queue | Where-Object { $_.MessageCount -gt 30 } | ConvertTo-Html -Head $a | Out-String
$smtpserver = "servername"
$to = "abc@abc.com"
$from = "sender"
Send-MailMessage -SmtpServer $smtpserver -Subject "Queue test" -Body $body -To $to -From $from 
}

Open in new window

By looking at that again I noticed you had Format-Table in there, you absolutely do not want that. If you keep the intermediate file approach tihs line changes, removing Format-Table:
Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | get-queue | Where-Object { $_.MessageCount -gt 30 } | ConvertTo-Html -Head $a  | out-file $filename

Open in new window

Using Format-Table will make a horrible mess when ConvertTo-Html is called afterwards.
Avatar of jack jones
jack jones

ASKER

I used the below snippet but getting output as
snippet :

if (Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | get-queue | Where-Object { $_.MessageCount -gt 30 }) 

{ 
$a = "<style>"
$a = $a + "BODY{background-color:white;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;foreground-color: black;background-color: LightBlue}"
$a = $a + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;foreground-color: black;background-color: white}"
$a = $a + "</style>"

$body = Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | get-queue | Where-Object { $_.MessageCount -gt 30 } | ConvertTo-Html -Head $a | Out-String
$smtpserver = "servername"
$to = "abc@abc.com"
$from = "sender"
Send-MailMessage -SmtpServer $smtpserver -Subject "Queue test" -Body $body -To $to -From $from 
}

Open in new window


output :

<!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>
<style>BODY{background-color:white;}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Chris Dent
Chris Dent
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
YEs it worked thanks :)
I have below snippet that checks the snapshot it works fine, but when there is no snapshot it still sends what is written in the body, can we custom the body like if else , if there is no snapshot it will send a different message in the mail
$a = "<style>"
$a = $a + "BODY{background-color:white;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 5px;border-style: solid;border-color: black;foreground-color: black;background-color: LightBlue}"
$a = $a + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;foreground-color: black;background-color: white}"
$a = $a + "</style>"

$date = get-date
$datefile = get-date -uformat '%m-%d-%Y-%H%M%S'
$filename = "C:\snaptest\Snap_" + $datefile + ".htm"

$ss = Get-vm | Get-Snapshot

$ss | Select-Object vm, name, description, created, @{N="SizeGB";E={[int]$_.sizegb}} | ConvertTo-HTML -head $a -body "<H2>VM Snapshot Report Servername</H2>" | Out-File $filename




$server = "servername"
$port = portno
$to      = "abc@abc.com"
$from    = "sender"
$subject = "Report"
$body = Get-Content $filename

$message = New-Object system.net.mail.MailMessage $from, $to, $subject, $body
$client = New-Object system.Net.Mail.SmtpClient $server, $port

try {
   
# Convert body to HTML
    $message.IsBodyHTML = $true

$client.Send($message)
    "Message sent successfully"

}
catch {

    "Exception caught in CreateTestMessage1(): "

}

Open in new window

Yep, it can be done like this.
$sendMailMessageParams = @{
    Smtpserver = "servername"
    To         = "abc@abc.com"
    From       = "sender"
    Subject    = "Report"
    BodyAsHtml = $true
}

$head = "<style>",
        "BODY { background-color: white; }",
        "TABLE { border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse; }",
        "TH { border-width: 1px; padding: 5px; border-style: solid; border-color: black; foreground-color: black; background-color: LightBlue}",
        "TD { border-width: 1px; padding: 5px; border-style: solid; border-color: black; foreground-color: black; background-color: white}",
        "</style>" | Out-String
        
if ($snapshot = Get-VM | Get-Snapshot) {
    $body = $snapshot | Select-Object vm, name, description, created, @{N="SizeGB";E={[int]$_.sizegb}} | 
        ConvertTo-HTML -head $a -body "<H2>VM Snapshot Report Servername</H2>" |
        Out-String
} else {
    $body = ConvertTo-Html -Head $head -Body "<p>What should the body say?</p>" |
        Out-String
}

Send-MailMessage -Body $body @sendMailMessageParams

Open in new window

one thing to ask,
all smtp parameters you are specifying in sendMailMessageParams and calling sendMailMessageParams at the end..
also wat does Start-Sleep -s 10  does ..
The short answer on that one is that mail message parameters often change, putting them at the top means you don't have to dig through the script to find them.

The technique I'm using is called splatting. Splatting can be used to pass parameters into commands in PowerShell. When using splatting, parameters and their values are declared in a hashtable (that's @{}) which is assigned to a variable. The "@" character is used to "splat" the content of the hashtable into a command as a set of parameters.

The second command, Start-Sleep, sends the script to sleep for 10 seconds (-s is -Seconds).
when i run this manually it runs successfully and sends a mail. It also runs successfully in task scheduler but doesn't sends a mail.
Action - Start a program
Program: Powershell.exe
Add a argument : Link to script(C:\test\test.ps1)
modified a script a bit pls check

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn;
$sendMailMessageParams = @{
    Smtpserver = "servername"
    To         = "abc@abc.com"
    From       = "sender"
}

$head = "<style>",
        "BODY { background-color: white; }",
        "TABLE { border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse; }",
        "TH { border-width: 1px; padding: 5px; border-style: solid; border-color: black; foreground-color: black; background-color: LightBlue}",
        "TD { border-width: 1px; padding: 5px; border-style: solid; border-color: black; foreground-color: black; background-color: white}",
        "</style>" | Out-String

if ($Queue = Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | Get-Queue | Where-Object { $_.MessageCount -gt 50 } | Select Identity, MessageCount, Status) { 
    $body = $Queue | ConvertTo-Html -Head $head | Out-String

    Send-MailMessage -Subject "subject test" -Priority High -Body $body -BodyAsHtml @sendMailMessageParams
}
else{
Send-MailMessage -Subject "subject test" -Body "body test "-BodyAsHtml @sendMailMessageParams
}

Open in new window

Could be a rights problem, it can be less than easy to send mail (which you'd think would be the easiest thing).

We should make it log. If Send-MailMessage does anything wrong we'll see it in the log file (path immediately below).
Start-Transcript -Path "C:\Windows\Temp\QueueStatus.log"

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn;
$sendMailMessageParams = @{
    Smtpserver = "servername"
    To         = "abc@abc.com"
    From       = "sender"
}

$head = "<style>",
        "BODY { background-color: white; }",
        "TABLE { border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse; }",
        "TH { border-width: 1px; padding: 5px; border-style: solid; border-color: black; foreground-color: black; background-color: LightBlue}",
        "TD { border-width: 1px; padding: 5px; border-style: solid; border-color: black; foreground-color: black; background-color: white}",
        "</style>" | Out-String

if ($Queue = Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true } | Get-Queue | Where-Object { $_.MessageCount -gt 50 } | Select Identity, MessageCount, Status) { 
    $body = $Queue | ConvertTo-Html -Head $head | Out-String

    Send-MailMessage -Subject "subject test" -Priority High -Body $body -BodyAsHtml @sendMailMessageParams
}
else{
    Send-MailMessage -Subject "subject test" -Body "body test "-BodyAsHtml @sendMailMessageParams
}

Stop-Transcript

Open in new window

i noticed i dont have access to C:\Windows\Temp so changed to to my desktop directory.. and no log was generated in that directory
The service account likely doesn't have permission to write to your desktop. I was going to use $env:TEMP, which would have been the temp directory for the user account running the script. Does your account have access to that?
yes i do have access  to it
Make the first line this:
Start-Transcript -Path "$env:TEMP\QueueStatus.log"

Open in new window

Got his error looks like credential error strange
PS>TerminatingError(): "Active Directory operation failed on . The supplied credential for 'username' is invalid."
Active Directory operation failed on . The supplied credential for
'username' is invalid.
At C:\Users\username\Desktop\test_q.ps1:17 char:5
+ if ($Queue = Get-ExchangeServer | Where { $_.isHubTransportServer -eq $true
} |  ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~
    + CategoryInfo          : NotSpecified: (:) [], ADInvalidCredentialExcepti
   on
    + FullyQualifiedErrorId : [Server=EU2-INFEML-P01,RequestId=30683d1a-f5b9-4
   057-8afb-c300b2d7d499,TimeStamp=24/05/2017 11:39:58] [FailureCategory=Cmdl
  et-ADInvalidCredentialException] 475F71D5

**********************
Windows PowerShell transcript end
End time: 20170524133959

Open in new window

Are you able to log on as the service account and run the script directly? It's quite upset about it's rights...
looks like its upset even for service account. i used service account to run this from scheduler and got same log in $env:TEMP
Could it be Exchange rights? I'm at a bit of a loss as to the cause of that one I'm afraid, many years since I was an Exchange admin.
Ohh np.. i will try to convert into batch and try running or find some other alternative lets see :)