How to Email a Range which contains a chart using VBA?

[sreekhosh]

Hi,


I am using the below code for sending a range as email body. But I recently added a chart also into the range but it doesnt work (chart is not displaying).


I just try with disabling the following code in function [.DrawingObjects.Delete ]. then the an error message was displying in email body instead of Chart. Please help.


Please see the code below


===============================


Sub Email_Send()


Dim rng As Range

Dim OutApp As Object

Dim OutMail As Object


Set rng = Nothing

On Error Resume Next


Set rng = Selection.SpecialCells(xlCellTypeVisible)


Set rng = Sheets("Email").Range("A1:W43").SpecialCells(xlCellTypeVisible)

On Error GoTo 0


If rng Is Nothing Then

MsgBox "The selection is not a range or the sheet is protected" & _

vbNewLine & "please correct and try again.", vbOKOnly

Exit Sub

End If

CurrentFile = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))


With Application

.EnableEvents = False

.ScreenUpdating = False

End With


Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)


strbody = "<H5>Dear Team,</H5>" & _

"Please find the " & CurrentFile & "."

On Error Resume Next

With OutMail

.to = Range("x1")

.CC = Range("x2")

.BCC = ""

.Subject = CurrentFile

.HTMLBody = strbody & "


" & RangetoHTML(rng) & "


" & Signature


.Display


End With

On Error GoTo 0


With Application

.EnableEvents = True

.ScreenUpdating = True

End With


End Sub


===================================


Function RangetoHTML(rng As Range)


Dim fso As Object

Dim ts As Object

Dim TempFile As String

Dim TempWB As Workbook


TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


'Copy the range and create a new workbook to past the data in

rng.Copy

Set TempWB = Workbooks.Add(1)

With TempWB.Sheets(1)

Cells(1).PasteSpecial Paste:=8

.Cells(1).PasteSpecial xlPasteValues, , False, False

.Cells(1).PasteSpecial xlPasteFormats, , False, False

.Cells(1).Select

Application.CutCopyMode = False

On Error Resume Next

.DrawingObjects.Visible = True

.DrawingObjects.Delete

On Error GoTo 0

End With


'Publish the sheet to a htm file

With TempWB.PublishObjects.Add( _

SourceType:=xlSourceRange, _

Filename:=TempFile, _

Sheet:=TempWB.Sheets(1).Name, _

Source:=TempWB.Sheets(1).UsedRange.Address, _

HtmlType:=xlHtmlStatic)

.Publish (True)

End With


'Read all data from the htm file into RangetoHTML

Set fso = CreateObject("Scripting.FileSystemObject")

Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)

RangetoHTML = ts.readall

ts.Close

RangetoHTML = Replace(RangetoHTML, "align=center xublishsource=", _

"align=left xublishsource=")


'Close TempWB

TempWB.Close savechanges:=False


'Delete the htm file we used in this function

Kill TempFile


Set ts = Nothing

Set fso = Nothing

Set TempWB = Nothing

End Function

 

[sreekhosh]

Somebody Please Help??????

 

[SirJB7]

Hi, sreekhosh!

I haven't ever tried to do such a thing, but at a first glance I think I'm afraid that you couldn't do that. The RangetoHTML variable is assigned the result of an open as text stream file, I can't imagine how to assign a chart to it. And if I could, I don't know how the .HTMLBody property would behave.

Hope it helps.

Regards!

 

[sreekhosh]

Thanks SirJB7


Yes SirJB7, you are right it is not as simple as i expected. Today i tried another method, I saved the chart as JPG in a folder. and added to the HTML body (using <img Src="chart.jpg"&#62. It works fine and displayed in the email but the thing is when ever i change the picture it will change in the email also.


Regards,


Sreekhosh

 

[SirJB7]

Hi, sreekhosh!

Well, glad you found a workaround, even if not perfect. Welcome back whenever needed or wanted.

Regards!

 

[NARAYANK991]

Hi Sreekhosh ,


Have you checked out the following links ?


1. http://www.mrexcel.com/forum/excel-questions/42480-send-chart-email-body-visual-basic-applications.html


2. http://www.pcreview.co.uk/forums/include-excel-charts-outlook-email-body-vbulletin-t3580457.html


3. http://www.ozgrid.com/forum/showthread.php?t=62502 ; there are other links in this thread.


4. http://stackoverflow.com/questions/10281016/how-to-send-an-embedded-image-in-email-from-excel


Narayan

 

[sreekhosh]

thank you Narayan.


sorry for the late reply.


I have already gone through these links... Adding a chart in the message body is very tough task.. I chnagd ma mind.. and started to send that chart as attachement in JPG format.


Thanks


Sreekhosh

 

[Deepak]

Dear sreekhosh,
if you haven't found any solution so far yet then must should to try this.

http://social.msdn.microsoft.com/Fo...l-chart-into-new-outlook-email?forum=exceldev

 

[sreekhosh]

Dear Deepak,

I have asked this doubt one year before. I am very glad to see your reply after one year, however the link you provided is not satisfying my query.

I need to add a Range and a chart to email body for sending (not as attachment). But its not possible for me and I add the range into email body and attached the chart as well.

Regards
Sreekhosh

 

[Deepak]

tested.
this will paste the active chart to mail body.

Option Explicit

Sub CopyAndPasteToMailBody()
    Dim mailApp, mail As Object
    Dim olMailItem, wEditor As Variant
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    mail.Display
    Set wEditor = mailApp.ActiveInspector.wordEditor
    ActiveChart.ChartArea.Copy
    wEditor.Application.Selection.Paste
End Sub

 

[sreekhosh]

Great Thanks a ton.

 

[Deepak]

Welcome! Have a good day!!