locked
PowerShell Outlook.Application: Send Email containing Text + Excel copied speadsheet RRS feed

  • Question

  • Hi,

    I want to send an email contaning: Text + Excel (copy past spreadsheet part)

    I succeed to  display text or excel but not both. 

    I'm not sure how the function Getinspector.WordEditor.Range().Paste() is working

    and how to manipulate the Body function. 

    #Create and get my Excel Obj
    $x1 = New-Object -comobject Excel.Application
    $UserWorkBook = $x1.Workbooks.Open("C:\MyExcelFile.xls")
    
    #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
    
    #create outlook Object
    $Outlook = New-Object -comObject  Outlook.Application 
    $Mail = $Outlook.CreateItem(0) 
    $Mail.Recipients.Add("Myname@Mydomain.com") 
    
    #Add the text part I want to display first
    $Mail.Body = "My Comment on the Excel Spreadsheet" 
    
    #Then Copy the Excel 
    $Mail.Getinspector.WordEditor.Range().Paste()
    $Mail.Display()



    Wednesday, March 13, 2013 10:00 AM

Answers

  • I've got 1 answer working

    #Create and get my Excel Obj
    $x1 = New-Object -comobject Excel.Application
    $UserWorkBook = $x1.Workbooks.Open("C:\MyExcelFile.xls")
    
    #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").Copy()
    
    
    #create outlook Object
    $Outlook = New-Object -comObject  Outlook.Application 
    $Mail = $Outlook.CreateItem(0) 
    $Mail.Recipients.Add("Myname@Mydomain.com") 
    
    #Add the text part I want to display first
    $Mail.Body = "My Comment on the Excel Spreadsheet" 
    
    #Then Copy the Excel using parameters to format it
    $Mail.Getinspector.WordEditor.Range().PasteExcelTable($true,$false,$false)
    #Then it becomes possible to insert text before
    $wdDoc = $Mail.Getinspector.WordEditor
    $wdRange = $wdDoc.Range()
    $wdRange.InsertBefore("Hello world!")
    $Mail.Display()
    • Marked as answer by cyril frelet Thursday, March 14, 2013 7:34 AM
    Thursday, March 14, 2013 7:34 AM

All replies

  • I've got 1 answer working

    #Create and get my Excel Obj
    $x1 = New-Object -comobject Excel.Application
    $UserWorkBook = $x1.Workbooks.Open("C:\MyExcelFile.xls")
    
    #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").Copy()
    
    
    #create outlook Object
    $Outlook = New-Object -comObject  Outlook.Application 
    $Mail = $Outlook.CreateItem(0) 
    $Mail.Recipients.Add("Myname@Mydomain.com") 
    
    #Add the text part I want to display first
    $Mail.Body = "My Comment on the Excel Spreadsheet" 
    
    #Then Copy the Excel using parameters to format it
    $Mail.Getinspector.WordEditor.Range().PasteExcelTable($true,$false,$false)
    #Then it becomes possible to insert text before
    $wdDoc = $Mail.Getinspector.WordEditor
    $wdRange = $wdDoc.Range()
    $wdRange.InsertBefore("Hello world!")
    $Mail.Display()
    • Marked as answer by cyril frelet Thursday, March 14, 2013 7:34 AM
    Thursday, March 14, 2013 7:34 AM
  • Thanks for sharing the Solution......I was facing similar issue.

    Regards


    Knowledge is Power{Shell}.

    Friday, December 27, 2013 6:58 AM
  • This is all you have to do to send a worksheet as an embedded mail body.

    $sheet.UsedRange.Parent.MailEnvelope.Item.To='joe@domain.com'
    $sheet.UsedRange.Parent.MailEnvelope.Item.Subject='Here is my worksheet'
    $sheet.UsedRange.Parent.MailEnvelope.Item.Send()


    ¯\_(ツ)_/¯


    • Edited by jrv Sunday, February 8, 2015 4:56 AM
    Sunday, December 29, 2013 8:06 AM
  • Sorry - I forgot to add the attachment code which is very complex.

    $workbook.SendMail('jsmith@dom.com',' This is my workbook')

    That is it.  Just one line. You can also just send a worksheet and you can easily add other attachments. No need to start Outlook or to use any other method. Remember that Office is integrated and automation is about using all Office products as an integrated suite.


    ¯\_(ツ)_/¯


    • Edited by jrv Sunday, February 8, 2015 4:57 AM
    Sunday, December 29, 2013 8:16 AM
  • Thanks Jrv.....that is so cool.

    But am trying to copy the contents from 2 different excel sheets and put them in a mail.
    Good to know the above way.. :)
    Regards


    Knowledge is Power{Shell}.

    Sunday, December 29, 2013 1:09 PM
  • Thanks Jrv.....that is so cool.

    But am trying to copy the contents from 2 different excel sheets and put them in a mail.
    Good to know the above way.. :)
    Regards


    Knowledge is Power{Shell}.

    Please start you own thread if you have a question.  This thread has been closed.  I just added this because no one knows the correct way to use Office mail.  You can send as many attachments as you want.  There are many facilities for mailing in all Office applications.  Each is customized to the type of document.


    ¯\_(ツ)_/¯

    Sunday, December 29, 2013 1:15 PM
  • I'm a little new to the whole Power Shell Script and this Script works great.  However, I have been trying to manipulate it to where it copies the content of a Windows Word document and place that content into an Outlook email.  Any help would be great appreciated.

    Sunday, February 8, 2015 2:36 AM
  • Please start you own thread.  THis thread has been closed for years.

    ¯\_(ツ)_/¯

    Sunday, February 8, 2015 4:50 AM