Conditional formatting not copying over correctly into body of HTML email

Hi all,

I've spent DAYS trying to figure this out and it's really getting me frustrated and down

I'm using Ron de Bruins' VBA code to send an email of a range of cells that have conditional formatting in them. If I manually copy and paste the cells into a new e-mail message, the conditional formatting shows up exactly as displayed in the excel worksheet.

BUT...when I use a button with a VBA script to copy and paste it into the body of a new Outlook email message, the conditional formatting is all messed up! It doesn't even make sense how it's figuring out the formatting (especially since a normal copy/paste works fine).

Can someone PLEASE help me figure this one out!?

I'm using Excel 2010 and Outlook 2010

Thanks everyone!

Hello seaottr,

Can you post your workbook so we can see your code and the layout?

Hello seaottr,

Copy and paste the Module1 macro code into a new VBA module in your workbook. Change the CommandButton2 in the "Results" worksheet module also.

Module1 Macro Code

'Written: September 22, 2008
'Updated: August 18, 2011
'Author:  Leith Ross
'Summary: Send a specfied worksheet range in the body of an Outlook email
'         in HTML format.


Sub EmailRangeInHTML(ByVal Recipient As String, ByVal Subject As String, Optional Range_To_Send As Variant)

  Dim FSO As Object
  Dim HTMLcode As String
  Dim HTMLfile As Object
  Dim MyApp As Boolean
  Dim olApp As Object
  Dim Rng As Range
  Dim TempFile As String
  Dim Wks As Worksheet

  Const ForReading As Long = 1
  Const olMailItem = 0
  Const olFormatHTML = 2
  Const UseDefault As Long = -2
    
     On Error GoTo CleanUp
     
     If IsMissing(Range_To_Send) Then
        Set Rng = Selection
     Else
        Select Case TypeName(Range_To_Send)
          Case Is = "Range"
              Set Rng = Range_To_Send
          Case Is = "String"
              Set Rng = Evaluate(Range_To_Send)
          Case Else
              MsgBox "Your Selection is Not a Valid Range."
              GoTo CleanUp
        End Select
     End If
     
     ' Copy the worksheet to create a new workbook
       Set Wks = Rng.Parent
       Wks.Copy
     
     ' The new workbook will be saved to the user's Temp directoy
       TempFile = Environ("Temp") & "\" & Wks.Name & ".htm"
     
     ' If a file by this exists then delete it
       If Dir(TempFile) <> "" Then Kill TempFile
     
         ' Start Outlook
           Set olApp = CreateObject("Outlook.Application")
      
         ' Convert the Message worksheet into HTML
           With ActiveWorkbook.PublishObjects.Add( _
             SourceType:=xlSourceRange, _
             FileName:=TempFile, _
             Sheet:=Wks.Name, _
             Source:=Rng.Address, _
             HtmlType:=xlHtmlStatic)
            .Publish (True)
           End With
       
         ' Read the HTML file back as a string
           Set FSO = CreateObject("Scripting.FileSystemObject")
           Set HTMLfile = FSO.OpenTextFile(TempFile, ForReading, True, UseDefault)
           
            ' Read in the entire file as a string
              HTMLcode = HTMLfile.ReadAll
             
           HTMLfile.Close
          
          
         ' Re-align the HTML code to the left side of the page
           HTMLcode = Replace(HTMLcode, "align=center x:publishsource=", _
                              "align=left x:publishsource=")
                      
         ' Compose and send the email
           Set olEmail = olApp.CreateItem(olMailItem)
             With olEmail
               .To = Recipient
               .Subject = Subject
               .BodyFormat = olFormatHTML
               .HTMLBody = HTMLcode
               .Send
             End With
            
   ' Exit Outlook
     olApp.Quit
            
CleanUp:
   ' Did an error occur
     If Err <> 0 Then
        MsgBox "Run-time error '" & Err.Number & "':" & vbCrLf & vbCrLf & Err.Description
     End If
   
   ' Close the new workbook and don't save it
     ActiveWorkbook.Close SaveChanges:=False
  
   ' Delete the Temp File
     If Dir(TempFile) <> "" Then Kill TempFile
   
   ' Delete the Publish Object
     With ActiveWorkbook.PublishObjects
       If .Count <> 0 Then .Item(.Count).Delete
     End With
   
   ' Free memory resources
     Set olApp = Nothing
     Set olEmail = Nothing
     Set FSO = Nothing

End Sub

Sub EmailMyself()
  
  Dim Rng As Range
  
    'Set Rng = ThisWorkbook.Worksheets("Results").Range("A1:J39")
    EmailRangeInHTML "LeithRoss@gmail.com", "Sending Range in HTML test"
  
End Sub

CommandButton2_Click() Event Code
Be sure to change the email address to what you want to use.

Private Sub CommandButton2_Click()
' Working in Office 2000-2007
  EmailRangeInHTML "email@email.com", "Team Results - Month-To-Date", Worksheets("Results").Range("A1:J30")
End Sub

Hello seaottr,

Glad I could help. I still haven't discovered why the other code failed. I ran tests reading the email in Gmail and Outlook and the results were the same. If I discover the cause of problem I will let Ron De Bruin know about.

Hello daniela9488b,

I have since updated this macro. This newer version may run on Windows 8, but I have not tested it on Windows 8. Give this a try a let me know.

Macro to Send Range in Email Body

'Written: December 30, 2013
'Author:  Leith Ross
'Summary: Send a specfied worksheet range in the body of an Outlook email in HTML format.

Sub EmailRangeInHTML(ByVal Recipient As String, ByVal Subject As String, ByRef Range_To_Send As Range)

    Dim Bytedata()  As Byte
    Dim HTMLcode    As String
    Dim HTMLfile    As Object
    Dim olApp       As Object
    Dim TempFile    As String
    Dim Wks         As Worksheet
    
      ' Copy the worksheet to create a new workbook
        Set Wks = Range_To_Send.Parent
     
      ' The new workbook will be saved to the user's Temp directoy
        TempFile = Environ("Temp") & "\Temp Email.htm"
     
          ' Start Outlook
            Set olApp = CreateObject("Outlook.Application")
      
          ' Convert the Message worksheet into an HTML file.
            With Wks.Parent.PublishObjects
                .Add(SourceType:=xlSourceRange, _
                    Filename:=TempFile, Sheet:=Wks.Name, _
                    Source:=Range_To_Send.Address, HtmlType:=xlHtmlStatic) _
                .Publish Create:=True
            End With
       
          ' Read the HTML file back as a string.
            Open TempFile For Binary Access Read As #1
                ReDim Bytedata(LOF(1))
                Get #1, , Bytedata
            Close #1
          
            HTMLcode = StrConv(Bytedata, vbUnicode)
          
          ' Re-align the HTML code to the left side of the web page.
            HTMLcode = VBA.Replace(HTMLcode, "align=center x:publishsource=", "align=left x:publishsource=")
                      
          ' Activate the mail inspector. This must be done in Outlook 2010 and later to use Send.
            olApp.Session.getdefaultFolder 6
                     
          ' Compose and send the email.
            With olApp.CreateItem(olMailItem)
                .To = Recipient
                .Subject = Subject
                .BodyFormat = 2 ' HTML
                .HTMLBody = HTMLcode
                .Send
            End With
            
        Kill TempFile
        Wks.Parent.PublishObjects.Delete
        
End Sub

Macro Example

EmailRangeInHTML "YourEmail@Somewhere.com", "HTML Range Test", Range("A1:J33")

Hello daniela9488b,

The only ActiveX object being created in this macro is Outlook. Is it installed in Windows 8?

Hello daniela9488b,

Windows 8 is a very different OS and undoubtedly affects Office and VBA. I don't have an answer for you but will look into this problem.

Hello daniela9488b,

If you are using 2013 then it lacks support for Outlook VBA macros. You will need a third party add-in (app).

myles8908,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Curious about this code, Leith could you elaborate here. I brought your code over and am looking to understand what properties carry over the CF and/or if I will need to completely change to your code in order to make this happen.

Thanks

-Eddie

Protonspounge welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.