Results 1 to 4 of 4

Thread: Understanding Conditional fomatting in RangetoHTML

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Understanding Conditional fomatting in RangetoHTML

    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?





    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

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,089
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Understanding Conditional fomatting in RangetoHTML

    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:


    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:
    Set rng = Sheets("Sheet1").Range("Email_range")
    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:
    Source:=TempWB.Sheets(1).Range("A1:C6").Address

    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
            
            .Columns("C:C").Cut
            .Columns("H:H").Insert Shift:=xlToRight
    
    
            .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).Range("A1:C6").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
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Feb 2016
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Understanding Conditional fomatting in RangetoHTML

    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 by dotsent; Apr 20th, 2019 at 10:41 PM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,089
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Understanding Conditional fomatting in RangetoHTML

    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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •