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
Hello,
hi guys, straight off, im stupid, extremely stupid so if you can help me please keep it simple as im barely technical at all :)
anyways, im a comic book retailer who sells a tonne of items on a preorder basis.i have an excel sheet showing all items preordered for customers and their details. this form shows the release date of each item. what I need is excel to automatically send an email to each customer when I change the release date ( due to delays etc). the email needs to contain the item title and the new date. I currently have outlook also and I know that this is possible but I dont know how to do it :(
any help is appreciated, theres a pint in it for ya :)
i dont know if I can display an example of the excel form on here, it may help :s
hi guys, straight off, im stupid, extremely stupid so if you can help me please keep it simple as im barely technical at all :)
anyways, im a comic book retailer who sells a tonne of items on a preorder basis.i have an excel sheet showing all items preordered for customers and their details. this form shows the release date of each item. what I need is excel to automatically send an email to each customer when I change the release date ( due to delays etc). the email needs to contain the item title and the new date. I currently have outlook also and I know that this is possible but I dont know how to do it :(
any help is appreciated, theres a pint in it for ya :)
i dont know if I can display an example of the excel form on here, it may help :s
Related:
- Email notification if cell is changed
- What is light in whatsapp notification - Guide
- Network notification - Guide
- No email no password - Guide
- How to change email in free fire - Guide
- Instagram share list order changed - Instagram Forum
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