Copy from excel paste to email body

[Jewtus]

I have been trying to figure out the command/function for pasting the data and formatting from Excel to outlook. I originally was using readtorange, but that strips the formatting, so I started resorting to VB and I cannot seem to figure out what the paste command is. This is what I have:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = False
$oBook=$oExcel.WorkBooks.Open ($fExcel)
$Sheet=$oExcel.ActiveWorkbook.Worksheets.Item($sSheetname)
$test=$Sheet.Range("A1:D60").Copy

$olApp = ObjCreate("Outlook.Application")
$olMailItem = 0
$objMail = $olApp.CreateItem($olMailItem)
$objMail.Save
$objMail.To =("Test@Test.com")
$objMail.Subject = ("Testing")
;~ $objMail.Body.Selection.Paste
$objMail.Display

$oBook.Close(False)
$oExcel.Quit

I have tried CTRL+V on the window, and the data is in the clipboard, but short of sending a CTRL+V command, I cannot figure out how to paste it into the body. Can someone point me to the right VBA function?

[Jfish]

This thread may help with the paste and some ideas on formatting (OutlookEX UDF for templates): https://www.autoitscript.com/forum/topic/166385-outlook-message-clip-put-paste-tables/?page=1

 

[Juvigy]

Check you this topic:

Also you may go with exporting the excel data to htm file and then importing it to outlook.

Or what i did

$oOApp = ObjCreate("Outlook.Application")
    $oOMail = $oOApp.CreateItem ($olMailItem)
    $oDoc = $oOMail.GetInspector.WordEditor
    $oRange = $oDoc.Range
    $oRange.Collapse($wdCollapseStart)
    $oRange.InsertParagraph
    $oRange.InsertBefore("Dear All,")
    $oRange.InsertParagraphAfter()
    $oRange.InsertAfter("Some text")
    $oRange.InsertParagraphAfter()
    $oRange.InsertAfter("Some text2")
    $oRange.InsertParagraphAfter()
    $oRange.move(4,3)
    $oRange.Collapse($wdCollapseStart)
    $oExcel.Application.ActiveSheet.Range("A2:K9").Copy
    $oRange.Paste
    $oRange.Collapse($wdCollapseEnd)
    $oExcel.Application.ActiveSheet.Range("c2:f9").Copy
    $oRange.Paste
    $oRange.Collapse($wdCollapseEnd)
    $oOMail.Send

 

[Jewtus]

@Jfish

I was looking at that thread, but it looks like it deals with unformatted content unless I use clipboard UDF. I didn't see any functions in clipboard that seems to work for me. I also already have the content in the clipboard, so I tried clipput a couple different ways, but it just pastes a 1 when I use Body= and puts nothing when I use BodyHTML=.

@Juvigy

I had actually chimed in on that thread with what I'm currently doing (the send command), but I am not sure I understand where you are getting $wdCollapseEnd. It looks like your script might work if I can resolve the undefined $wdCollapseEnd variable.

[Juvigy]

It is a constant from outlook object model.

    Local $olMailItem = 0, $olFormatRichText = 3, $olImportanceLow = 0, $olImportanceNormal = 1, $olImportanceHigh = 2
    Local $olByValue = 1, $olFormatHTML = 2, $olFormatPlain = 1, $wdCollapseEnd = 0 , $wdCollapseStart = 1

 

[water]

The $wd* constants are from the Word object model, not Outlook.

When using the OutlookEX and Word UDFs then those constants are already included.

[Jewtus]

I actually did figure that out when I was looking into collapse (which it turns out I didn't need).

 

This script works flawlessly for me:

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = False
$oBook=$oExcel.WorkBooks.Open ($fExcel)
$Sheet=$oExcel.ActiveWorkbook.Worksheets.Item($sSheetname)

$oOApp = ObjCreate("Outlook.Application")
$olMailItem = 0
$oOMail = $oOApp.CreateItem ($olMailItem)
$oOMail.Display
$oRange = $oOMail.GetInspector.WordEditor.Range
$Sheet.Range("A1:D60").Copy
$oRange.Paste
$oExcel.DisplayAlerts = False
$oBook.Close(False)
$oExcel.Quit

Thanks for the sample @Juvigy

Edited May 29, 2015 by Jewtus