CODEWITHSUNDEEP
excelvbaoutlook
Harry Wood
Harry Wood

Reputation: 13

VBA Run time error 91 when executing code

I am working on a spreadsheet where column 23 (w) is keeping track of column AA which is a day counter. When the day counter reaches over 14, then column 23 (w) will say the word "Overdue" and my code will send out an email to say it is overdue. I am using a formula to make the cell read overdue, hence why I am trying to use _Calculate in my code. But when I try and manipulate the day tracker to read overdue, I get the following error, and I don't know why.

Runtime error 91: Object variable or with block variable not set

Private Sub Worksheet_Calculate()
    Dim target As Range

    If target.Column = 23 And target.Value = "Overdue" Then
        With CreateObject("Outlook.Application").createitem(0)
            .To = "[email protected]"
            .Subject = "Item OverDue"
            .Body = "Dear User," & vbNewLine & vbNewLine & "An Item has been marked as Overdue. Please open up the workbook to assess."
            .send
        End With
    End If
End Sub

Upvotes: 1

Views: 79

Answers (2)

user11440229
user11440229

Reputation: 21

Worksheet_Calculate has no Target argument. You may be mashing up some related Worksheet_Change code.

Since Target is not being passed into the Worksheet_Calculate, you decided to Dim it but never assign (Set) any cell or range of cells to it.

Target is Nothing and Nothing has no column or Value property.

You might try looping through the values in column W to see if any are Overdue but that might mean that duplicate emails would be sent out. Perhaps set up another column where TRUE/FALSE indicates whether an email has already been sent.

Assuming that column Z is unused you could use that to indicate whether an overdue email has already been sent. This method survives closing and reopening the workbook so long as the changes are saved when closing.

Option Explicit

Private Sub Worksheet_Calculate()

    Dim rng As Range

    For Each rng In Range(Cells(2, "W"), Cells(Rows.Count, "W").End(xlUp))
        If LCase(rng.Text) = "overdue" And Not rng.Offset(0, 3) Then

            With CreateObject("Outlook.Application").createitem(0)
                .To = "[email protected]"
                .Subject = "Item OverDue"
                .Body = "Dear User," & vbNewLine & vbNewLine & "An Item has been marked as Overdue. Please open up the workbook to assess."
                .send
            End With

            rng.Offset(0, 3) = True
        End If
    Next rng
End Sub

Upvotes: 2

aucuparia
aucuparia

Reputation: 2051

Unlike Selection_Change, the Calculate event isn't associated with a target cell (the whole worksheet is calculated at once). So your target object never refers to anything, hence the error 91 (which is Object variable not set).

There is a Worksheet_Change event (see https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.change) which does pass a target, but this is not triggered when a formula changes value, only when a user makes the change directly. So this probably won't work for you.

I think you have two choices:

  • if the change you want to track is caused by a user editing the sheet somewhere, then change your event handler to Worksheet_Change, look for the actual cell that the user changed (e.g. column AA), and then repeat the overdue calculation.
  • if, as it seems, the change isn't caused by user input then you will need to modify your Worksheet_Calculatehandler to loop through all the relevant cells and check for any with value "Overdue".

Upvotes: 1

Related Questions