Powershell Integration Help Needed



  • 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)
    


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





  • 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



  • How to get rid of this error ??

    Error is provided here :

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



  • This sorted out

    clipboard | select skip -7| out-string


Log in to reply