Send Email Using MACRO only when the cell is updated

[Nico Learning]

Hi All,

Can someone please help me with this code i have a code that works perfectly, it sends the email fine, however i cannot work out how to modify it to send the email only if the cell is updated please see code below, it would be perfect if i could get it to send in when the spread sheet is closed But only if row "B" is updated

The current code works when the spread sheet is closed but i only want it to send when its been updated, please see below.

Private Sub auto_close()
sendemail
End Sub

Public Function sendemail()
On Error GoTo ende
esubject = "New Coaching Required"
sendto = "nicholas.walker@npower.com"
ccto = ""
ebody = "Please see update on the coaching template" 

Set app = CreateObject("Outlook.Application")
Set itm = app.createitem(0)

With itm
.Subject = esubject
.to = sendto
.cc = ccto
.body = ebody
.send


Set app = Nothing
Set itm = Nothing


End With



ende:

End Function

Thank you

 

[Akashwani]

Hi Nico,

take a look here....

http://www.mrexcel.com/forum/showthread.php?t=408130

It may solve your problem or at least guide you in the right direction.

Ak

 

[Nico Learning]

Hi Akashwani,

Thank you for that reply, however it didnt lead me to exactly to what i want to do,

example if "D2" was the last cell that was update, then if someone goes into the ss and updte "D3" then when the ss closed then the email will be sent but if nothing change then then i dont want the macro to run, in other words the macro should alway be checking for new entry in the row below before sending the email

Cheers
Nico

 

[Akashwani]

Hi Nico,

Look here....

http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change

Have you done a search of MrExcel?

Sorry that is the best I can do.

Ak

 

[Nico Learning]

Thank you Akashwani

regards
Nico

 

[Akashwani]

Hi Nico,

I'm not sure if this is of any use to you,
Put this in sheet1 or which ever sheet you like...

Option Explicit
'''This will send an email IF any cell changes in Row2
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 Then 
'''This is my procedure for sending Email, change it to suit yours
 ActiveWorkbook.Sheets(1).Copy '''Change sheet number to suit
With ActiveWorkbook
.SendMail Recipients:="[EMAIL="akashwani@coldpost.com"]akashwani@coldpost.com[/EMAIL]", _
Subject:="New coaching required" & Format(Date, " dddd d mmmm yyyy")
End With
Application.DisplayAlerts = False
End If
End Sub

Hopefully someone with far greater knowledge will jump in and put you straight on what you want.

Good luck.

Ak

 

[Nico Learning]

A Good friend of mine come up with what i think the solution is, please see below

Public haschanged As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
haschanged = True
End Sub

Private Sub CommandButton1_click()
If haschanged = True Then
   'Actions if worksheet has changed here
   sendemail
Else
   'Actions if no change has occurred here
End If
End Sub

Public Function sendemail()
On Error GoTo ende
esubject = "New Coaching Required"
sendto = "nico2003_ks@hotmail.com"
ccto = ""
ebody = "Please see update on the coaching template" & vbCrLf & "" & vbCrLf & "Please send a calendar invite to the user with an appropriate Date" & vbCrLf & "" & vbCrLf & "Regards"


Set app = CreateObject("Outlook.Application")
Set itm = app.createitem(0)

With itm
.Subject = esubject
.to = sendto
.cc = ccto
.body = ebody
.send


Set app = Nothing
Set itm = Nothing


End With



ende:

End Function

Hopefully this will help anyone that has something similar

Thanks