• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

code for email and snapshot of charts in mail



I am looking for some adjustment to below code, where it generate mail with the given range as snapshot in the mail itself.

It is working fine but the issue is, it is copying range in the excel and pasting it , where as i have graphs , which it is not copying.

Hence can anyone suggest what modification needs to be done to below code so that it copy graph and paste it insted of range

Many thanks !!

Sub Bus_Admin()

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object

Set rng = Nothing
On Error Resume Next
Set rng = Sheets("Category").Range("B10:U18").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

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

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
        .To = Sheets("Category").Range("AH1")
        .CC = Sheets("Category").Range("AH2")
        .Subject = Sheets("Category").Range("AH3")
        .HTMLBody = Sheets("Category").Range("AH4") & "<br>" & _
                                            RangetoHTML(rng) & "<br>" & "<br>" & _
        On Error Resume Next
        On Error GoTo 0
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
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
    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
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        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, _
        .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
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    '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

Kenneth Hobson

Active Member
If you want to embed the image. Set a value for fn and change name of "Chart 1" to suit.
  ActiveWorkbook.Sheets("Category").ChartObjects("Chart 1").Chart.Export fn, "GIF"
  .htmlbody = .htmlbody & vbCrLf & "<img src=" & """" & fn & """" & " alt=" & """" & "Chart1" & """" & " />"
  Kill fn
Otherwise, a copy/paste to Outlook WordEditor (body) will work.