Macro to copy a Frozen column & the next visible column

[jamesraywebber]

So I have this code:

Sub Mail_Sheet_Outlook_Body()
'For Tips see: 
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2016
    Dim rng As Range
    Dim sch As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim A As String
    Dim D As String
    Dim C As Range
    Dim B As Range
    Dim r As Range
    
    Set r = Range("B12:NC27")
       
    D = Worksheets("TSA Daily").Range("B1").Value
         
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    Set rng = Nothing
    Set rng = Sheets("TSA Schedule").Range("A1:A9")
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .To = "CorpSupervisors@ethernet.com"
        .CC = "RTA@ethernet.com; someone@ethernet.com"
        .BCC = ""
        .Subject = "*****TSA Schedule " & D & "*****"
        .HTMLBody = RangetoHTML(rng) & "In the event that Michael is not onsite the TSA's can reach him at 555.123.4567 or contact Me(me@ethernet.com) with any questions and/or concerns."
        .Display   'or use .Display
    End With
    On Error GoTo 0


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


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub




Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    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"




    'Copy the range and create a new workbook to past the data in
    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


    'Publish the sheet to a htm file
    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


    'Read all data from the htm file into RangetoHTML
    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=")


    'Close TempWB
    TempWB.Close SaveChanges:=False


    'Delete the htm file we used in this function
    Kill TempFile


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

This code creates an email for me and copies info from my sheet called TSA Schedule. On the sheet it hides the column with the date that has already pasted so its constantly changing. When I click on my button it creates the email and only copies the range A1:A9. I also want it to put in the email the ranges A12:A27 & then put right beside that the next visible column range from A12:A27. Any help would be greatly appreciated.