Send email when a cell changed in excel
Closed
disposablehero
Haval - Jul 14, 2016 at 06:29 AM
- Posts
- 2
- Registration date
- Monday April 25, 2011
- Status
- Member
- Last seen
- April 26, 2011
Haval - Jul 14, 2016 at 06:29 AM
Related:
- Send email when excel cell is updated
- Auto notify of email when excel sheet updated - Best answers
- Notification when excel spreadsheet is updated - Best answers
- Auto notify or mail when Excel sheet updated. ✓ - Forum - Excel
- How to send an email automatic when a cell is updated - Forum - Excel
- Send email when excel spreadsheet is updated - Guide
- Auto notify or mail when excel sheet is updated. - Forum - Excel
- Macro to send email from excel with attachment - Guide
3 replies
RWomanizer
Apr 27, 2011 at 01:24 AM
- Posts
- 365
- Registration date
- Monday February 7, 2011
- Status
- Contributor
- Last seen
- September 30, 2013
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
RWomanizer
Apr 26, 2011 at 08:19 AM
- Posts
- 365
- Registration date
- Monday February 7, 2011
- Status
- Contributor
- Last seen
- September 30, 2013
Apr 26, 2011 at 08:19 AM
Hi,
could you paste a sample of your data. and redefine your problem as per sample data so we can help you in more efficient way.
Regards,
Rahul
could you paste a sample of your data. and redefine your problem as per sample data so we can help you in more efficient way.
Regards,
Rahul
disposablehero
Apr 26, 2011 at 11:12 AM
- Posts
- 2
- Registration date
- Monday April 25, 2011
- Status
- Member
- Last seen
- April 26, 2011
Apr 26, 2011 at 11:12 AM
hi rahul
sheet is as below:-
Headers are:- NAME, ADDRESS 1 TO ADDRESS 6, ITEM, QTY, PRICE, EMAIL, DATE
Aaron Rowe 5 Cheviot Place Yetholm Kelso Borders TD58SD United Kingdom WALKING DEAD #84 (MR) IMAGE COMICS (83) 1 $2.10 elvis8beef@hotmail.co.uk 20-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom AGE OF X UNIVERSE #2 (OF 2) MARVEL COMICS (1) 1 $2.55 aaaadamn@yahoo.co.uk 27-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom AMAZING SPIDER-MAN #659 BIG MARVEL COMICS (658) 1 $2.55 aaaadamn@yahoo.co.uk 20-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom NEW MUTANTS #24 AGEX MARVEL COMICS (23) 1 $2.10 aaaadamn@yahoo.co.uk 27-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom PLANET OF THE APES #1 1:10 VARIANT BOOM COMICS () 1 $3.99 aaaadamn@yahoo.co.uk 27-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom PLANET OF THE APES #1 BOOM COMICS () 1 $2.55 aaaadamn@yahoo.co.uk 27-04-11
Adam Donoghue 46,Throopside Avenue Bournemouth Dorset BH9 3NR United Kingdom AMAZING SPIDER-MAN #659 BIG MARVEL COMICS (658) 1 $2.55 sc8goat@live.co.uk 20-04-11
Adam Donoghue 46,Throopside Avenue Bournemouth Dorset BH9 3NR United Kingdom VENOM #2 MARVEL COMICS (1) 1 $2.10 sc8goat@live.co.uk 20-04-11
Basically what I need is say if the release date for 'walking dead #84' was to change to '15-05-11'. I would type in thet new date and excel would notice that the cell had changed and automatically send an email to the customer providing them with the new release date for the item. this could take place at the point of the cell being changed or ideally send all emails in bulk once the excel sheet had been closed and saved
hope that makes sense :s
sheet is as below:-
Headers are:- NAME, ADDRESS 1 TO ADDRESS 6, ITEM, QTY, PRICE, EMAIL, DATE
Aaron Rowe 5 Cheviot Place Yetholm Kelso Borders TD58SD United Kingdom WALKING DEAD #84 (MR) IMAGE COMICS (83) 1 $2.10 elvis8beef@hotmail.co.uk 20-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom AGE OF X UNIVERSE #2 (OF 2) MARVEL COMICS (1) 1 $2.55 aaaadamn@yahoo.co.uk 27-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom AMAZING SPIDER-MAN #659 BIG MARVEL COMICS (658) 1 $2.55 aaaadamn@yahoo.co.uk 20-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom NEW MUTANTS #24 AGEX MARVEL COMICS (23) 1 $2.10 aaaadamn@yahoo.co.uk 27-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom PLANET OF THE APES #1 1:10 VARIANT BOOM COMICS () 1 $3.99 aaaadamn@yahoo.co.uk 27-04-11
Adam Broomfield Unit 12 Churchfields Business Park Clensmore Street Kidderminster Worcestershire DY101JY United Kingdom PLANET OF THE APES #1 BOOM COMICS () 1 $2.55 aaaadamn@yahoo.co.uk 27-04-11
Adam Donoghue 46,Throopside Avenue Bournemouth Dorset BH9 3NR United Kingdom AMAZING SPIDER-MAN #659 BIG MARVEL COMICS (658) 1 $2.55 sc8goat@live.co.uk 20-04-11
Adam Donoghue 46,Throopside Avenue Bournemouth Dorset BH9 3NR United Kingdom VENOM #2 MARVEL COMICS (1) 1 $2.10 sc8goat@live.co.uk 20-04-11
Basically what I need is say if the release date for 'walking dead #84' was to change to '15-05-11'. I would type in thet new date and excel would notice that the cell had changed and automatically send an email to the customer providing them with the new release date for the item. this could take place at the point of the cell being changed or ideally send all emails in bulk once the excel sheet had been closed and saved
hope that makes sense :s
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