How to paste a chart from excel to outlook

[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 May 7, 2015 by Juvigy

[water]

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

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

[water]

Does it work when you do it by hand?

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

[Jewtus]

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

[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).

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

[water]

Great

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

 

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

[Juvigy]

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

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