• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

Powershell - How to format an email

Hi guys, I have 3 arrays containing values which were read from an access DB.

I have the below code to send the email:

$body = @"
Hi, please see the list below for the result.

PC Name          Date          State
-------------   -------        -------
$PCnames
$Date
$State

You will receive further updates soon.
"@

Send-MailMessage -From $From -to $To -Subject "Test" `
-Body $body -SmtpServer $SMTPServer -port $SMTPPort -UseSsl `
-Credential $cred

Open in new window


The problem is, the email looks like a mess. Below is the result;
Hi, please see the list below for the result.

PC Name          Date          State
-------------          -------          -------
PC1 PC2 PC3
1/01/2015 1/01/2015 1/01/2015
Online Online Offline

You will receive further updates soon.

The result I'm trying to get is:
Hi, please see the list below:

PC Name          Date                   State
-------------        -------------            ----------
PC1                1/01/2015           Online
PC2                1/01/2015           Online
PC3                1/01/2015           Offline <-- If possible any "offline" should be red

You will receive further updates soon.

I'm assuming this will need to be in html? But I have no idea how to make it format like this.
0
amaru96
Asked:
amaru96
  • 11
  • 8
  • 6
2 Solutions
 
Neil RussellTechnical Development LeadCommented:
You can add the -BodyAsHtml parameter and then use HTML tags to properly format your email body. Use tables to correctly align your tabular data etc.

Happy new Year
0
 
amaru96Author Commented:
How do I get the values of the array to show like that? Instead of all on one line.
0
 
Neil RussellTechnical Development LeadCommented:
give me a few minutes....
0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
footechCommented:
The way you're trying to create the body text is problematic.  It appears that you're trying to insert the content of a few arrays into a string, and if you follow that method it is difficult to change how those arrays are inserted.

It's hard to give advice (in particular, code) when I can't see how $PCnames,$Date, and $State were populated/created, but instead of those three objects you should create a single object for each PC with PCName, Date, and State as properties.  That object can be output as a plain-text table or piped to ConvertTo-HTML to create HTML text for use in an email.
0
 
amaru96Author Commented:
Hi footech, how do I go about creating that object to contain those properties?

Below is the code I'm using to populate the arrays.

$objRecordset.MoveFirst()
while (!($objRecordset.EOF))
{
         $PCs_in_array += $objRecordSet.Fields.Item("name").Value
       $State_in_array += $objRecordSet.Fields.Item("State").Value
       $DateTime_in_array += $objRecordSet.Fields.Item("Date_Time").Value      
   $objRecordset.MoveNext()
}
0
 
Neil RussellTechnical Development LeadCommented:
$a = "<style>"
$a = $a + "BODY{font-family: Verdana, Arial, Helvetica, sans-serif;font-size:10;font-color: #000000}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color: #D2B48C}"
$a = $a + "TD{border-width: 1px;padding: 2px;border-style: solid;border-color: black}"
$a = $a + "</style>"

$Objects = Get-ChildItem -Path "C:\temp"
$BodyText =$a
$BodyText += "<H2>please see the list below:<H2>"
$BodyText += "<table><th>PC NAME</th><th>Date</th><th>State</th>"

foreach ($Obj in $Objects )
{
	$BodyText += "<tr><td>$($obj.name)</td><td>$($obj.LastWriteTime)</td>"
	if ($Obj.Mode -eq "-a---")
	{
		$BodyText += "<td bgcolor=""#FFEFD5"">$($obj.Mode)</td></tr>"
	}
	else
	{
		$BodyText += "<td bgcolor=""RED"">$($obj.Mode)</td></tr>"
	
	}

}
$BodyText +="</table>"
$BodyText |  Out-File -FilePath c:\temp\output.html 

Open in new window

0
 
footechCommented:
This might be incorrect since I don't work with Access, but I believe it might work.
$custom = @()
$objRecordset.MoveFirst()
while (!($objRecordset.EOF))
{
    $custom += New-Object PsObject -Property @{
                PCName = $objRecordSet.Fields.Item("name").Value
                State = $objRecordSet.Fields.Item("State").Value
                Date = $objRecordSet.Fields.Item("Date_Time").Value      
                }
    $objRecordset.MoveNext()
}
$custom

Open in new window

$custom should be an array of objects which have those properties.
0
 
Neil RussellTechnical Development LeadCommented:
The above gives you a working example.

Now just change the references to $obj to patch in your data.
Can you do this?
0
 
Neil RussellTechnical Development LeadCommented:
Adding footech's code to mine you get....

$custom = @()
$objRecordset.MoveFirst()
while (!($objRecordset.EOF))
{
    $custom += New-Object PsObject -Property @{
                PCName = $objRecordSet.Fields.Item("name").Value
                State = $objRecordSet.Fields.Item("State").Value
                Date = $objRecordSet.Fields.Item("Date_Time").Value      
                }
    $objRecordset.MoveNext()
}

$a = "<style>"
$a = $a + "BODY{font-family: Verdana, Arial, Helvetica, sans-serif;font-size:10;font-color: #000000}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color: #D2B48C}"
$a = $a + "TD{border-width: 1px;padding: 2px;border-style: solid;border-color: black}"
$a = $a + "</style>"

$BodyText =$a
$BodyText += "<H2>please see the list below:<H2>"
$BodyText += "<table><th>PC NAME</th><th>Date</th><th>State</th>"

foreach ($Obj in $custom )
{
	$BodyText += "<tr><td>$($obj.name)</td><td>$($obj.Date_Time)</td>"
	if ($Obj.Status -eq "Offline")
	{
		$BodyText += "<td bgcolor=""RED"">$($obj.State)</td></tr>"
	}
	else
	{
		$BodyText += "<td bgcolor=""#FFEFD5"">$($obj.State)</td></tr>"
	
	}

}
$BodyText +="</table>"
$BodyText |  Out-File -FilePath c:\temp\output.html 

Open in new window


Play with that until your output.html file looks like what you want then use send-mailmessage to send
0
 
amaru96Author Commented:
Footech, the result of $custom looks good, however how do I place that within the email body?

Neilsr, sorry mate, I'm not sure what you want me to do.
0
 
Neil RussellTechnical Development LeadCommented:
See my last post.  That should give you a finished email that is in c:\temp\output.html with formatting.

Adjust html formatting code to suite colours etc and once happy add last line....

Send-MailMessage -From $From -to $To -Subject "Test" `
-Body $bodyText -SmtpServer $SMTPServer -port $SMTPPort -UseSsl `
-Credential $cred -BodyAsHtml

Open in new window

0
 
footechCommented:
There are a few property references in that code which don't match up with property names I coded.
0
 
amaru96Author Commented:
Neil, the file (output.html) doesn't contain any data. I see the headings but that's all.

2015-01-01-22-17-51-.png
0
 
Neil RussellTechnical Development LeadCommented:
$custom = @()
$objRecordset.MoveFirst()
while (!($objRecordset.EOF))
{
    $custom += New-Object PsObject -Property @{
                PCName = $objRecordSet.Fields.Item("name").Value
                State = $objRecordSet.Fields.Item("State").Value
                Date = $objRecordSet.Fields.Item("Date_Time").Value      
                }
    $objRecordset.MoveNext()
}

$a = "<style>"
$a = $a + "BODY{font-family: Verdana, Arial, Helvetica, sans-serif;font-size:10;font-color: #000000}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color: #D2B48C}"
$a = $a + "TD{border-width: 1px;padding: 2px;border-style: solid;border-color: black}"
$a = $a + "</style>"

$BodyText =$a
$BodyText += "<H2>please see the list below:<H2>"
$BodyText += "<table><th>PC NAME</th><th>Date</th><th>State</th>"

foreach ($Obj in $custom )
{
	$BodyText += "<tr><td>$($obj.PCname)</td><td>$($obj.Date)</td>"
	if ($Obj.Statu -eq "Offline")
	{
		$BodyText += "<td bgcolor=""RED"">$($obj.State)</td></tr>"
	}
	else
	{
		$BodyText += "<td bgcolor=""#FFEFD5"">$($obj.State)</td></tr>"
	
	}

}
$BodyText +="</table>"
$BodyText |  Out-File -FilePath c:\temp\output.html 

Open in new window

0
 
Neil RussellTechnical Development LeadCommented:
I think it is New Year Head :D

Try the revised code above.

Excuse me asking but we have no clues,  what is your level of experience with Powershell and with HTML?
0
 
amaru96Author Commented:
Footech, yeh I should of seen that, sorry guys - must be that new year head :-)

Neil, thanks for your time and effort to get it looking that way I want, really appreciate it.

And sorry to ask, but just out of curiosity (as my experience with PS is pretty low), if I wanted to use $custom within the email body, how would I do that?
0
 
Neil RussellTechnical Development LeadCommented:
$custom should now be show in your table output that i gave you.

I dont understand the question

And I was on about My new year head :D
0
 
amaru96Author Commented:
Neil, yes it is in the output file and my original question has been answered, but I was just wondering if it were possible to include "$custom" directly into the email body itself (without the HTML formatting).

Something like:
Send-MailMessage -From $From -to $To -Subject "Test" `
-Body $custom -SmtpServer $SMTPServer -port $SMTPPort -UseSsl `
-Credential $cred -BodyAsHtml
0
 
Neil RussellTechnical Development LeadCommented:
$bodytext = $custom | convert-tohtml


Give it a try
0
 
amaru96Author Commented:
Send-MailMessage : Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'Body'. Specified method is not supported
0
 
footechCommented:
One more correction to line 27.  Should be
if ($Obj.State -eq "Offline")

Open in new window


:) I can spot problems in other people's code pretty reliably, but when it comes to my own I'm not always so good.

By way of explanation, Neilsr's code processes the objects in the $custom array one by one so that it can create the HTML code.  This is the only way I know of doing it so that "Offline" would be in red.   Essentially it requires you to specify the HTML code in a more manual fashion.  You could just process $custom automatically, but the HTML generated will be a lot more constrained.  For example:
$head = @"
<style>
TABLE{border-width: 2px;border-style: solid;border-color: black;border-collapse: collapse;}
TH{border-width: 2px;padding: 4px;border-style: solid;border-color: black;background-color:lightblue;text-align:left;font-size:14px}
TD{border-width: 1px;padding: 4px;border-style: solid;border-color: black;font-size:12px}
</style>
"@
Send-MailMessage -From $From -to $To -Subject "Test" `
-Body ($custom | ConvertTo-HTML -head $head | Out-String) -SmtpServer $SMTPServer -port $SMTPPort -UseSsl `
-Credential $cred -BodyAsHtml

Open in new window

0
 
Neil RussellTechnical Development LeadCommented:
amaru96
Where? That means nothing unless you also show the command that caused it.
0
 
footechCommented:
Without doing HTML, you could use the following:
Send-MailMessage -From $From -to $To -Subject "Test" `
-Body ($custom | Out-String) -SmtpServer $SMTPServer -port $SMTPPort -UseSsl `
-Credential $cred

Open in new window

0
 
amaru96Author Commented:
Footech, yeh I picked that up too, but thanks - and for the explanation.

Neil, that error appeared when I tried;
$bodytext = $custom | convertto-html

Send-MailMessage -From $From -to $To -Subject "test" `
-Body $bodytext -BodyAsHtml -SmtpServer $SMTPServer -port $SMTPPort -UseSsl `
-Credential $cred

Open in new window

The below worked, although the result doesn't really line up the columns very well. But anyway, that was just for my curiosity.
Send-MailMessage -From $From -to $To -Subject "Test" `
-Body ($custom | Out-String) -SmtpServer $SMTPServer -port $SMTPPort -UseSsl `
-Credential $cred

Open in new window


Thanks again guys, really appreciate your efforts to help me and all the best for the new year!
0
 
footechCommented:
The reason the last one doesn't line up for you is because your email client isn't using a fixed-width font for plain text emails.  Change that and it will.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now