Pasting Chart into Outlook Email from Excel
11,114
Solution 1
You have some errors on your code, try using Option Explicit top of your module
Option Explicit
Public Sub pasting01()
Dim Sht As Excel.Worksheet
Set Sht = ThisWorkbook.ActiveSheet
Dim rng As Range
Set rng = Sht.Range("A1:J30")
rng.Copy
Dim OutApp As Object
Set OutApp = CreateObject("Outlook.Application")
Dim OutMail As Object
Set OutMail = OutApp.CreateItem(0)
Dim vInspector As Object
Set vInspector = OutMail.GetInspector
Dim wEditor As Object
Set wEditor = vInspector.WordEditor
With OutMail
.TO = "[email protected]"
.CC = "[email protected]"
.Subject = "Test"
.display
wEditor.Paragraphs(1).Range.Text = "Dear Mr Lee" & vbCr
wEditor.Paragraphs(2).Range.Paste
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Solution 2
Can you mess about with the following to suit your purpose?
Option Explicit
Sub pasting01()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
Dim myChart As Chart
Set myChart = ThisWorkbook.Worksheets("Sheet1").ChartObjects("Chart 1").Chart
Dim myPicture As String
Dim fileName As String
Dim myPath As String
myPicture = "Chart1.png"
myPath = "C:\Users\User\Desktop\"
fileName = myPath & myPicture
myChart.Export fileName
With OutMail
.TO = "[email protected]"
.CC = "[email protected]"
.Subject = "Test"
.Body = "Dear Mr Lee" & vbNewLine
.Attachments.Add fileName
.HTMLBody = "<html><p>First Line... </p>" & _
"<img src=cid:" & Replace(myPicture, " ", "%20") & " height=2*240 width=2*180>" & _
"<p>Salutation</p>" & _
"<p>" & "More text" & "</p></html>"
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Kill fileName
End Sub
Result:
Author by
josephyschen89
Updated on June 15, 2022Comments
-
josephyschen89 almost 2 years
Tried all other codes on similar pages but failed to work.
This is my current version. Works only if I currently have a new email window open and oddly, my code will paste the .body and cell range details into 2 separate new email windows.
I just want the code to open a new email window with contents .body and cell range details (contains chart). Anybody have any ideas where my code went wrong?
Sub pasting01() Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) With OutMail .TO = "[email protected]" .CC = "[email protected]" .Subject = "Test" .Body = "Dear Mr Lee" & vbNewLine ActiveSheet.Range("A1:J30").Copy Set vInspector = OutMail.GetInspector Set wEditor = vInspector.WordEditor wEditor.Application.Selection.Start = Len(.Body) wEditor.Application.Selection.End = wEditor.Application.Selection.Start wEditor.Application.Selection.Paste .display End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub
-
josephyschen89 about 6 yearsThanks so much the code works perfectly with option explicit!