I'm looking to take the first worksheet off of an Excel workbook and embed it into the body of an email.
The problem I'm running into is that the first half of my code won't export the worksheet into a nice, single .htm file (which you can do in Excel) and thus, doesn't embed properly. Another member suggested exporting to a single .mhtml file, but I found that opens another can of worms when trying to get it to embed into the body of an email.
I'm willing to tear this all apart and start over, however, if I use a single .htm file from a "Save As" from within Excel, it works perfectly.
Thanks in advance all.
##### Excel Stuff Here ################
#Create and get my Excel Obj
$excel = New-Object -comobject Excel.Application
$excel.visible=$false
$excel.DisplayAlerts=$false
$UserWorkBook = $excel.Workbooks.Open("e:hitlisthitlist.xlsx")
#Select first Sheet
$UserWorksheet = $UserWorkBook.Worksheets.Item(1)
#HitList File and type
$hitlist = "E:HitListHitlist.htm"
$xlHtml = 44
#Save, close, and clean up
$UserWorksheet.SaveAs($hitlist,$xlHtml)
$UserWorkBook.close()
$excel.quit()
$excel = $null
###### Mail Stuff Here ###############
#Get the date
$date = Get-Date
#Prep the mail details
$From = "HitList <[email protected]>"
$To = "[email protected]"
$Subject = "Here's your Hit List for " + $date.DayOfWeek + ", " + $date.ToShortDateString()
$SMTPServer = "smtpserver.com"
#this works if I have it as .htm
$Body = Get-Content E:HitListHitList.htm -Raw
#Send the mail
Send-MailMessage -From $From -to $To -Subject $Subject -Body $Body -BodyAsHtml -SmtpServer $SMTPServer
#end