Macros not working on excel sheet start up

Viewed 38

I am new to macros and I cant seem to find an answer to my problem. I have created a couple macros which work perfectly except for one issue. When I first open my excel sheet I have to step into my macro and "reset" it then it will work fine until I save, close and reopen the sheet the next time.

When I step into it the first line of it is highlighted yellow with an arrow to the left of it indicating an error but I'm not sure what the error is or how to pull up the error msg.

The line highlighted yellow is "Sub DueDateReminder()"

Private Sub Worksheet_calculate()
 
 Call DueDateReminder
 
End Sub
Sub DueDateReminder()

Dim c As Range
For Each c In Range("K19:K500")
    If c.Value = "No" Then
        
        c.Value = "Yes"
        Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutMail = xOutApp.CreateItem(0)
    xMailBody = "Hello " & c.Offset(0, 4) & vbNewLine & vbNewLine & _
              "This in automatic message to inform you that you have an upcoming due date regarding " & c.Offset(0, -3).Text & " on " & c.Offset(0, -1) & vbNewLine & vbNewLine & _
              "Best Regards!"
    On Error Resume Next
    With xOutMail
        .To = c.Offset(0, 3)
        .CC = ""
        .BCC = ""
        .Subject = "Project Review Meeting Due Date"
        .Body = xMailBody
        .Attachments.Add
        .display ' use .send for automatic email
    End With
    On Error GoTo 0
    Set xOutMail = Nothing
    Set xOutApp = Nothing
        End If
        Next c
End Sub
0 Answers
Related