Jump to content
vick

Copy from excel to email body

Recommended Posts

vick

Hi,

 

I have used Outlook UDF to create email.

Not in $bodyvar which is email body i need to copy something like $OWorkbook.ActiveSheet.Range("A1:F25").Copy and paste in $Bodyvar.

How i can do it pls?

thanks,

Share this post


Link to post
Share on other sites
vick

Hi,

 

I am getting this error:Variable used without being declared.:

Also excel has chart which i want to copy in body.

 

Share this post


Link to post
Share on other sites
Subz

Do you have an example Excel spreadsheet + your Autoit code we can look at?

Share this post


Link to post
Share on other sites
water

Depends on the format of the Outlook mail you use. Is it HTML or RTF?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
vick

html

 

Share this post


Link to post
Share on other sites
vick
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\1234".xlsx")

Global $oOutlook = _OL_Open()
Global $bodyvar=_Excel_RangeRead($oWorkbook, 1, "A1:F25")


_OL_Wrapper_SendMail(... )

this is my code. in excel i have chart and table which i want in body

Share this post


Link to post
Share on other sites
water

When using COM you do not automate the Outlook GUI but you use an API to directly interact with Outlook core.
I suggest to save the chart to a file and then imbed it into the mail body.
The example script for _OL_ItemCreate has an example how to add a Picture inline (Example 4).
 

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
vick

Hi,

 

I saw  _OL_ItemCreate function but do not see any example. how to add excel stuff when create email. 

Sorry I am not that Pro who can understand quickly.

Share this post


Link to post
Share on other sites
Subz

This works for me, have fun off to have some zzzzzzzzz.

#include <Excel.au3>

$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Chart.xlsx")
$oRange = $oWorkbook.ActiveSheet.Range("A1:G34").Select
$oExcel.CopyObjectsWithCells = True
$oExcel.Selection.Copy

$oOutlook = ObjCreate("Outlook.Application")
$oMail = $oOutlook.CreateItem(0)

$oMail.Display
$oMail.To = "sample@example.com"
$oMail.Subject = "Sample Subject"
$oWordEditor = $oOutlook.ActiveInspector.wordEditor
$oMail.Body = "Hello" & @CRLF & "Please find charts above." & @CRLF & @CRLF & "Regards Subz"
$oWordEditor.Range(0, 0).Select
$oWordEditor.Application.Selection.Paste
$oMail.Display

Share this post


Link to post
Share on other sites
vick

thanks Man. I will try and update you.

Share this post


Link to post
Share on other sites
vick

Hi,

 

I am getting this error Variable must be of type "Object" at this line:

$oRange = $oWorkbook.ActiveSheet.Range("A1:G34").Select

idea?

Share this post


Link to post
Share on other sites
water

What is the value of @error after _Excel_BookOpen?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
vick

 I am not getting in @error anything but in console i got error Variable must be of type "Object"

Share this post


Link to post
Share on other sites
vick
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Report.xlsx")
Sleep(5000)
$oRange = $oWorkbook.ActiveSheet.Range("A1:A5").Select
If @error Then
    MsgBox(0,"",@error)
EndIf
$oExcel.CopyObjectsWithCells = True
$oExcel.Selection.Copy

$oOutlook = ObjCreate("Outlook.Application")
$oMail = $oOutlook.CreateItem(0)

$oMail.Display
$oMail.To = "vp@xxx.com"
$oMail.Subject = "Sample Subject"
$oWordEditor = $oOutlook.ActiveInspector.wordEditor
$oMail.Body = "Hello" & @CRLF & "Please find charts above." & @CRLF & @CRLF & "Regards vp"
$oWordEditor.Range(0, 0).Select
$oWordEditor.Application.Selection.Paste
$oMail.Display

this is my script

Share this post


Link to post
Share on other sites
Juvigy

Your code works for me. Have you enabled macros and lowered excel security?

Try changing

$oRange = $oWorkbook.ActiveSheet.Range("A1:G34").Select

to

$oRange = $oWorkbook.Application.ActiveSheet.Range("A1:G34").Select

Share this post


Link to post
Share on other sites
vick

Thanks  everyone for support. It works now.

Share this post


Link to post
Share on other sites
vick

hi,

 

i found one issue here that what i put in email body like text 

"Hello" & @CRLF & "Please find charts above." & @CRLF & @CRLF & "Regards vp"

goes always after my copy part from excel. i want it in first line then paste.

How i can do it pls?

Share this post


Link to post
Share on other sites
Subz

Try:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Chart.xlsx")
Local $oRange = $oWorkbook.ActiveSheet.Range("A1:G34").Select
    $oExcel.CopyObjectsWithCells = True
    $oExcel.Selection.Copy

Local $oOutlook = ObjCreate("Outlook.Application")
Local $oMail = $oOutlook.CreateItem(0)
    $oMail.Display
    $oMail.To = "sample@example.com"
    $oMail.Subject = "Sample Subject"

Local $sBodyHeader = "Hello" & @CRLF & @CRLF & "Please find charts below." & @CRLF & @CRLF
Local $sBodyFooter = @CRLF & @CRLF & "Regards Subz"

Local $oWordEditor = $oOutlook.ActiveInspector.wordEditor
    $oWordEditor.Range(0, 0).Select
    $oWordEditor.Application.Selection.TypeText($sBodyHeader)
    $oWordEditor.Application.Selection.Paste
    $oWordEditor.Application.Selection.TypeText($sBodyFooter)

$oMail.Display

 

Share this post


Link to post
Share on other sites
vick

thank you very much sir:)

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

×