VBA:Run Time Error 91 Object Variable or With Block variable not set

[Morshed_Dhaka]

Hello Everyone,

Good day to all of you.

I am really struggling to run this VBA code. When I run this code, its perfect did the work which I am looking for but at the end, it shows " Run Time Error '91' Object Variable or With block variable not set "

Below is my code. If anybody correct the code that where is the problem, It will be really helpful for me. Thanks in advance.

VB Code :

Module 01 :

Sub Worksheet_Calculate()
    Dim BillPending As Range
    Dim MyLimit As Double
    MyLimit = 0
    Set BillPending = Sheets("APRIL").Range("AN1:AN38")
    On Error GoTo EndMacro:
    For Each mailid In BillPending.Cells
        With mailid
                If .Value > MyLimit Then
                        Call Mail_with_outlook2
                    End If
            Application.EnableEvents = False
            Application.EnableEvents = True
        End With
    Next mailid
ExitMacro:
    Exit Sub
EndMacro:
    Application.EnableEvents = True
    MsgBox "Some Error occurred." _
End Sub

Module 02 :

Option Explicit
Public mailid As Range
Sub Mail_with_outlook2()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail= OutApp.CreateItem(0)
    
    strto = Cells(mailid.Row, "AL").Value
    strcc = ""
    strbcc = ""
    strsub = "Your subject"
    strbody = "Hi " & Cells(mailid.Row, "AM").Value & vbNewLine & vbNewLine & _
              "Your total of this week is : " & Cells(mailid.Row, "AN").Value & _
              vbNewLine & vbNewLine & "Good job"
    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[Morshed_Dhaka]

anyone with any solution or suggestion ??? Sorry for my reply. I want to see it in the top items for seeking solution.

 

[Michael M]

Are there any blanks in AN1:AN38 ??
IS there a number in AN1 or is it the column header ??

 

[Morshed_Dhaka]

Hi Michael,

from AN1 to AN38, all are numbers, no blank cell, no header.

 

[Michael M]

Hmm...works for me with my test data !!
The data is definitely on the APRIL Sheet ??

 

[Morshed_Dhaka]

yes Michael. the data is in the APRIL sheet

 

[dmt32]

Hi,
not tested but see if updates to your codes help


Worksheet Code Page:

Sub Worksheet_Calculate()
    Dim BillPending As Range, mailid As Range
    Dim MyLimit As Double
    MyLimit = 0
    Set BillPending = Sheets("APRIL").Range("AN1:AN38")
    On Error GoTo ExitMacro
    Application.EnableEvents = False
    For Each mailid In BillPending.Cells
        If mailid.Value > MyLimit Then Call Mail_with_outlook2(mailid)
    Next mailid
    
ExitMacro:
    Application.EnableEvents = True
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

STANDARD Module

Sub Mail_with_outlook2(ByVal Target As Range)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    strto = Target.Offset(, -2).Value
    strcc = ""
    strbcc = ""
    strsub = "Your subject"
    strbody = "Hi " & Target.Offset(, -1).Value & vbNewLine & vbNewLine & _
              "Your total of this week is : " & Target.Value & _
              vbNewLine & vbNewLine & "Good job"
              
    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Display
    End With
    
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Dave

 

[Morshed_Dhaka]

Hi dmt32, it works like a charm . thanks a lot.

 

[dmt32]

Hi dmt32, it works like a charm . thanks a lot.

welcome & glad update helped - feedback very much appreciated.

Dave