• 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.

RangetoHTML - Snapshot Repeated at end

Anant Chirmade

New Member
Hi Team,

I have a automated report which published on email using "RangetoHTML" function..

Part of the email code is :

.HTMLBody = Body1 & RangetoHTML(rng) & Body2 & RangetoHTML(rng1) & Body3 & RangetoHTML(rng2)
When the email is sent, Snapshot for "RangetoHTML(rng)" is displayed twice.
1st at the proper place and 2nd at the very end of the email,which is unnecessary.

Rng1 and Rng2 snapshots are placed on proper place..
only Rng is repeated.

Please help..

Thanks in Advance.

Marc L

Excel Ninja


if this function or method exists, in code put cursor on it, hit F1 key
and read inner VBA help !

Anant Chirmade

New Member
Hi marc,

Actually its a custom function created in VBA :

Below is the function code :

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2013
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