How to include Excel charts in Outlook email *body* with VB

  • Thread starter Thread starter Chrisso
  • Start date Start date
C

Chrisso

Hello

Is it possible to cut and paste a regular Excel chart into an Outlook
email *body* with VBA?

I have all the code for sending emails from http://www.rondebruin.nl/mail/folder2/chart.htm
but what is missing is the option of how to include the charts in the
*body* of the generated email. Ron's example shows how to attach a GIF
file.

Does anyone know how to do this? My audience want to see the charts
immediatley in the email body.

Thanks in advance,
Chrisso
 
Hi Ron,

In your MailEnvelope example, in your link below, I found if changed
Dim Sendrng As Range
to
Dim Sendrng As Object
Set Sendrng = Selection

If I selected a chartarea, it worked fine. However it failed if I tried to
do any of the following

Set Sendrng = ActiveSheet.Chartobjects(1)
Set Sendrng = ActiveSheet.Chartobjects(1).Chart
Set Sendrng = ActiveSheet.Chartobjects(1).Chart.ChartArea


In your "SaveSend_Embedded_Chart" as refered to by Chrisso here
http://www.rondebruin.nl/mail/folder2/chart.htm
the following change worked for me

change
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add Fname
.Send 'or use .Display
End With

to
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
' Fname = "C:\My_Sales1.gif" defined earlier
s = "<p>Hi there<p>"
s = s & "<p><img src=file://" & Fname & "></p>"
s = s & "Bye"
s = "<HTML><BODY>" & s & "<HTML><BODY>"
.HTMLBody = s
.Send 'or use .Display
End With

Regards,
Peter T
 
Hi Peter

You can also use this

Activate the chart you want to send in the code
ActiveSheet.ChartObjects("Grafiek 1").Activate


Sub Send_Chart_with_MailEnvelope()
On Error GoTo StopMacro

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

ActiveSheet.ChartObjects("Grafiek 1").Activate

'Create the mail and send it

ActiveWorkbook.EnvelopeVisible = True
With ActiveSheet.MailEnvelope

' Set the optional introduction field thats adds
' some header text to the email body.
.Introduction = "This is a test mail."

' In the "With .Item" part you can add more options
' See the tips on this Outlook example page.
' http://www.rondebruin.nl/mail/tips2.htm
With .Item
.To = "(e-mail address removed)"
.Subject = "My subject"
.Send
End With

End With

StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False

End Sub
 
Looks like "Send_Chart_with_MailEnvelope" is one of those few operations
that requires code to "select" something, in this case the chart.

In my previous post, in an 'ordinary' message I mentioned something like the
following works

s = s & "<p><img src=file://" & Fname & "></p>"
s = "<HTML><BODY>" & s & "<HTML><BODY>"
.HTMLBody = s

however I forgot to say that the file, Fname, must remain on the disk, so
when done don't do
Kill Fname

Regards,
Peter T
 
Back
Top