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