Skipping hidden rows

Question

I have a workbook with a main page control hub to send multiple emails in a sequence.

After I send the first email to the table of recipients I record those who respond. I filter them out before sending the next email.

I am trying to skip the hidden rows as they contain contacts who do not need to receive the second email.

Sub Send_seq_two()

Dim sh As Worksheet
Dim sh2 As Worksheet
Set sh = ThisWorkbook.Sheets("TheHub")
Set sh2 = ThisWorkbook.Sheets("Tables")
Set sh3 = ThisWorkbook.Sheets("Contacts")
Set OA = CreateObject("Outlook.Application")
Dim msg As Object
Dim sign As String
Dim I As Integer

Dim tbl As ListObject
Set tbl = Application.Range("mt").ListObject

Dim lrow As Integer
lrow = tbl.Range.Rows(tbl.Range.Rows.Count).Row

If sh.Range("B2").Value <> "2" Or sh3.Range("K6").Value = "1" Then
    MsgBox "check sequence"
End If

For I = 6 To lrow

    Set msg = OA.CreateItem(0)

    If sh3.Range("H" & I).Value <> "" And sh3.Range("K" & I).Value = "" And sh3.Range("J" & I).Value = "1" And sh.Range("B2").Value = "2" Then

        msg.Display
        sign = msg.HTMLBody

        msg.To = sh3.Range("H" & I).Value
        msg.CC = sh3.Range("I" & I).Value
        msg.Subject = sh.Range("B3").Value
        msg.HTMLBody = "<p><span style='font-size:15px;font-family:Calibri,sans-serif;'>Hi " & sh3.Range("D" &     I).Value & ",<br><br>" & sh2.Range("D3").Value & sign

        If sh.Range("B4").Value <> "" Then
            msg.attachments.Add sh.Range("B4").Value
        End If

        msg.Send
    
        sh.Range("C14").Value = Date
        sh3.Range("K" & I).Value = "1"

    End If

Next I

End Sub

I tried the hidden row property but I think I am applying it incorrectly. I only want to skip the hidden rows. I do not want to delete them.

Answer 1

Hopefully you can modify below code as your requirements

dim i as Range, emailRng as Range, r as Long
Set sh3 = ThisWorkbook.Sheets("Contacts")

' to figure out only visible cells after applying filter
set emailRng = sh3.Range("H1:H" & lrow).SpecialCells(xlCellTypeVisible)

for each i in emailRng
    r = i.row
    msg.To = sh3.Range("H" & r).Value
    msg.CC = sh3.Range("I" & r).Value
Next