HELP! Runtime error 13 Sending two non-contiguous rows as a table

Hi,

I have the code below and I'm trying to send two non-contiguous rows in an email to make it look like a table. I have this line of code below that's causing the error.

Set rng3 = Sheets("Jan. 2012").Range("A11:S11").SpecialCells(xlCellTypeVisible) & Sheets("Jan. 2012").Range(Cells(i, 1), Cells(i, 19)).SpecialCells(xlCellTypeVisible)

How else would I do this?? Please help. The entire code is below

Private Sub Workbook_Open()
        
    Dim Sdate As String
    Dim ol As Outlook.Application
    Dim olMsg As Outlook.MailItem
    Set objOutlook = CreateObject("outlook.application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Dim olNameSpace As Outlook.Namespace
    Dim olInbox As Outlook.MAPIFolder
    Dim rng As Range
    Dim rng3 As Range
    
    
    
    
'Make Outlook visible
Set ol = New Outlook.Application
Set olNameSpace = ol.GetNamespace("MAPI")
Set olInbox = olNameSpace.GetDefaultFolder(olFolderInbox)
olInbox.Display

Set rng = Sheets("Template").Range("A3:S29").SpecialCells(xlCellTypeVisible)

    Dim i As Integer

For i = 12 To 14

        With objOutlookMsg
        Set objOutlook = CreateObject("outlook.application")
        Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
         Set rng3 = Sheets("Jan. 2012").Range("A11:S11").SpecialCells(xlCellTypeVisible) & Sheets("Jan. 2012").Range(Cells(i, 1), Cells(i, 19)).SpecialCells(xlCellTypeVisible)
        .Subject = "Action Required for Top Driver " & Cells(i, 1).Value & " " & Cells(i, 2).Value
        .HTMLBody = RangetoHTML(rng) & RangetoHTML(rng3)
        .To = Cells(i, 11).Value
        .CC = Cells(i, 12).Value
        .Importance = 2
        .Send
        End With
    
Next i
End Sub

the RangetoHTML function is below also

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

Hi g1987, I don't know if this is the issue but your line:

Set rng3 = Sheets("Jan. 2012").Range("A11:S11").SpecialCells(xlCellTypeVisible) & Sheets("Jan. 2012").Range(Cells(i, 1), Cells(i, 19)).SpecialCells(xlCellTypeVisible)

does not explicitly call the cells on Sheets("Jan. 2012"). try

Set rng3 = Sheets("Jan. 2012").Range("A11:S11").SpecialCells(xlCellTypeVisible) & Sheets("Jan. 2012").Range(sheets("Jan. 2012").Cells(i, 1), sheets("Jan. 2012").Cells(i, 19)).SpecialCells(xlCellTypeVisible)

If you have a lot of lines that are being declared from sheet("Jan. 2012") then you may be better off explicitly declaring the worksheet as WsJan like in the following:

Sub aDifferentApproach()
    Dim WsJan As Worksheet
    Dim Rng3 As Range
    
    Set WsJan = Worksheets("Jan. 2012")
    Set Rng3 = WsJan.Range("A11:S11").SpecialCells(xlCellTypeVisible) & WsJan.Range(WsJan.Cells(i, 1), WsJan.Cells(i, 19)).SpecialCells(xlCellTypeVisible)
End Sub

Why would you post the same question twice? All this does is clutter the forum. Thread will be closed.

No worries, the dup post has been taken care of.