Powershell Integration Help Needed

Lakshmana

I have already asking the help for the Powershell to send the mail through SMTP with the excel content.Now i have made .html which creates a Table where the data needs to be copied inside the table.When i copy the html code which through the error for &,since the body is in HTML format which is not accepting.Any suggestions.Whether the html code path can be given if yes how to proceed.HTML code is 2000 lines where gives the proper output of the table

$email = "[email protected]"
$smtpServer = "mail.somecompany.com"
$ForEmail = @()
######################################
#Create and get my Excel Obj
$x1 = New-Object -comobject Excel.Application
$UserWorkBook = $x1.Workbooks.Open("C:\temp\bluenose.xlsx")

#Select first Sheet
$UserWorksheet = $UserWorkBook.Worksheets.Item(1)
$UserWorksheet.activate()

#Copy the part of the sheet I want in the Email
$rgeSource=$UserWorksheet.range("A1","E20")
$rgeSource.Copy() | out-null

$Results = Get-Clipboard
foreach ($Result in $Results)
{
    $ForEmail += "$Result<br>"
}

######################################

$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)

$msg.From = "[email protected]"
$msg.To.Add($email)
$msg.Subject = "Excel Pasted"
$msg.IsBodyHtml = $True

$msg.Body = "Here is the contents of the excel file<br>
<br>
$ForEmail
<br>
"
$smtp.Send($msg)

Lakshmana

Any other option is there i can use by replacing Get-Clipboard option

black3dynamite

Use Get-Content.

https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.management/get-content?view=powershell-6

Lakshmana

The table got copied to the body of the mail through this Powershell where the timestamp or logs comes inside the body which is present above the table.How to get rid of that ??

Error is provided here :

Version:1.0 StartHTML:0000000159 EndHTML:0000053673 StartFragment:0000005518 EndFragment:0000053621 SourceURL

Powershell Code Working One

[$x1 = New-Object -comobject Excel.Application

$UserWorkBook = $x1.Workbooks.Open("C:\Location\file.xlsx")

$UserWorksheet = $UserWorkBook.Worksheets.Item(1)

$UserWorksheet.activate()

$rgeSource=$UserWorksheet.range("A1","E32")

$rgeSource.Copy() | out-null

$Results = Get-Clipboard -TextFormatType Html

$message = @{

From = '[email protected]’

To = '[email protected]'

Subject = "Test Mail -$(Get-Date)"

BodyAsHtml = $true

}

$Server = @{

smtp = 'smtp.domain.com’

}

$message.Body= "

<br>

$Results

<br>
Regards,
<br>
Username
<br>

"

Send-MailMessage @message @Server

Lakshmana

How to get rid of this error ??

Error is provided here :

Version:1.0 StartHTML:0000000159 EndHTML:0000053673 StartFragment:0000005518 EndFragment:0000053621 SourceURL

Lakshmana

This sorted out

clipboard | select skip -7| out-string