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

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
 
P

Peter T

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
 
R

Ron de Bruin

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
 
P

Peter T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to insert HTML into an Outlook 2021 email 0
Office 365 Outlook adding unwanted HTML to Email Body 0
Sending a mail via outlook excel 2003 0
Email Help: Email with voting buttons from excel, Body gets delete 0
Finding asymptote equation and how to draw them in excel 0
Excel 2010. How to copy chart format to all charts in sheet 2
HELP: Email body deleted when I receive voting response 0
Publishing XCL Range to HTML to insert into Email Body 1

Top