Jump to content
Jewtus

Copy from excel paste to email body

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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

 

  • Like 1

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by Jewtus

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×