Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Send email when a cell changed in excel [Closed]

Report
Posts
2
Registration date
Monday April 25, 2011
Status
Member
Last seen
April 26, 2011
 -
 Haval -
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


See more
Related:

3 replies

Answer 1 / 3
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
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. 

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
7
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

Hi,

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
Answer 2 / 3
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
 120
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
Answer 3 / 3
Posts
2
Registration date
Monday April 25, 2011
Status
Member
Last seen
April 26, 2011
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!