Results 1 to 4 of 4

Thread: Excel to HTML Text File - Formatting/Highlighting

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2020
    Posts
    3

    Question Excel to HTML Text File - Formatting/Highlighting

    Hello - I have a Microsoft Excel Workbook with VBA. I am using Microsoft Excel 2016. The VBA takes the information inputted onto one worksheet, creates an HTML text file, saves it to folder at a shared location, and e-mails the file to a specified e-mail address/s when a submit button is pressed. It also clears the form so that it is ready for the next time it's used. It currently does not copy any highlighting, text colors (other than black), or formatting in general. Is there a way to have it copy formatting, text colors or both? Below is the VBA being used for the worksheet.

    Code:
    Sub Button2_Click()
     
         Dim aOutlook As Object
         Dim aEmail As Object
         Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String
         Dim strSubject As String
         Dim strBody As String
         Dim strTemp As String
        
         Set aOutlook = CreateObject("Outlook.Application")
         Set aEmail = aOutlook.CreateItem(0)
         
         'Set Subject
         strSubject = ActiveSheet.Range("B2") & " PG" & UCase(ActiveSheet.Range("B3")) & " Executive Staff"
    
         aEmail.Subject = strSubject
         
         'Set Body for mail
         'Header and manpower HTML
         strBody = "<b><p style='font-family:calibri;font-size:18'>PG" & UCase(ActiveSheet.Range("B3")) & " Executive Staff End Report</p><br/>" & _
                   "<p style='font-family:calibri;font-size:14'> Date:</b> " & ActiveSheet.Range("B2") & "<br/><b>Report Completed by:</b> " & ActiveSheet.Range("B4") & "<br/><br/>" & _
                   "<b><u>Manpower:</u><br/>WC: </b>" & ActiveSheet.Range("B7") & "<br/><b>SBTA: </b>" & ActiveSheet.Range("B8") & _
                   "<br/><b>BTA: </b>" & ActiveSheet.Range("B9") & "<br/><b>KNI: </b>" & ActiveSheet.Range("B10") & "<br/><br/>"
    
         'GTH HTML
         If ActiveSheet.Range("B12").Value <> "" Then
            strTemp = ActiveSheet.Range("B12") & "<br/>"
         End If
         If ActiveSheet.Range("B13").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B13") & "<br/>"
         End If
         If ActiveSheet.Range("B14").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B14") & "<br/>"
         End If
         If ActiveSheet.Range("B15").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B15") & "<br/>"
         End If
         If ActiveSheet.Range("B16").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B16") & "<br/>"
         End If
             
         strBody = strBody & "<b><u>GTHs</b></u><br/>" & strTemp
         
         'ETR HTML
         If ActiveSheet.Range("B18").Value <> "" Then
            strTemp = ActiveSheet.Range("B18") & "<br/>"
         End If
         If ActiveSheet.Range("B19").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B19") & "<br/>"
         End If
         If ActiveSheet.Range("B20").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B20") & "<br/>"
         End If
         If ActiveSheet.Range("B21").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B21") & "<br/>"
         End If
         If ActiveSheet.Range("B22").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B22") & "<br/>"
         End If
        
         strBody = strBody & "<br/><b><u>ETRs</b></u><br/>" & strTemp
        
        'Assets HTML
         If ActiveSheet.Range("B24").Value <> "" Then
            strTemp = ActiveSheet.Range("B24") & "<br/>"
         End If
         If ActiveSheet.Range("B25").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B25") & "<br/>"
         End If
          If ActiveSheet.Range("B26").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B26") & "<br/>"
         End If
          If ActiveSheet.Range("B27").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B27") & "<br/>"
         End If
          If ActiveSheet.Range("B28").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B28") & "<br/>"
         End If
          If ActiveSheet.Range("B29").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B29") & "<br/>"
         End If
         If ActiveSheet.Range("B30").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B30") & "<br/>"
         End If
         If ActiveSheet.Range("B31").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B31") & "<br/>"
         End If
                       
         strBody = strBody & "<br/><b><u>Assets</b></u><br/>" & strTemp
                        
         'Patterns HTML
         'Zone 80
         strTemp = "<br/><b><u>Patterns</u><br/>Zone 80</b><BR/>"
         
         If ActiveSheet.Range("B34").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B34") & "<br/>"
         End If
         If ActiveSheet.Range("B35").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B35") & "<br/>"
         End If
         If ActiveSheet.Range("B36").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B36") & "<br/>"
         End If
         If ActiveSheet.Range("B37").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B37") & "<br/>"
         End If
         If ActiveSheet.Range("B38").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B38") & "<br/>"
         End If
         If ActiveSheet.Range("C34").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C34") & "<br/>"
         End If
         If ActiveSheet.Range("C35").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C35") & "<br/>"
         End If
         If ActiveSheet.Range("C36").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C36") & "<br/>"
         End If
         If ActiveSheet.Range("C37").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C37") & "<br/>"
         End If
         If ActiveSheet.Range("C38").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C38") & "<br/>"
         End If
         
         'Zone 90
         strTemp = strTemp & "<br/><b>Zone 90</b><BR/>"
         
         If ActiveSheet.Range("B40").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B40") & "<br/>"
         End If
         If ActiveSheet.Range("B41").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B41") & "<br/>"
         End If
         If ActiveSheet.Range("B42").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B42") & "<br/>"
         End If
         If ActiveSheet.Range("B43").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B43") & "<br/>"
         End If
         If ActiveSheet.Range("B44").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B44") & "<br/>"
         End If
         If ActiveSheet.Range("C40").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C40") & "<br/>"
         End If
         If ActiveSheet.Range("C41").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C41") & "<br/>"
         End If
         If ActiveSheet.Range("C42").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C42") & "<br/>"
         End If
         If ActiveSheet.Range("C43").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C43") & "<br/>"
         End If
         If ActiveSheet.Range("C44").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C44") & "<br/>"
         End If
         
         'Zone 100
         strTemp = strTemp & "<br/><b>Zone 100</b><BR/>"
         
         If ActiveSheet.Range("B46").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B46") & "<br/>"
         End If
         If ActiveSheet.Range("B47").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B47") & "<br/>"
         End If
         If ActiveSheet.Range("B48").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B48") & "<br/>"
         End If
         If ActiveSheet.Range("B49").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B49") & "<br/>"
         End If
         If ActiveSheet.Range("B50").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B50") & "<br/>"
         End If
         If ActiveSheet.Range("C46").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C46") & "<br/>"
         End If
         If ActiveSheet.Range("C47").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C47") & "<br/>"
         End If
         If ActiveSheet.Range("C48").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C48") & "<br/>"
         End If
         If ActiveSheet.Range("C49").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C49") & "<br/>"
         End If
         If ActiveSheet.Range("C50").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C50") & "<br/>"
         End If
         
         'Zone 110
         strTemp = strTemp & "<br/><b>Zone 110</b><BR/>"
         
         If ActiveSheet.Range("B52").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B52") & "<br/>"
         End If
         If ActiveSheet.Range("B53").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B53") & "<br/>"
         End If
         If ActiveSheet.Range("B54").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B54") & "<br/>"
         End If
         If ActiveSheet.Range("B55").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B55") & "<br/>"
         End If
         If ActiveSheet.Range("B56").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B56") & "<br/>"
         End If
         If ActiveSheet.Range("C52").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C52") & "<br/>"
         End If
         If ActiveSheet.Range("C53").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C53") & "<br/>"
         End If
         If ActiveSheet.Range("C54").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C54") & "<br/>"
         End If
         If ActiveSheet.Range("C55").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C55") & "<br/>"
         End If
         If ActiveSheet.Range("C56").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C56") & "<br/>"
         End If
    
         
         'Zone 120
         strTemp = strTemp & "<br/><b>Zone 120</b><BR/>"
         
         If ActiveSheet.Range("B58").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B58") & "<br/>"
         End If
         If ActiveSheet.Range("B59").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B59") & "<br/>"
         End If
         If ActiveSheet.Range("B60").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B60") & "<br/>"
         End If
         If ActiveSheet.Range("B61").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B61") & "<br/>"
         End If
         If ActiveSheet.Range("B62").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B62") & "<br/>"
         End If
         If ActiveSheet.Range("C58").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C58") & "<br/>"
         End If
         If ActiveSheet.Range("C59").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C59") & "<br/>"
         End If
         If ActiveSheet.Range("C60").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C60") & "<br/>"
         End If
         If ActiveSheet.Range("C61").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C61") & "<br/>"
         End If
         If ActiveSheet.Range("C62").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C62") & "<br/>"
         End If
         
         'Zone 250
         strTemp = strTemp & "<br/><b>Zone 250</b><BR/>"
         
         If ActiveSheet.Range("B64").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B64") & "<br/>"
         End If
         If ActiveSheet.Range("B65").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B65") & "<br/>"
         End If
         If ActiveSheet.Range("B66").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B66") & "<br/>"
         End If
         If ActiveSheet.Range("B67").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B67") & "<br/>"
         End If
         If ActiveSheet.Range("B68").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B68") & "<br/>"
         End If
         If ActiveSheet.Range("C64").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C64") & "<br/>"
         End If
         If ActiveSheet.Range("C65").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C65") & "<br/>"
         End If
         If ActiveSheet.Range("C66").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C66") & "<br/>"
         End If
         If ActiveSheet.Range("C67").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C67") & "<br/>"
         End If
         If ActiveSheet.Range("C68").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("C68") & "<br/>"
         End If
         
         'Job Watch HTML
         strTemp = strTemp & "<br/><b><u>Job Watch</b></u><BR/>"
         
         If ActiveSheet.Range("B71").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B71") & "<br/>"
         End If
         If ActiveSheet.Range("B72").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B72") & "<br/>"
         End If
         If ActiveSheet.Range("B73").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B73") & "<br/>"
         End If
         If ActiveSheet.Range("B74").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B74") & "<br/>"
         End If
              
        'FOH Activity HTML
         strTemp = strTemp & "<br/><b><u>FOH Activity</b></u><BR/>"
         
         If ActiveSheet.Range("B76").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B76") & "<br/>"
         End If
         If ActiveSheet.Range("B77").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B77") & "<br/>"
         End If
         If ActiveSheet.Range("B78").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B78") & "<br/>"
         End If
         If ActiveSheet.Range("B79").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B79") & "<br/>"
         End If
         If ActiveSheet.Range("B80").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B80") & "<br/>"
         End If
              
         'Significant Concerns HTML
         strTemp = strTemp & "<br/><b><u>Significant Concerns</b></u><BR/>"
         
         If ActiveSheet.Range("B82").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B82") & "<br/>"
         End If
         If ActiveSheet.Range("B83").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B83") & "<br/>"
         End If
         If ActiveSheet.Range("B84").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B84") & "<br/>"
         End If
         If ActiveSheet.Range("B85").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B85") & "<br/>"
         End If
         If ActiveSheet.Range("B86").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B86") & "<br/>"
         End If
              
         'Response HTML
         strTemp = strTemp & "<br/><b><u>Response</b></u><BR/>"
         
         If ActiveSheet.Range("B88").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B88") & "<br/>"
         End If
         If ActiveSheet.Range("B89").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B89") & "<br/>"
         End If
         If ActiveSheet.Range("B90").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B90") & "<br/>"
         End If
         If ActiveSheet.Range("B91").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B91") & "<br/>"
         End If
         If ActiveSheet.Range("B92").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B92") & "<br/>"
         End If
              
         'Truck Issues HTML
         strTemp = strTemp & "<br/><b><u>Truck Issues</b></u><BR/>"
         
         If ActiveSheet.Range("B94").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B94") & "<br/>"
         End If
         If ActiveSheet.Range("B95").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B95") & "<br/>"
         End If
         If ActiveSheet.Range("B96").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B96") & "<br/>"
         End If
         If ActiveSheet.Range("B97").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B97") & "<br/>"
         End If
         If ActiveSheet.Range("B98").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B98") & "<br/>"
         End If
         
         'Other Cases HTML
         strTemp = strTemp & "<br/><b><u>Other Cases</b></u><BR/>"
         
         If ActiveSheet.Range("B100").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B100") & "<br/>"
         End If
         If ActiveSheet.Range("B101").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B101") & "<br/>"
         End If
         If ActiveSheet.Range("B102").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B102") & "<br/>"
         End If
         If ActiveSheet.Range("B103").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B103") & "<br/>"
         End If
         If ActiveSheet.Range("B104").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B104") & "<br/>"
         End If
         
         'Customer Complaints HTML
         strTemp = strTemp & "<br/><b><u>Customer Complaints</b></u><BR/>"
         
         If ActiveSheet.Range("B106").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B106") & "<br/>"
         End If
         If ActiveSheet.Range("B107").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B107") & "<br/>"
         End If
         If ActiveSheet.Range("B108").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B108") & "<br/>"
         End If
         If ActiveSheet.Range("B109").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B109") & "<br/>"
         End If
         If ActiveSheet.Range("B110").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B110") & "<br/>"
         End If
         
         'Total Sales HTML
         strTemp = strTemp & "<br/><b><u>Total Sales</b></u><BR/>"
         strTemp = strTemp & "<b>Total Sales:</b>" & ActiveSheet.Range("B112") & "<br/>"
    
         'Administrative Actions HTML
         strTemp = strTemp & "<br/><b><u>Administrative Actions</b></u><BR/>"
         strTemp = strTemp & "<b>Qty Logged:</b>" & ActiveSheet.Range("B114") & "<br/>"
         strTemp = strTemp & "<b>People:</b>" & ActiveSheet.Range("B115") & "<br/>"
         strTemp = strTemp & "<b>FMTPs:</b>" & ActiveSheet.Range("B116") & "<br/>"
         strTemp = strTemp & "<b>JCAs:</b>" & ActiveSheet.Range("B117") & "<br/>"
         strTemp = strTemp & "<b>AJTs:</b>" & ActiveSheet.Range("B118") & "<br/>"
         strTemp = strTemp & "<b>Over 24hrs:</b>" & ActiveSheet.Range("B119") & "<br/>"
         strTemp = strTemp & "<b>Title TC:</b>" & ActiveSheet.Range("B120") & "<br/>"
         strTemp = strTemp & "<b>Delayed Title TC:</b>" & ActiveSheet.Range("B121") & "<br/>"
         strTemp = strTemp & "<b>Processed:</b>" & ActiveSheet.Range("B122") & "<br/>"
         strTemp = strTemp & "<b>Pending:</b>" & ActiveSheet.Range("B123") & "<br/>"
         strTemp = strTemp & "<b>In Bound:</b>" & ActiveSheet.Range("B124") & "<br/>"
         
         'Admin Support Requests HTML
         strTemp = strTemp & "<br/><b><u>Admin Support Requests</b></u><BR/>"
         
         If ActiveSheet.Range("B122").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B126") & "<br/>"
         End If
         If ActiveSheet.Range("B123").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B127") & "<br/>"
         End If
         If ActiveSheet.Range("B124").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B128") & "<br/>"
         End If
         If ActiveSheet.Range("B125").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B129") & "<br/>"
         End If
         If ActiveSheet.Range("B126").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B130") & "<br/>"
         End If
              
         'Executive Systems HTML
         strTemp = strTemp & "<br/><b><u>Executive Systems</b></u><BR/>"
         
         If ActiveSheet.Range("B132").Value <> "" Then
            strTemp = strTemp & ActiveSheet.Range("B132") & "<br/>"
         End If
         
         strBody = strBody & strTemp
         aEmail.HTMLBody = strBody
         
         
         'Set Recipient
         aEmail.Recipients.Add "sample@email.com"
         
         'Send Mail
         aEmail.Send
         
         'Save to network
         'ActiveWorkbook.SaveAs ("S:\Checklist\FY2020\Report" & ActiveSheet.Range("B2") & " PG" & Ucase(ActiveSheet.Range("B3")) & " Executive Staff.xlsm")
         
         'Save as HTML text File
         Dim fso As Object
         Dim Fileout As Object
         Dim strFolder As String
         
         strFolder = "s:\Checklist\FY2020\Report" & Format(ActiveSheet.Range("B2"), "mm-yyyy")
         
         If FolderExists(strFolder) = False Then
            MkDir (strFolder)
         End If
         
         Set fso = CreateObject("Scripting.FileSystemObject")
         
         Set Fileout = fso.CreateTextFile(strFolder & "" & Format(ActiveSheet.Range("B2"), "mm-dd-yyyy") & " PG" & UCase(ActiveSheet.Range("B3")) & " Executive Staff.html", True, True)
         
         Fileout.Write strBody
         Fileout.Close
         
         'Reset form
         ActiveSheet.Range("B2:B2").Value = "=Today()"
         ActiveSheet.Range("B3:B4").Value = ""
         ActiveSheet.Range("B7:B10").Value = ""
         ActiveSheet.Range("B12:B16").Value = ""
         ActiveSheet.Range("B18:B22").Value = ""
         ActiveSheet.Range("B24:B24").Value = ""
         ActiveSheet.Range("B25:B31").Value = ""
         ActiveSheet.Range("B34:B38").Value = ""
         ActiveSheet.Range("B40:B44").Value = ""
         ActiveSheet.Range("B46:B50").Value = ""
         ActiveSheet.Range("B52:B56").Value = ""
         ActiveSheet.Range("B58:B62").Value = ""
         ActiveSheet.Range("B64:B68").Value = ""
         ActiveSheet.Range("B71:B74").Value = ""
         ActiveSheet.Range("B76:B80").Value = ""
         ActiveSheet.Range("B82:B86").Value = ""
         ActiveSheet.Range("B88:B92").Value = ""
         ActiveSheet.Range("B94:B98").Value = ""
         ActiveSheet.Range("B100:B104").Value = ""
         ActiveSheet.Range("B106:B110").Value = ""
         ActiveSheet.Range("B112:B112").Value = ""
         ActiveSheet.Range("B114:B124").Value = ""
         ActiveSheet.Range("B126:B130").Value = ""
         ActiveSheet.Range("B132:B132").Value = ""
         ActiveSheet.Range("C34:C38").Value = ""
         ActiveSheet.Range("C40:C44").Value = ""
         ActiveSheet.Range("C46:C50").Value = ""
         ActiveSheet.Range("C52:C56").Value = ""
         ActiveSheet.Range("C58:C62").Value = ""
         ActiveSheet.Range("C64:C68").Value = ""
    
         Application.DisplayAlerts = False
         Application.Quit
         
    
    End Sub
    
    Function FolderExists(sFile As Variant) As Boolean
        On Error Resume Next
        If Len(sFile) > 0 Then
                FolderExists = (Len(Dir$(sFile, vbDirectory)) > 0&)
        End If
    End Function
    I appreciate any insights you can provide. Thank you.
    Last edited by iastshane; Sep 25th, 2020 at 06:32 PM.

  2. #2

    Thread Starter
    New Member
    Join Date
    Sep 2020
    Posts
    3

    Re: Excel to HTML Text File - Formatting/Highlighting

    I saw another recent thread that had a similar question but I did not see any answer to it within the thread. Like that person, I am new to VBA and do not know how to code. I did not write this VBA framework.
    Last edited by iastshane; Sep 25th, 2020 at 06:32 PM. Reason: removed redundancy from first post.

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2020
    Posts
    3

    Re: Excel

    empty
    Last edited by iastshane; Sep 28th, 2020 at 03:56 PM. Reason: redundant

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel to HTML Text File - Formatting/Highlighting

    first go back and edit your posts to wrap all the code in code tags, so it is more readable
    highlight the code and press the # button in the toolbar

    your code can be abbreviated by creating loops for ranges of cells

    to include the formatting you need to build that into the html code as you as you add the values, you need to determine which formatting types you want to get as i am sure there is too many options to include all and loop through each format option for each cell and add them to the html string

    there was a recent thread that may do what you want
    it used code by Ron de Bruin that copied the range to a new workbook, then published (saves) the workbook as html, copied the html string, from that file, to the email body
    i do not know if the thread was resolved as it was not reported back, the issue for that thread was images were not included and pastespecial was overwriting previous pastes
    the latter should have been easy to fix, but the images would have been more involved to include
    https://www.vbforums.com/showthread....-as-Email-Body
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width