Send email when a cell changed in excel
Closed
                    
        
                    disposablehero
    
        
                    Posts
            
                
            2
                
                            Registration date
            Monday April 25, 2011
                            Status
            Member
                            Last seen
            April 26, 2011
            
                -
                            Apr 25, 2011 at 08:21 PM
                        
Haval - Jul 14, 2016 at 06:29 AM
        Haval - Jul 14, 2016 at 06:29 AM
        Related:         
- Email notification if cell is changed
- What is combine notification in viber - Guide
- How to change free fire email id - Guide
- What is light in whatsapp notification settings - Guide
- Facebook account hacked email and phone changed ✓ - Facebook Forum
- Turn off wifi networks available notification android - Guide
3 responses
                
        
                    RWomanizer
    
        
                    Posts
            
                
            365
                
                            Registration date
            Monday February  7, 2011
                            Status
            Contributor
                            Last seen
            September 30, 2013
            
            
                    120
    
    
                    
Apr 27, 2011 at 01:24 AM
    Apr 27, 2011 at 01:24 AM
                        
                            
                    Hi disposablehero,
Note: First make copy of your sheet, because you are not able to undo after executing the code.
Please make some changes/check the following in your sheet as:
1) Name should be in column A,
2) Item in Column H
3) E-Mail in Column K
4) Date in Column L, and
5) Insert a new column M with header new release date, and fill it if any release date is changed.
Now paste the code in by pressing Alt+F11.
Run the Macro by Alt+F8. This macro send a mail if release date is changed and replace the new release date with date and the column new release date will be blank again.
Change the Subject and Body of the massage according to you in the code.
            Note: First make copy of your sheet, because you are not able to undo after executing the code.
Please make some changes/check the following in your sheet as:
1) Name should be in column A,
2) Item in Column H
3) E-Mail in Column K
4) Date in Column L, and
5) Insert a new column M with header new release date, and fill it if any release date is changed.
Now paste the code in by pressing Alt+F11.
Run the Macro by Alt+F8. This macro send a mail if release date is changed and replace the new release date with date and the column new release date will be blank again.
Change the Subject and Body of the massage according to you in the 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"         ' Change your massage subject here
                'Change body of the massage here
                .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
                
                 
        
    
    
        
    
    
    
Jul 14, 2016 at 06:29 AM
Thank you very much for the useful code. How is it possible to make the code send automatically changed data to emails without pressing Alt+F8?
Best regards