Forum Discussion

Edg38426's avatar
Edg38426
Copper Contributor
Feb 08, 2023
Solved

VBA Code for sending email not working

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

 

 

 

  • Edg38426 

    Click 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.

4 Replies

  • Edg38426 

    Click 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.

    • Edg38426's avatar
      Edg38426
      Copper Contributor
      I figured it out. I wasn't paying attention and had inserted the code into a module instead of where it needed to be. Sorry to waste your time!
    • Edg38426's avatar
      Edg38426
      Copper Contributor
      I'm not seeing it do ANYTHING in the VBA editor.

Resources