Understanding Conditional fomatting in RangetoHTML

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Hi!

Utilizing Ron de Bruin's code to email Excel range over Outlook in HTML, however could you guys help me understand the conditional formatting part. I want to focus on 3 columns in my range: E; F; G. I have conditional formatting in place for column G based on formula:
Code:
=F3<>0
e.g. if column F is not 0, format column G (yellow fill). This works just fine in Excel.

Problem occurs when running VBA to email my range over Outlook. VBA hides column F before selecting the range, however I still expect conditional formatting to withstand (which it does - in Excel!). When looking at the email, column F is not showing, as expected, however conditional formatting now works based column E, not column F.

If I avoid hiding column F, conditional formatting works fine, however I prefer to hide column F when selecting the range to be emailed. Could you guys help me in understanding that is causing this?

formatting.jpg

L1h7M2B

L1h7M2B


The code itself is as following:

Code:
Sub Email()


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


    Set rng = Nothing
    Set rng2 = Nothing
    On Error Resume Next


    Set rng = Sheets("Sheet1").Range("Email_range").SpecialCells(xlCellTypeVisible)
    Set rng2 = Sheets("Sheet1").Range("Table3").SpecialCells(xlCellTypeVisible)
    
    On Error GoTo 0

    If rng2 Is Nothing Then

    Call Stop

    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 = Range("A2")
        .CC = ""
        .BCC = ""
        .Subject = "Subject line"
        .HTMLBody = RangetoHTML(rng)
        .Send   'or use .Display
    End With
    On Error GoTo 0


    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With


    Set OutMail = Nothing
    Set OutApp = Nothing
    
Application.OnTime Now + TimeValue("00:00:5"), " Close "    
    
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"


    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


    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 x:publishsource=", _
                          "align=left x:publishsource=")


    TempWB.Close savechanges:=False


    Kill TempFile


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

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
This is what is happening:
- First you hide column F.
- The range contains only visible cells: Set rng = Sheets ("Sheet1"). Range ("Email_range"). SpecialCells (xlCellTypeVisible)

- Continuing with your example, the range is D, E, F, G, but as hidden F, then the visible range is D, E, G (only 3 columns)


- The code creates a new book, and on the sheet of the new book copies your range
rng.Copy


- Paste in A1
.Cells(1).PasteSpecial Paste: = 8

- Only paste A, B, C (3 columns)
.Cells(1).PasteSpecial xlPasteValues,, False, False


- Paste the conditional format, where the conditional format refers to the previous column, at this moment the format is in column C and it takes as reference the values ​​of column B, that is why the colors are modified.
.Cells(1).PasteSpecial xlPasteFormats, , False, False

Sheet in new book:
3b72db4d40515de655e5dc5f9531a1f3.jpg


That is the explanation.


I already tried to send the range without hiding column F, hiding column C in the new sheet of the new book, the conditional format works, but when putting the data in outlook, column F becomes visible.

----


One way to fix it is:

- On the sheet, do not hide column F


- In code Email(), Leave the range with all the visible columns:


Code:
[COLOR=#0000ff]Set rng = Sheets("Sheet1").Range("Email_range")[/COLOR]

In the Function RangetoHTML
- add these lines
Code:
        .Columns ("C: C"). Cut
        .Columns ("H: H"). Insert Shift:=xlToRight

'What it does is change the conditional format of column C (before F) to column H. It means that column D will now take as reference what is in column H


- Change the range of cells you want to put in outlook:
Code:
[COLOR=#0000ff]Source:=TempWB.Sheets(1).Range("A1:C6").Address[/COLOR]


For the example that we are reviewing, it looks like this:

Code:
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"
    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
        
[COLOR=#0000ff]        .Columns("C:C").Cut[/COLOR]
[COLOR=#0000ff]        .Columns("H:H").Insert Shift:=xlToRight[/COLOR]


        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With


    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         [COLOR=#0000ff]Source:=TempWB.Sheets(1).Range([/COLOR][COLOR=#ff0000]"A1:C6[/COLOR][COLOR=#0000ff]").Address[/COLOR], _
         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 x:publishsource=", _
                          "align=left x:publishsource=")


    TempWB.Close savechanges:=False
    Kill TempFile


    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 
Upvote 0
DanteAmor, thanks a lot! Great input, well appreciated.

I think I ended up with "not hide" the column option, however made VBA change the column so that it's virtually invisible (though not hidden). Still, I also tried test option to change RangeHTML function, but wasn't entirely sure how it'd work. Given column H was out of screenshot, I assume it was used because thought to be empty? In reality it isn't but obviously I could refer to another column that eventually will be empty. Was the objective to just find a blank column to refer to and effectively remove formatting effects?

This was not what I tried to do - I wanted the formatting effects in Outlook to stay as they were also when the column was hidden. But the useful explanation might lead me to believe this might not be possible.


...actually column H brings me to another conditional formatting issue here, which might be different in nature. Column H will have location data + another set of conditional formatting to check columns D + H and format column H in case there are duplicates (=COUNTIFS($D$1:$D$5;$D1;$H$1:$H$5;$H1)>1). I.e. not individual duplicates in column D or H, but combined. Again, works great in Excel.

This conditional formatting looks totally different once in Outlook. I tried switching all hiding off on purpose, however almost all records in column H are formatted in Outlook. I know this comes by quite vague, however any ideas what might cause this or how to troubleshoot? Unlike with the previous one, I don't even quite understand the new formatting logic present in Outlook.
 
Last edited:
Upvote 0
One option is that your copying range covers all the columns, since if you copy d, e, f, g. the macro of Ron hits them in a, b, c, d and that completely changes the conditional format.
Or modify Ron's macro to paste in d, e, f, g:

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

by:

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

That is, try to replicate your original sheet on the target sheet that Ron uses to create the HTML format.


The other option is to change the format conditions to cell format, copy and paste.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top