Jump to content
Juvigy

How to paste a chart from excel to outlook

Recommended Posts

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

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 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Does it work when you do it by hand?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Great :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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 2019-08-19 - Version 1.4.13.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-07-24 - Version 1.3.6.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (NEW 2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - 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
Taks Scheduler (NEW 2019-10-09 - Version 0.9.0.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

  • Similar Content

    • By roeselpi
      hello everybody.
      i am an absolute beginner and have zero experience with programming and / or scripting.
      i have been looking at autoit for about one week and thus do not know and / or understand a lot about it. 
      but: i have started writing a programe. i have not got very far yet, but many hours of tutorial watching and trying out things has brought me to where i am now. to be honest, i did not even think that i would make it this far. trial and error all the way. but i am quite happy to say that my programe works... well... all apart from the most important two functions. but i have given it a good shot and come to the point where my understanding is limited for what i want to do and thus i would need the help of a coding-expert.
      to make a long story short i will show what i have got... i guess everything can be copy and pasted apart from the image. it is just an example image but i will upload it anyway.
      1. make a simple text file and save it as "test.txt" with the following text within it:
      Hammurabi walks into the forest @ 5 o'clock. 2. make a simple text file and save it as "illogic.ini" with the following text within it:
      [alternation] alter=2,4,1 ;- 0 for NO alternation [spacing] steps=3 ;- aplies only if alternation is 0 ;- disable function with 0 but alternation must then aply [syntax] syntax=3,5,2 ;- 0 for RANDOM choice [symbols] symbs=a,b,c,d,e,f,g,h,i,j,k,0,1,2,3,4,5,6 ;- symbols to be used in scramble routine 3. save the image as "illogic.jpg" together with the other two files in order to have the same as me in one directory (illogic). or edit the au3 code to match with the rest.
      4. save the au3 code below as "illogic.au3" in the same directory.
      #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <WindowsConstants.au3> #include <FileConstants.au3> #include <MsgBoxConstants.au3> #include <WinAPIFiles.au3> $ini = "illogic.ini" $sections = IniReadSectionNames($ini) For $a = 1 To UBound($sections) - 1 $keys = IniReadSection($ini, $sections[$a]) For $b = 1 To UBound($keys) - 1 ; ConsoleWrite($keys[$b][1] & @CRLF) ; ShellExecute(IniRead($ini, $sections[$a], $keys[$b][0], "")) Next Next #Region ### START Koda GUI section ### Form=C:\AutoIt3\illogic\illogic-mask.kxf Global $Navigation = GUICreate("illogic - the illogical scrambler", 1002, 653, -1, -1) Global $File = GUICtrlCreateMenu("&File") Global $NewFile = GUICtrlCreateMenuItem("New File", $File) Global $OpenFile = GUICtrlCreateMenuItem("Open File", $File) Global $SaveAs = GUICtrlCreateMenuItem("Save as...", $File) Global $Exit = GUICtrlCreateMenuItem("Exit", $File) Global $etc = GUICtrlCreateMenu("?") Global $About = GUICtrlCreateMenuItem("About", $etc) Global $scramble = GUICtrlCreateButton("ILLOGIC", 48, 568, 145, 33) Global $descramble = GUICtrlCreateButton("LOGIC", 240, 568, 145, 33) Global $exiteer = GUICtrlCreateButton("EXIT", 808, 568, 145, 33) Global $entry = GUICtrlCreateEdit("", 40, 128, 921, 417) GUICtrlSetData(-1, "Empty File...") GUICtrlSetBkColor(-1, 0xE3E3E3) Global $Pic1 = GUICtrlCreatePic("C:\AutoIt3\illogic\illogic.jpg", 40, 16, 921, 89) Global $reset = GUICtrlCreateButton("RESET", 432, 568, 147, 33) GUISetState(@SW_SHOW) #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Global $exitbutton $exitbutton = MsgBox(36, "Close Aplication?", "Bye For Now & See You Next Time...") If $exitbutton = 6 Then Exit Case $Exit Global $exitbutton $exitbutton = MsgBox(36, "Close Aplication?", "Bye For Now & See You Next Time...") If $exitbutton = 6 Then Exit Case $exiteer Global $exitbutton $exitbutton = MsgBox(36, "Close Aplication?", "Bye For Now & See You Next Time...") If $exitbutton = 6 Then Exit Case $About MsgBox(0,"About...","illogic v 1.0") Case $SaveAs $EntryData = GUICtrlRead($entry) $SaveLocation = FileSaveDialog("Save file", @ScriptDir, "All Files (*.*)", 16) FileWrite($SaveLocation, $EntryData) Case $NewFile GUICtrlSetData($entry, "New File...") Case $reset GUICtrlSetData($entry, "Reset...") Case $OpenFile $read = FileOpenDialog("Open File", @ScriptDir, "All Files (*.*)", 16) $text = FileRead($read, -1) GUICtrlSetData($entry, $text) FileClose($text) EndSwitch WEnd That is now a working programe. If you open the test.txt file in the programe it will copy the text into the edit box and that is where my problem starts. I would now like the two only unused buttons "illogic" and "logic" to perform what i have intended them to do and that is where the settings from the ini file become important and also the spot where i need help because being a noobie also means not understanding how to go about solving issues where there are no tutorials...
       
      Pressing "illogic" should transform (with the settings from the ini shown above) the text within the edit box like shown below:
      Hchaj16bmemj0u3bdgr0a25bbgi0i5wadaik2alckfks14ceihn13t6egjo tchhj16beefj0o3bdgr0e25sbgi0t5@ad5ik2aoc'fkc14celho13c6egjk3. to explain how the text got scrambled let us look at the ini file:
      the alternation "alter=2,4,1" describes that inbetween each letter or space there must be first two, then next four and finally one letter placed in addition to the existing text
      the steps "steps=3" describes that inbetween each letter or space there must be 3 additional letters placed. this only aplies when alternation=0
      the syntax "syntax=3,5,2" describes which letter must be used in which order. first the 3rd letter then from there on the 5th letter and finally the 2nd letter. 
      the symbols "symbs=a,b,c,d,e,f,g,h,i,j,k,0,1,2,3,4,5,6" describes what spacing letters can or must be used. basically the full alphabet and numerals and possibly even special chars
       
      it took about twenty minutes to do this manually because i kept getting the couting wrong. 
       
      anyway, the illogic button should distort the text in accordence with the settings in the ini file and of course output it in the edit box. then the file should or could be saved as a text file.
       
      i guess you know what the "logic" button is supposed to do... yes, it is supposed to turn the mumble-jumble back into the original text. (as long as the ini file is not altered it should work)
       
      another idea was working with several ini files and adding a choice dropdown for the ini files, but that is not an absolute requirement.
       
      now to my problem:
       
      i have no idea how to carry on form the point i am at now. i have been able to read the ini values into the console but the impossible task begins now trying to make functions or variables for each parameter from the ini-file. that is where i would hope for some help from users who know what they are doing and maybe can take the time to give me a big help. 
       
      i am very much hoping for helpful replies.
       
      kind regards
       
       
      edit: corrected some mistakes.

×
×
  • Create New...