Jump to content

Copy from excel paste to email body


Recommended Posts

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?

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...