Autofilter rows error: Run-time error '91': Object variable or With block variable not set

81 Views Asked by At

I am trying to email multiple users with multiple rows. I use the autofilter to filter out the rows.

Sometimes it errors:

Run-time error '91': Object variable or With block variable not set

Set rng = Sheet20.AutoFilter.Range.Resize(Sheet20.AutoFilter.Range.Rows.Count - 1).Columns("$M:$U")

Full code:

Sub CashEmail()
Application.ScreenUpdating = False
Sheet20.AutoFilterMode = False
Dim OutApp As Object, OutMail As Object
Dim rng As Range, i As Long, v As Variant
v = Sheet20.Range("$M$20:$V" & Sheet20.Cells(Sheet20.Rows.Count, "$M").End(xlUp).Row).Value ' Modify the range to include columns A to L and V to W
Set OutApp = CreateObject("Outlook.Application")
With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(v, 1)
        If Not .Exists(v(i, 10)) And v(i, 10) <> "" Then
            .Add v(i, 10), Nothing
            With Sheet20.Range("$M21:V$21") ' Modify the range to include columns A to B and E to F
                .CurrentRegion.AutoFilter Field:=10, Criteria1:=v(i, 10)
                Set rng = Sheet20.AutoFilter.Range.Resize(Sheet20.AutoFilter.Range.Rows.Count - 1).Columns("$M:$U")
                Set OutMail = OutApp.CreateItem(0)
                With OutMail
                    .To = v(i, 10)
                    .Subject = "xxxx"
                    .HTMLBody = RangetoHTML(rng)
                    .Display
                End With
            End With
        End If
    Next i
End With
Sheet20.AutoFilterMode = False
Sheet20.ShowAllData
Application.ScreenUpdating = True
End Sub

I tried to turn off the autofilter = false at the beginning.

0

There are 0 best solutions below