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.