Feb 08 2023 08:47 AM
I am using the following code in a workbook. The idea is that if a date is added in Column T, an email will generate that pulls other data into the body of the email and displays it before sending. I have used a similar code before, but for the life of me I can't understand why this one is not working. I'm not even getting an error. It just exits the sub and that's it. Any idea as to what is happening?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim objOL As Object
Dim objMsg As Object
' Get out if multiple cells have been changed
If Target.CountLarge > 1 Then Exit Sub
' Get out if the changed cell is not in column T
If Target.Column <> 20 Then Exit Sub
' Get out if the changed cell does not contain a date
If Not IsDate(Target.Value) Then Exit Sub
' Get out if the cell in column Q in the same row is empty
If Target.Offset(0, -3).Value = "" Then Exit Sub
' Start Outlook
Set objOL = CreateObject("Outlook.Application")
' Create a new message
Set objMsg = objOL.CreateItem(0) ' olMailItem
' Recipient
objMsg.To = "removed for privacy"
objMsg.CC = "removed for privacy"
' Subject
objMsg.Subject = "Work Order Completed"
' Body text
objMsg.Body = Target.Offset(0, -17).Value & " work order number " & Target.Offset(0, -3).Value & ", requested by " & Target.Offset(0, -18).Value & " and assigned to " & Target.Offset(0, -2).Value & ", was completed on " & Format(Target.Value, "mm/dd/yyyy")
' show the message so that the user can inspect it
objMsg.Display
Application.CalculateFull
End Sub
Feb 08 2023 12:24 PM
SolutionClick in the first line
Private Sub Worksheet_Change(ByVal Target As Range)
and press F9 to set a breakpoint.
Enter or edit a date in a cell in column T.
The code will pause at the breakpoint.
Press F8 repeatedly to execute the code line by line, and observe carefully at which point it suddenly stops.
Feb 08 2023 12:32 PM
Feb 08 2023 12:50 PM
Feb 08 2023 12:24 PM
SolutionClick in the first line
Private Sub Worksheet_Change(ByVal Target As Range)
and press F9 to set a breakpoint.
Enter or edit a date in a cell in column T.
The code will pause at the breakpoint.
Press F8 repeatedly to execute the code line by line, and observe carefully at which point it suddenly stops.