Jump to content
Juvigy

How to paste a chart from excel to outlook

Recommended Posts

Juvigy

I am trying to paste a range with a chart from excel WB to outlook new email.

The problem is i cant paste it with the formatting - it shows as text.

I am using Selection.Copy from excel and i am trying to paste it in outlook newly created mail.

If i go manually and use CTRL+V in the mail - it works. I tried the <Clipboard.au3> functions but i cant get it to work.

I also tried converting this VBA:

Sub PasteFormattedTable()
  Dim Doc As Word.Document
  Dim wdRn As Word.Range
  Dim Xl As Excel.Application
  Dim Ws As Excel.Worksheet
  Dim xlRn As Excel.Range

  Set Doc = Application.ActiveInspector.WordEditor
  Set wdRn = Doc.Range

  Set Xl = GetObject(, "Excel.Application")
  Set Ws = Xl.Workbooks("Mappe1.xls").Worksheets(1)

  Set xlRn = Ws.Range("b2", "c6")
  xlRn.Copy

  wdRn.Paste
End Sub

Any ideas ?

Edited by Juvigy

Share this post


Link to post
Share on other sites
water

A chart is a different object than a range. Will check tomorrow and post an example. 


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
Juvigy

I know , but next to the chart there are a few cells that i want to include - that is why i use the range instead of the chart object.

Share this post


Link to post
Share on other sites
water

Does it work when you do it by hand?


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
Juvigy

Yes. Programmatically it also works till some point - the only thing is the paste in outlook. If i pause the program till that point and manually do a ctrl+v in the new email - it works great.

Share this post


Link to post
Share on other sites
Jewtus

You could do it ghetto style with a Send() command

Share this post


Link to post
Share on other sites
water

Haven't had the time to create a script for it. But I can think it could work this way:

Use function _XLChart_ChartsGet from my ExcelChart UDF to get a list of charts and use the returned object to copy the chart to the clipboard.
In Outlook use the method described in the example script _OL_ItemCreate.au3 (Example 1) that comes with the OutlookEX UDF. This exampel uses MS Word to edit the mail body.

Another approach would be to save the chart and insert it into an HTML mail as described in _OL_ItemCreate.au3 (Example 4).

  • Like 1

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
Juvigy

Thanks Water , i nearly did it converting this one :

;~ Sub test1()
;~     Set mailApp = CreateObject("Outlook.Application")
;~     Set mail = mailApp.CreateItem(olMailItem)
;~     mail.Display
;~     Set wEditor = mailApp.ActiveInspector.wordEditor
;~ '    ActiveChart.ChartArea.Copy
;~     Selection.Copy
;~     wEditor.Application.Selection.Paste
;~ End Sub

But it required 'mail.Display' - i was trying to avoid that. using  .GetInspector.WordEditor from your example instead of  .ActiveInspector.wordEditor solved it.

Share this post


Link to post
Share on other sites
water

Great :)


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
Juvigy

I bumped to another strange thing: Lets say I have 5 charts in a column one bellow the other. If i select the first one , and then the last one and paste it to another excel sheet - it pastes only the 2 charts. If i paste it in Outlook - it pastes the all the charts. Any idea why and how to fix this ? It is the same when doing it manually.

 

Share this post


Link to post
Share on other sites
water

If the same method leads to different results then it looks like a MS Office problem. I have no idea why it behaves differently.


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
Juvigy

I will have to go to hard way with something like:

$oRange.InsertAfter('Hi Guys, <BR> <BR> ')
$oRange.Collapse($wdCollapseStart)
$oRange.Paste

 

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

×