Solved

Powershell - How to format an email

Posted on 2015-01-01
25
136 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
  • 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
 
LVL 39

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 39

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 39

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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 39

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 39

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 39

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hi all.   The other day I had to change the passwords for a bunch of users on the fly. Because they were so many, I decided to do it in an automated way and I would like to share it with you all.   If you are not doing it directly in a Domain Co…
Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now