Run-Time Error 91 Object variable or With block variable not set

Question

I am getting a "Run-Time Error 91 Object variable or With block variable not set" error. The code worked once or twice and, after that, it stopped working.

Can anyone help with what I am doing incorrectly?

I am getting the error with the following code, on the lines indicated below:

Private Sub CommandButton1_Click()
    Dim Sht As Excel.Worksheet
    Set Sht = ThisWorkbook.ActiveSheet
 
    Recip = Worksheets("STIF Report").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6) ----!
   
    Custody = Worksheets("STIF Report").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5) ----!
   
    Dim rng As Range
    Set rng = Sht.Range("B43:D85")
        rng.Copy
   
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
 
    Dim OutMail As Object
    Set OutMail = OutApp.CreateItem(0)
 
    Dim vInspector As Object
    Set vInspector = OutMail.GetInspector
   
    Dim wEditor As Object
    Set wEditor = vInspector.WordEditor
    
    With OutMail
        .TO = Recip
        .CC = ""
        .Subject = "STIF Vehicle Confirmation" & " - " & Custody ----!
        .display
        
         wEditor.Paragraphs(1).Range.Text = "Hello All," & Chr(11) & Chr(11) & "I hope this email finds you all doing well." & Chr(11) & Chr(11) & _
         "Can you please confirm if the below STIF vehicle details are accurate for the accounts below? If the vehicle has changed, can you please confirm the new STIF vehicle name and CUSIP?" & vbCrLf
        
         wEditor.Paragraphs(2).Range.Paste
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

@BigBen I am still having issues with this piece of code. I have tried everything and there is nothing that seems to work... — Mamamia93
@BigBen There are two tables on the same sheet. The table I want to have pasted in the email body is the second table under anther table. That is the reason the range is starting from row 43. I am filtering that table, and, depending on the information presented after filtering; I want that information in the email. Does that make sense? — Mamamia93
@BigBen I did as you said and added the 'ListObjects("Email")' and it works perfectly. Thank you for taking the time to help me. — Mamamia93

Mamamia93 Mamamia93 · Accepted Answer · 2020-07-13T19:08:25

It seems that the issue I had is because of multiple tables on the same sheet, and the macro had a hard time understanding which table I was referring to. There are two tables on the sheet (one on top of the other), and I needed the second table pasted in an email body after filtering the information I wanted to appear. To do that, I had to name the table; in this case, 'Email,' and call it with the .ListObjects property, which in this case is .ListObjects("Email") for the macro to understand which table I wanted it to focus on.

You can see the updated code below:

Private Sub CommandButton1_Click()
    Dim Sht As Excel.Worksheet
    Set Sht = ThisWorkbook.ActiveSheet
    Recip = Worksheets("STIF Report").ListObjects("Email").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6)
  
    Custody = Worksheets("STIF Report").ListObjects("Email").AutoFilter.Range.Offset(2).SpecialCells(xlCellTypeVisible).Cells(1, 5)
  
    Dim rng As Range
    Set rng = Sht.Range("B43:D85")
        rng.Copy
  
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
    Dim OutMail As Object
    Set OutMail = OutApp.CreateItem(0)
    Dim vInspector As Object
    Set vInspector = OutMail.GetInspector
  
    Dim wEditor As Object
    Set wEditor = vInspector.WordEditor
   
    With OutMail
        .TO = Recip
        .CC = ""
        .Subject = "STIF Vehicle Confirmation" & " - " & Custody
        .display
       
         wEditor.Paragraphs(1).Range.Text = "Hello All," & Chr(11) & Chr(11) & "I hope this email finds you all doing well." & Chr(11) & Chr(11) & _
         "Can you please confirm if the below STIF vehicle details are accurate for the accounts below? If the vehicle has changed, can you please confirm the new STIF vehicle name and CUSIP?" & vbCrLf
       
         wEditor.Paragraphs(2).Range.Paste
    End With
End Sub

Run-Time Error 91 Object variable or With block variable not set

1 Answers