Solved

Powershell - How to format an email

Posted on 2015-01-01
25
143 Views
Last Modified: 2015-01-01
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
Comment
Question by:amaru96
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 6
25 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526440
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
 
LVL 1

Author Comment

by:amaru96
ID: 40526449
How do I get the values of the array to show like that? Instead of all on one line.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526461
give me a few minutes....
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:footech
ID: 40526462
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
 
LVL 1

Author Comment

by:amaru96
ID: 40526466
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526469
$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
 
LVL 40

Assisted Solution

by:footech
footech earned 100 total points
ID: 40526472
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526473
The above gives you a working example.

Now just change the references to $obj to patch in your data.
Can you do this?
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526474
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
 
LVL 1

Author Comment

by:amaru96
ID: 40526478
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526479
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
 
LVL 40

Expert Comment

by:footech
ID: 40526482
There are a few property references in that code which don't match up with property names I coded.
0
 
LVL 1

Author Comment

by:amaru96
ID: 40526484
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
 
LVL 37

Accepted Solution

by:
Neil Russell earned 400 total points
ID: 40526487
$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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526488
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
 
LVL 1

Author Comment

by:amaru96
ID: 40526489
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526491
$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
 
LVL 1

Author Comment

by:amaru96
ID: 40526495
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526498
$bodytext = $custom | convert-tohtml


Give it a try
0
 
LVL 1

Author Comment

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

Expert Comment

by:footech
ID: 40526504
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40526505
amaru96
Where? That means nothing unless you also show the command that caused it.
0
 
LVL 40

Expert Comment

by:footech
ID: 40526509
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
 
LVL 1

Author Comment

by:amaru96
ID: 40526521
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
 
LVL 40

Expert Comment

by:footech
ID: 40526539
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question