好吧,这可能已经得到了解答,但我是新手并且正在努力学习,所以我想我不会“得到”它.我有一个包含信息表的变量(我从SQL查询中获取).我可以将变量输出到屏幕并查看表格,但是当我尝试在正文中构建
HTML电子邮件时,它会出现乱码.
这是代码：
# This code sets up the HTML table in a more friendly format
$style = "<style>BODY{font-family: Arial; font-size: 10pt;}"
$style = $style + "TABLE{border: 1px solid black; border-collapse: collapse;}"
$style = $style + "TH{border: 1px solid black; background: #dddddd; padding: 5px; }"
$style = $style + "TD{border: 1px solid black; padding: 5px; }"
$style = $style + "</style>"
# This code defines the search string in the IssueTrak database tables
$sqlServer = "Blah"
$sqlDBName = "Blah"
$sqlUsername = "Blah"
$sqlPassword = "Blah"
$sqlQuery = "SELECT u.FirstName,u.LastName,u.EMail,COUNT(UserID) as Count
FROM dbo.Issues i with(nolock)
JOIN DBO.Users u with(nolock) on u.UserID = i.NextActionBy
where i.Status='Open'
group by u.FirstName,u.EMail
order by Count(*) desc"
# This code connects to the sql server and retrieves the data
$sqlConnection = New-Object System.Data.sqlClient.sqlConnection
$sqlConnection.ConnectionString = "Server = $sqlServer; Database = $sqlDBName; uid = $sqlUsername; pwd = $sqlPassword"
$sqlCmd = New-Object System.Data.sqlClient.sqlCommand
$sqlCmd.CommandText = $sqlQuery
$sqlCmd.Connection = $sqlConnection
$sqlAdapter = New-Object System.Data.sqlClient.sqlDataAdapter
$sqlAdapter.SelectCommand = $sqlCmd
$DataSet = New-Object System.Data.DataSet
$sqlAdapter.Fill($DataSet)
$sqlConnection.Close()
# This code outputs the retrieved data
$DataSet.Tables | Format-Table -Auto
# This code emails a report regarding the results of the retrieved data
$smtpServer = "Blah"
$smtpFrom = "Blah"
$smtpTo = "Blah"
$messageSubject = "IssueTrak Open Issues By Next Action Report"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom,$smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Body = "Here is a listing of open issues in IssueTrak,sorted by Next Action.<br><br>"
$message.Body = $message.Body + $html
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
屏幕上的输出如下所示：
35 FirstName LastName EMail Count --------- -------- ----- ----- John Doe John.Doe@blah.com 51 Jane Doe Jane.Doe@blah.com 20
…但是电子邮件正文如下：
Here is a listing of open issues in IssueTrak,sorted by Next Action. Microsoft.PowerShell.Commands.Internal.Format.FormatStartData Microsoft.PowerShell.Commands.Internal.Format.GroupStartData Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
…等等.我究竟做错了什么？
解决方法
这是我如何做到的：
# Create a DataTable
$table = New-Object system.Data.DataTable "TestTable"
$col1 = New-Object system.Data.DataColumn Name,([string])
$col2 = New-Object system.Data.DataColumn Dept,([string])
$table.columns.add($col1)
$table.columns.add($col2)
# Add content to the DataTable
$row = $table.NewRow()
$row.Name = "John"
$row.Dept = "Physics"
$table.Rows.Add($row)
$row = $table.NewRow()
$row.Name = "Susan"
$row.Dept = "English"
$table.Rows.Add($row)
# Create an HTML version of the DataTable
$html = "<table><tr><td>Name</td><td>Dept</td></tr>"
foreach ($row in $table.Rows)
{
$html += "<tr><td>" + $row[0] + "</td><td>" + $row[1] + "</td></tr>"
}
$html += "</table>"
# Send the email
$smtpserver = "smtpserver.domain.com"
$from = "user@domain.com"
$to = "user@domain.com"
$subject = "test"
$body = "Hi there,<br />Here is a table:<br /><br />" + $html
Send-MailMessage -smtpserver $smtpserver -from $from -to $to -subject $subject -body $body -bodyashtml
