Finding LastRow after filter

[maxfli]

I am really close to having this process working. I am sending emails with attachments for a table on Sheet1 after I filter on column 12. The problem is

For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

Does not find the last row after the filter is applied. It is finding the entire 1,004,000 rows in the spreadsheet. Can anyone offer a fix to the code.
Thanks

Sub create_multiple_emails()
  Dim wb As Workbook, sh As Worksheet, c As Range, m As Range
  Dim sBody As String, wFile As String
  Dim dam As Object, dict As Object
  Dim pCode As String
  Dim fileN As String
  Dim sOutTo As String
  Dim sOutSub As String
  Dim sOutInv As String
  Dim sOutCont As String
  Dim sOutDue As String
  Dim sOutFNm As String
  Dim answer As Integer
  Dim lastRow As Long
 
  pCode = InputBox("Enter Pull Code for mailing")
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  '
  Set sh = ActiveSheet
  Set dict = CreateObject("scripting.dictionary")
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  sh.Range("A1").AutoFilter Field:=12, Criteria1:=pCode
  For Each c In sh.Range("A2", sh.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)
        sOutFNm = c.Value
        sOutInv = c.Offset(, 2).Value
        sOutTo = c.Offset(, 7).Value
         sOutCont = c.Offset(, 9).Value
          sOutDue = c.Offset(, 10).Value
    

 
 Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'On Error Resume Next
With OutMail
.To = sOutTo
.Subject = "AT&T Capital Services invoice #" & sOutInv & " Contract # " & sOutCont & " Due " & sOutDue
.HTMLBody = "<b>" & "INVOICE ATTACHED: Due " & sOutDue & "</b>" & "<br>" _
& "I have attached the AT&T Capital Services invoice #" & sOutInv & " " & ". Please confirm receipt." & "<br>" _
& " Let us know if there are any changes to the billing contact or mailing address on the invoice." & "<br>" _
& " I appreciate your help." & "<br>" _
& "<br>" _
& "<br>" _
& "Thank you" & "<br>" _
& MyName & "<br>" _
& "Capital Services, Inc."
.Attachments.Add sPath & "\" & sOutFNm
.ReadReceiptRequested = True
.OriginatorDeliveryReportRequested = True
.display
Range("P" & Rows.Count) = "Invoice sent to customer on " & Format(Now, "mm-dd-yy") & " " & MyName 'Environ$("username")
End With
  Next
  sh.ShowAllData
   MsgBox "Emails sent"
End Sub

 

[BSALV]

You said table, so i wrote something for a table
you have to check first if there is at least 1 cell visible left, otherwise you get an error

For Each c In sh.Range("A2").ListObject.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

 

[maxfli]

I guess I shouldn’t refer to it as a table. I get an Object variable or with block variable not set.
It is simply rows of data in sheet1.

 

[maxfli]

Just to be sure I turned Off the AutoFilter on field 12. This ensured I had data in the left most A2 cell. I still get the block variable not set error. Is there any other possible solutions???

 

[BSALV]

just an idea without testing

     With sh.Range("A1").CurrentRegion 'the range around A1
          .AutoFilter Field:=12, Criteria1:=pcode 'filter your 12th column
          For Each c In .Columns(1).SpecialCells(xlCellTypeVisible) 'the remaining visible cells of currentregion
               If c.Row <> .Row Then 'not the headerrow
                   ....

 

[Osvaldo Palmeiro]

Maybe:

sh.Range("A1").AutoFilter Field:=12, Criteria1:=pCode
LastRow=sh.AutoFilter.Range.Columns(1).SpecialCells(12).Count-1