Keeping Conditional Formating in RangetoHTML function

[movbanewbie]

Hi All,

I am using a cool vba code I got from Ron's MVP Page. I use all the time to send excel ranges in a body of outlook mail. My problem, I have an excel range that contains conditional formatting. The function that is used in the code is ignoring the conditional formatting. I was hoping someone can assist with the below function so that the range that is copied to another workbook keeps the conditional formatting. Thanks in advance for your help.

Function RangetoHTML(rng As Range)
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
Dim fc As FormatCondition

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


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
'Next fc
End With


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


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=")


TempWB.Close savechanges:=False


Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

 

[UnsolvedCoding]

You may not be able to keep the formating because HTML and VBA use different ways of formating.

For example, in VBA you can select a specific font sizes, say 12 or 15, but in HTML the standard size font size is 3 which in VBA translates roughly to a font size of 10 give or take.

It seems to me that the best way to retain specific formating is to write a few extra subs that create the message in HTML that way you can use HTML to specify what parts of the message get changed.

 

[movbanewbie]

Thank you for your reply. I am fairly new to vba and I am affraid I do not have the knowledge yet to code the change. Any input would be greatly appreciated.

 

[SkipVought]

Please specify each CF [formula or criteria] & corresponding format.

You might also try turning on your macro recorder and recording the editing of each of your CFs.

Post back with the appropriate information.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]