How to import a text file into powershell and email it, formatted as HTML

Question

I'm trying to get a list of all Exchange accounts, format them in descending order from largest mailbox and put that data into an email in HTML format to email to myself. So far I can get the data, push it to a text file as well as create an email and send to myself. I just can't seem to get it all put together. I've been trying to use ConvertTo-Html but it just seems to return data via email like "pageFooterEntry" and "Microsoft.PowerShell.Commands.Internal.Format.AutosizeInfo" versus the actual data. I can get it to send me the right data if i don't tell it to ConvertTo-Html, just have it pipe the data to a text file and pull from it, but it's all ran together with no formatting. I don't need to save the file, i'd just like to run the command, get the data, put it in HTML and mail it to myself. Here's what I have currently:

#Connects to Database and returns information on all users, organized by Total Item Size, User
$body = Get-MailboxStatistics -database "Mailbox Database 0846468905" | where {$_.ObjectClass -eq “Mailbox”} | Sort-Object TotalItemSize -Descending | ft @{label=”User”;expression={$_.DisplayName}},@{label=”Total Size (MB)”;expression={$_.TotalItemSize.Value.ToMB()}}  -auto | ConvertTo-Html

#Pause for 5 seconds for Exchange 
write-host -foregroundcolor Green "Pausing for 5 seconds for Exchange"
Start-Sleep -s 5


$toemail = "me@example.com" # Emails report to this address.
$fromemail = "me@example.com" #Emails from this address.
$server = "Exchange.company.com" #Exchange server - SMTP.


#Email the report.
$email = New-Object System.Net.Mail.MailMessage
$email.IsBodyHtml = $True
$email.To.Add($toemail)
$email.From = $fromemail
$email.Subject = "Exchange Mailbox Sizes"
$email.Body = $body
$client = New-Object System.Net.Mail.SmtpClient $server
$client.UseDefaultCredentials = $true
$client.Send($email)

Any thoughts would be helpful, thanks!

score 1 · Accepted Answer · edited Apr 07 '12 at 04:31

1

you can try mine which works for me. You need to set all the variables, which should all self-documenting.

$body = "$(cat $file)"

send-MailMessage -SmtpServer $smtpserver -To $to -From $from -Subject $subject -Body $body -BodyAsHtml -Priority high

edited Apr 07 '12 at 04:31

Wesley

32,690
9
82
117

answered Apr 06 '12 at 20:28

johnshen64

5,865
24
17

Thanks for the response! What exactly is "$(cat $file)" doing? I'm just curious how I get the output from the get-mailbox into that body, would I need to save it to a file first, then set the $body = "$(savedfile)" ? I'm familiar with setting the variable with the $, but not so much with $(word$word). Just curious what it's trying to do. Thanks for the clarification! – Don Apr 09 '12 at 13:31
Ahh, I see, the cat command is a built-in command. I have it where it sends the email now, so the only thing left is to fix the formatting (all of the content is bunched together versus being in two columns). I'll continue to research that, but if you have any suggestions for formatting, let me know. Thanks again! – Don Apr 09 '12 at 13:59
formatting is html so you need somehow convert your file into html. there are scripts available to do some basic formatting, such as inserting p tag for double line etc. – johnshen64 Apr 09 '12 at 15:22

score 0 · Answer 2 · answered Apr 09 '12 at 18:36

Thanks for your help above. What you suggested worked fine for me in part, but I had some other formatting issues that I ended up figuring out as well. Just to help the next person looking I'm posting my end result/configuration as well. It may not be as efficient as it could be but it seems to work for what I wanted! Thanks again!

    #Connects to Database and returns information on all users, organized by DisplayName, ItemCount, TotalItemSize
    $body = Get-MailboxStatistics -database "Mailbox Database 0846468905" | where {$_.ObjectClass -eq “Mailbox”} | Sort-Object TotalItemSize -Descending  | ConvertTo-Html -property DisplayName, ItemCount, TotalItemSize

    #Pause for 3 seconds for Exchange to write the file.
    Start-Sleep -s 3

    $toemail = "me@example.com" #Emails report to this address.
    $fromemail = "me@example.com" #Emails report from this address.
    $server = "Exchange.company.com" #Exchange server - SMTP.

    $HTMLmessage = @"
    <font color=""black"" face=""Arial, Verdana"" size=""3"">
    <br>
    <body BGCOLOR=""white"">
    $body
    </body>
    "@ 

    #Email the report.
    Send-MailMessage -smtpServer $server -to $toemail -from $fromemail -subject "Exchange Mailbox Sizes" -body $HTMLmessage -BodyAsHtml -priority High

How to import a text file into powershell and email it, formatted as HTML

2 Answers2