Send email when cells in specific column are changed.

[js1127]

I have been trying to get a macro working to send an email when a date is added to a certain column in Excel. I thought I had found the right code. But it is not sending the email when I test it. How do I set this up? I would like an email to go out to the address in a cell when another cell is updated. This is the code I am trying to use. But I am not getting the email.

Sub SendMail()
Dim OutApp As Object
Dim OutMail As Object
Dim RelDate As Range
Dim lastRow As Long
Dim dateCell, dateCell1 As Date

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
lastRow = Range("A" & Rows.Count).End(xlUp).Row
On Error GoTo cleanup
For Each RelDate In Range("M2:M" & lastRow)
If RelDate = "" Then GoTo 1
dateCell = RelDate.Value
dateCell1 = Cells(RelDate.Row, "L").Value
If dateCell <> dateCell1 Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Cells(RelDate.Row, "K").Value
.Subject = "Release Date Changed"
.Body = "Dear " & Cells(RelDate.Row, "A").Value _
& vbNewLine & vbNewLine & _
"The release date of " & Cells(RelDate.Row, "H").Value & _
" is changed to " & dateCell _
& vbNewLine & vbNewLine _
& vbNewLine & vbNewLine & _
"Regards," & vbNewLine & _
"Your Name"
.send
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Cells(RelDate.Row, "L").Value = dateCell
RelDate.ClearContents
1: Next RelDate
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[Micron]

Use Private Sub Worksheet_Change(ByVal Target As Range) and IF the changed cell is inside your range, Offset to get the address value for your email?

Please paste code between vba tags (vba button on posting toolbar) and use proper indentation for more than a few lines of code.

 

[js1127]

Use Private Sub Worksheet_Change(ByVal Target As Range) and IF the changed cell is inside your range, Offset to get the address value for your email?

Please paste code between vba tags (vba button on posting toolbar) and use proper indentation for more than a few lines of code.

Sub SendMail()
Dim OutApp As Object
Dim OutMail As Object
Dim RelDate As Range
Dim lastRow As Long
Dim dateCell, dateCell1 As Date

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
lastRow = Range("A" & Rows.Count).End(xlUp).Row
On Error GoTo cleanup
    For Each RelDate In Range("M2:M" & lastRow)
    If RelDate = "" Then GoTo 1
    dateCell = RelDate.Value
    dateCell1 = Cells(RelDate.Row, "L").Value
    If dateCell <> dateCell1 Then
          Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = Cells(RelDate.Row, "K").Value
                .Subject = "Release Date Changed"
                .Body = "Dear " & Cells(RelDate.Row, "A").Value _
                        & vbNewLine & vbNewLine & _
                        "The release date of " & Cells(RelDate.Row, "H").Value & _
                        " is changed to " & dateCell _
                        & vbNewLine & vbNewLine _
                        & vbNewLine & vbNewLine & _
                        "Regards," & vbNewLine & _
                        "Your Name"
                .send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
        Cells(RelDate.Row, "L").Value = dateCell
        RelDate.ClearContents
1:  Next RelDate
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

 

[js1127]

Sub SendMail()
Dim OutApp As Object
Dim OutMail As Object
Dim RelDate As Range
Dim lastRow As Long
Dim dateCell, dateCell1 As Date

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
lastRow = Range("A" & Rows.Count).End(xlUp).Row
On Error GoTo cleanup
    For Each RelDate In Range("M2:M" & lastRow)
    If RelDate = "" Then GoTo 1
    dateCell = RelDate.Value
    dateCell1 = Cells(RelDate.Row, "L").Value
    If dateCell <> dateCell1 Then
          Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = Cells(RelDate.Row, "K").Value
                .Subject = "Release Date Changed"
                .Body = "Dear " & Cells(RelDate.Row, "A").Value _
                        & vbNewLine & vbNewLine & _
                        "The release date of " & Cells(RelDate.Row, "H").Value & _
                        " is changed to " & dateCell _
                        & vbNewLine & vbNewLine _
                        & vbNewLine & vbNewLine & _
                        "Regards," & vbNewLine & _
                        "Your Name"
                .send
            End With
            On Error GoTo 0
            Set OutMail = Nothing
        End If
        Cells(RelDate.Row, "L").Value = dateCell
        RelDate.ClearContents
1:  Next RelDate
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

I changed my columns to match the code, or so I thought. I am very new to this. I changed Column A to name, K to email address, H to item, L to date field and M to date field. When M column is changed, email is sent to email address in K. I don't really need L but this code needed it so I added it and figured I would work around it.

 

[Micron]

You haven't said much about what's wrong/what's happening (doesn't work doesn't really help anyone to help you).
As I mentioned, if you want this to happen when a cell value is changed, put all that into the code for that event (except Sub and End Sub lines of course).
Or use that event to get the values and pass them to your posted code. To do that you'd have to change the sub structure to accept those values such as

SendMail(strName As String, strEmail As String, strItem As String, strLDate As String, strMDate As String)

and that assumes all your values are string data type.
Where is that sub? If in a standard module, you could put a break point at the start, put the cursor in the code, press F5 and step through your code with F8 and watch what happens.

 

[js1127]

Thank you so much for your quick responses today. I really appreciate it. I am thinking this is way over my head. I thought I could copy and paste the code I found on this site and change the columns on my spreadsheet to match the post I found on this site.

When I said it is not working, I meant I am not getting the email when I run the macro. I update the field in the worksheet, then click on Alt + F8. I don't get the email.

 

[Micron]

I think we can work through it but I'll need a bit of time to digest it all; speaking of which, it's din-din time here. Someone might even beat me to a procedure that works.

Did you try stepping through as I advised? Might help to know what's going on. This or one like it might be very helpful to you.

 

[Micron]

Forgot to mention that you need to explain what a "change" is exactly. Without worrying about it, an email would be sent for any of the following:
- change "dog" to "cat" in the column
- change nothing (aka empty string or zero length string - "" ) to anything
- delete anything
- change "dog" to "dog"

So your code might need to compare new and old values? That will complicate it slightly.

 

[Micron]

Am scrutinizing your email code now. This might very well be your issue: OutApp.Session.Logon
If Outlook is already running, a new session will not start. I imagine you never got prompted to log into Outlook, thus your mail was not sent.

Other observations to come as I find them if they seem worthy of pointing out. For now,
- Dim dateCell, dateCell1 As Date << dateCell is a variant because it's not explicitly declared. Maybe that's what you wanted - don't know. If not then must be
Dim dateCell As Date, dateCell1 As Date
- Unless I've misinterpreted, if your code had worked the loop would send an email for every date from M2 to column end. If that was 1K rows, then 1K emails, perhaps many to the same recipients regardless of what the date values were in that column. Surely that is not what you want?
- I plan to treat date variables as strings since there's no <> comparison and strings will be what goes into the email body anyway.
- I don't see the sense in

            On Error Resume Next
            With OutMail

That's saying if any part of the email construct fails (e.g. an invalid To value that raises an error) that is telling it to ignore and try to build the rest of the email.

 

[js1127]

This is what I am wanting. I would like an email to go out to the email address in K2 column when any information is entered or changed in column J2. Each row is a machine and would need an email each time a row in column J is edited. I would like the email to include C-Model, D-Serial Number, F-Customer Name and J-3 day delivery window date.

This is what I was trying to do when I copied the other code. I really did start learning about VBA code recently. LOL Your recommendations are not easy for me to understand. I really do appreciate any help you could give me in getting the above to work.