locked
Copy the contents from excel spreadsheet to body of email RRS feed

  • Question

  • I need to copy the file to the body of the mail through smtp where the table format changes into text while copying.Kindly help on this issue.I having 5 sheets inside the excel where i need to copy only sheet1 to the body of the mail.The issue i am facing here is only the format of the output which gets changes into text file.Kindly help me

    $email = "someone@somecompany.com"
    $smtpServer = "mail.somecompany.com"
    $ForEmail = @()
    ######################################
    #Create and get my Excel Obj
    $x1 = New-Object -comobject Excel.Application
    $UserWorkBook = $x1.Workbooks.Open("C:\temp\files.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 = "ExcelPaste@somecompany.com"
    $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)

    Sunday, March 11, 2018 6:41 PM

Answers

  • A little research and testing gives us the clean HTML.

    $clipText = Get-Clipboard -TextFormatType Html | select -skip 7 | Out-String


    \_(ツ)_/

    • Marked as answer by LS-01 Tuesday, March 20, 2018 11:19 AM
    Tuesday, March 20, 2018 10:04 AM

All replies

  • You will have to change the Excel data to text or HTML to send it as an email body.


    \_(ツ)_/

    Sunday, March 11, 2018 7:15 PM
  • If you just need to send text using SMTP then this is the easiest way.

    $mailprops = @{
        To = 'jeff@outlook.com'
        From = 'jeff@outlook.com'
        Subject = 'Send Excel Table'
        BodyAsHtml = $true
        Port = 587
        SmtpServer = 'smtp.outlook.com'
        UseSSL = $true
        Credential = Get-Credential jeff@outlook.com
    }
    $xl = New-Object -comobject Excel.Application
    $wb = $xl.Workbooks.Open('d:\test\Book1.xlsx')
    $wb.WorkSheets[1].UserRange.Copy()
    $clp = Get-Clipboard -Format Text |%{$_ + '</br>'} | Out-String
    Send-MailMessage @mailprops -Body $clp

    The PowerShell mail CmdLets is more flexible and handles more conditions that would have to be set in the SMTP mail client.  It is a wrapper around the mail client.

    If you want Excel embedded in a mail body then you will need to use Word, Excel or Outlook to create the embedded Excel sheet.


    \_(ツ)_/

    Sunday, March 11, 2018 10:09 PM
  • You will have to change the Excel data to text or HTML to send it as an email body.


    \_(ツ)_/

    How to do that ?
    Monday, March 12, 2018 12:06 AM
  • If you just need to send text using SMTP then this is the easiest way.

    $mailprops = @{
        To = 'jeff@outlook.com'
        From = 'jeff@outlook.com'
        Subject = 'Send Excel Table'
        BodyAsHtml = $true
        Port = 587
        SmtpServer = 'smtp.outlook.com'
        UseSSL = $true
        Credential = Get-Credential jeff@outlook.com
    }
    $xl = New-Object -comobject Excel.Application
    $wb = $xl.Workbooks.Open('d:\test\Book1.xlsx')
    $wb.WorkSheets[1].UserRange.Copy()
    $clp = Get-Clipboard -Format Text |%{$_ + '</br>'} | Out-String
    Send-MailMessage @mailprops -Body $clp

    The PowerShell mail CmdLets is more flexible and handles more conditions that would have to be set in the SMTP mail client.  It is a wrapper around the mail client.

    If you want Excel embedded in a mail body then you will need to use Word, Excel or Outlook to create the embedded Excel sheet.


    \_(ツ)_/

    Error comes here 

    You cannot call a method on a null-valued expression.
    At line:12 char:1
    + $wb.WorkSheets[1].UserRange.Copy()

    I have changed this line 

    $wb.WorkSheets[1].UsedRange.Copy()

    where the file copies as Text only not as Table format

    • Edited by LS-01 Monday, March 12, 2018 12:18 AM
    Monday, March 12, 2018 12:08 AM
  • You cannot copy as any other type.  Excel clipboard copies are either objects or text.  I you want HTML formatting you have to write the code to create it.

    You can also copy and paste into an Outlook mail item which will copy it as an Excel object which, on a machine with Excel installed, will be an active Excel table with formatting.


    \_(ツ)_/

    Monday, March 12, 2018 12:26 AM
  • You cannot copy as any other type.  Excel clipboard copies are either objects or text.  I you want HTML formatting you have to write the code to create it.

    You can also copy and paste into an Outlook mail item which will copy it as an Excel object which, on a machine with Excel installed, will be an active Excel table with formatting.


    \_(ツ)_/

    How to do that ?Any examples please ?
    Monday, March 12, 2018 12:56 AM
  • Search or [post in Excel developers forum.  There might be come examples.


    \_(ツ)_/

    Monday, March 12, 2018 12:58 AM
  • Here is how to paste in Outlook:

    $ol = New-Object -ComObject Outlook.Application
    $mail = $ol.CreateItem([Microsoft.Office.Interop.Outlook.OlItemType]::olMailItem)
    $insp = $mail.GetInspector
    $we = $insp.WordEditor
    $range = $we.Range()
    $range.Paste()
    



    \_(ツ)_/


    • Edited by jrv Monday, March 12, 2018 1:19 AM
    Monday, March 12, 2018 1:12 AM
  • Here is how to paste in Outlook:

    $ol = New-Object -ComObject Outlook.Application
    $mail = $ol.CreateItem([Microsoft.Office.Interop.Outlook.OlItemType]::olMailItem)
    $insp = $mail.GetInspector
    $we = $insp.WordEditor
    $range = $we.Range()
    $range.Paste()



    \_(ツ)_/


    Actually i need the paste option for smtp mail only because the shift handover will be generated from the machine where no user profile is configured in it.

    Monday, March 12, 2018 1:44 AM
  • Can't be done with SMTP mailers.  You have to nad generate about 500 lines of HTML code to do it with SMTP>


    \_(ツ)_/

    Monday, March 12, 2018 1:48 AM
  • Can't be done with SMTP mailers.  You have to nad generate about 500 lines of HTML code to do it with SMTP>


    \_(ツ)_/

    I have created the html now.How to integrate with the html with this powershell ?

    When i have copied to the powershell i get the error as & cannot be used.Kindly help !!!

    Thursday, March 15, 2018 1:54 PM
  • What HTML?  Just add it as the body of the email.

    Use "Send-MailMessage" and not the old PS! net calls.


    \_(ツ)_/

    Thursday, March 15, 2018 5:22 PM
  • $email = "someone@somecompany.com"
    $smtpServer = "mail.somecompany.com"
    $ForEmail = @()
    ######################################
    #Create and get my Excel Obj
    $x1 = New-Object -comobject Excel.Application
    $UserWorkBook = $x1.Workbooks.Open("C:\temp\files.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 = "ExcelPaste@somecompany.com"
    $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)

    How to add here ?Now I have a HTML which creates the table and the data should be copied inside the table this is the requirement here 

    Saturday, March 17, 2018 10:06 AM
  • You are just asking the same question that was answered above.

    If you want an HTML table you have to write the code to create the HTML.  Look in the Gallery for examples of how to create reports from Excel.


    \_(ツ)_/

    Saturday, March 17, 2018 10:09 AM
  • You are just asking the same question that was answered above.

    If you want an HTML table you have to write the code to create the HTML.  Look in the Gallery for examples of how to create reports from Excel.


    \_(ツ)_/

    I have the HTML code which i have copied in the powershell where I get the below error I have pasted $ForEmail 

    The ampersand (&) character is not allowed. The & operator is reserved for future use; wrap an ampersand in double quotation marks ("&") to pass it as part of a string.
    Not all parse errors were reported. Correct the reported errors and try again.
        + CategoryInfo         : ParserError: (:) [], ParentContainsErrorRecordException
        + FullyQualifiedErrorId : UnexpectedToken 

    Saturday, March 17, 2018 10:34 AM
  • What code are you asking about?


    \_(ツ)_/

    Saturday, March 17, 2018 10:58 AM
  • What code are you asking about?


    \_(ツ)_/

    HTML code  inside the powershell where I refer some links for the numbers which are generated For that & symbol is coming inside HTML code
    Saturday, March 17, 2018 11:11 AM
  • No idea what you are talking about.  What HTML code.  There is no HTML code.


    \_(ツ)_/

    Saturday, March 17, 2018 11:14 AM
  • No idea what you are talking about.  What HTML code.  There is no HTML code.


    \_(ツ)_/

    I copy the data to the mail body with Clipboard only where i need the raw data(table) which is present as such inside the excel is needed.But I am unable to get the data here as such in the Excel sheet.Kindly suggest on this 

    $Results = $Results = Get-Clipboard

    The above command needs changes .I have tried "Get-Clipboard -TextFormatType Html" which is not working

    I have created a HTML code as such the table which is inside the excel.I dont know how to represent that html here ?

    Saturday, March 17, 2018 4:45 PM
  • You have to write the code to convert the excel cells to html.  YOU cannot copy the Excel to the clipboard.  Excel does not put the table on the clipboard in HTML.   It just puts text on the clipboards and a special data object.


    \_(ツ)_/

    Saturday, March 17, 2018 6:51 PM
  • it can be done in .vb script or by powershell itself?
    Sunday, March 18, 2018 7:26 AM
  • You have to write the code to convert the excel cells to html.  YOU cannot copy the Excel to the clipboard.  Excel does not put the table on the clipboard in HTML.   It just puts text on the clipboards and a special data object.


    \_(ツ)_/

    I have copied the table through Clipboard but the timestamp is coming with the body of the table ,How to get rid of this ? This is shown above the table?

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

    Sunday, March 18, 2018 6:14 PM
  • Skip the unwanted lines.

    $Lines | select -skip 5


    \_(ツ)_/

    Sunday, March 18, 2018 7:31 PM
  • Skip the unwanted lines.

    $Lines | select -skip 5


    \_(ツ)_/

    $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","I32")

    $rgeSource.Copy() | out-null

    $Results = Get-Clipboard -TextFormatType Html

    $message = @{

    From = 'user1@domain.com’

    To = 'user2@domain.com'

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


    BodyAsHtml = $true

    }


    $Server = @{

    smtp = 'smtp.domain.com’

    }


    $message.Body= "This is the test mail

    <br>

    $Results

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

    "

    Send-MailMessage @message @Server

    Where to use this above mentioned line here since i have tried where the timestamp or logs are coming above the tabular column only!

     
    Monday, March 19, 2018 8:07 AM
  • Hi,

    According to your situation, you may need to use <table>, <tr>, <td> to create a HTML table and add -BodyAsHtml parameter for Send-MailMessage cmdlet to indicate that the value of the Body parameter contains HTML. The following example and link for your reference:
    https://www.w3schools.com/html/html_tables.asp

    $body = @"
    <table>
        <tr>
            <td>$dataFromExcel</td>
            <td>$dataFromExcel</td>
        </tr>
        <tr>
            <td>$dataFromExcel</td>
            <td>$dataFromExcel</td>
        </tr>
    </table>
    "@

    Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, March 20, 2018 9:54 AM
  • A little research and testing gives us the clean HTML.

    $clipText = Get-Clipboard -TextFormatType Html | select -skip 7 | Out-String


    \_(ツ)_/

    • Marked as answer by LS-01 Tuesday, March 20, 2018 11:19 AM
    Tuesday, March 20, 2018 10:04 AM
  • A little research and testing gives us the clean HTML.

    $clipText = Get-Clipboard -TextFormatType Html | select -skip 7 | Out-String


    \_(ツ)_/

    Thank you so much its done
    Tuesday, March 20, 2018 11:20 AM
  • hello

    I used this code. It is same as yours but

    $x1 = New-Object -comobject Excel.Application
    
    $UserWorkBook = $x1.Workbooks.Open("G:\utkarsh-automation-script\statuses\best\test_excel-final.xlsx")
    
    $UserWorksheet = $UserWorkBook.Worksheets.Item(1)
    
    $UserWorksheet.activate()
    
    $rgeSource=$UserWorksheet.range("A3","E6")
    
    $rgeSource.Copy() | out-null
    
    $Results = Get-Clipboard -TextFormatType Html | select -skip 7 | Out-String
    
    
    $From = 'automation-gdw@apmea.mcd.com’
    
    $To = 'utkarsh.srivastava@capgemini.com'
    
    $Subject = "Test Mail -$(Get-Date)"
    
    
    $SMTPServer = 'klsmtprelay.mcd.com’
    
    $Body= "This is the test mail
    
    <br>
    
    $Results
    
    <br>
    Regards,
    <br>
    Username
    <br>
    
    "
    
    Send-MailMessage -From $From -to $To -Subject $Subject -BodyAsHtml $Body   -SmtpServer $SMTPServer

    i get this error message: 

    Get-Clipboard : The term 'Get-Clipboard' is not recognized as the name 
    of a cmdlet, function, script file, or operable program. Check the 
    spelling of the name, or if a path was included, verify that the path is 
    correct and try again.
    At C:\Users\mc54378\AppData\Local\Temp\2\78860306-83c9-40da-a376-03c1e79e
    6843.ps1:13 char:12
    + $Results = Get-Clipboard -TextFormatType Html | select -skip 7 | 
    Out-String
    +            ~~~~~~~~~~~~~
        + CategoryInfo          : ObjectNotFound: (Get-Clipboard:String) [], 
        CommandNotFoundException
        + FullyQualifiedErrorId : CommandNotFoundException

    Seems like Get-Clipboard is no longer supported by powershell. Is there any other alternative I could use?

    Thanks.

    Thursday, May 10, 2018 7:01 AM
  • Upgrade PowerShell to WMF 5.1 for that CmdLet.


    \_(ツ)_/

    Thursday, May 10, 2018 7:09 AM