RangetoHTML file size problem

[ChrisKoko]

Hi there,

I'm using RangetoHTML to send chunks of a spreadsheet via CDO emails to lots of recipients. The range is fairly small, and contains basic text, however the "RangetoHTML" function (which I am not really keen on since I cut & paste it but have not yet figured out exactly how it works) makes each of my messages 2MB in size, which is pretty poor given the small amount of actual data.

Is there anything I can do to improve the function's efficiency by removing the junk HTML code?

Thanks!

 

[Andrew Poulsom]

Welcome to the Board.

Which version of RangeToHTML are you using? And which version of Excel?

 

[ChrisKoko]

Hi there,
I'm on Excel 2003, and the RangetoHTML code I'm using is:

Function RangetoHTML(Rng As Range)
Dim wb As Workbook
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim i As Long
Dim Rng2 As Range
Dim DelCol1 As String
Dim DelCol2 As String
Randomize
TempFile = Rng.Parent.Parent.Path & "\TmpHTML" & Int(Rnd() * 10) & ".htm"
'Copy the sheet to a new workbook and copy the cells to avoid the
'255 character limit when copying sheets
Rng.Parent.Copy
' Rng.Parent.Cells.Copy ActiveSheet.Cells
Set wb = ActiveWorkbook
Set Rng2 = wb.Sheets(1).Range(Rng.Address)
'Convert to values
Rng2.Copy
Rng2.PasteSpecial xlPasteValues
Application.CutCopyMode = False
'Delete rows below
Rng2.Parent.Rows(Rng2.Rows(Rng2.Rows.Count).Row + 1 & ":65536").Delete
'Delete columns to right
DelCol2 = Chr(64 + Rng2.Parent.Columns(Rng2.Columns _
(Rng2.Columns.Count).Column + 1).Column)
Rng2.Parent.Columns(DelCol2 & ":IV").Delete

'Delete rows above
If Rng2.Rows(1).Row > 1 Then
Rng2.Parent.Rows("2:" & Rng2.Rows(1).Row - 1).Delete
End If
'Delete columns to left
If Rng2.Columns(1).Column > 1 Then
DelCol1 = Chr(64 + Rng2.Parent.Columns(Rng2.Columns(1).Column - 1).Column)
Rng2.Parent.Columns("A:" & DelCol1).Delete
End If
wb.SaveAs TempFile, xlHtml
wb.Close False
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function

Sorry, not sure how to paste code in with better formatting! Hope you can help!

 

[Andrew Poulsom]

I don't suppose you are using Excel 2007 are you? That code has hard coded 65536 and IV.

Here is another version you may want to try:

http://www.rondebruin.nl/mail/folder3/mail4.htm

 

[ChrisKoko]

Aha. Excellent.

I had an idea the coding of the function I was using was causing the problem.

The function you suggested does look a lot snappier, and gives me a message size of only 11KB. Just what I was after.

Thanks for your help!