Microsoft Excel - A Mail should go automatically by updating the cell

Asked By Rajender Prasad on 10-Feb-12 06:11 AM
Dear All,

In the attached sheet,
Whenver I update the Owner name, a mail should send to a particular person from the mailbox automatically with the comments "A row updated"

Automatically Mail should

Please help.

Web Star replied to Rajender Prasad on 10-Feb-12 06:15 AM
Try this way
This will automatically send an email to any address/addresses whenever the workbook is saved. The only problem I have with it is that if you save it, you get the prompt, and when you close, excel automatically prompts you again. As long as you don't do a redundant save then you won't get a redundant auto email notification :) 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ 
Cancel As Boolean) 

Dim answer As String 

answer = MsgBox("This is where you put the text to prompt the user if he wants to save or not" vbYesNo, "here is the title of that box") 

If answer = vbNo Then Cancel = True 
If answer = vbYes Then 
'open outlook type stuff 
Set OutlookApp = CreateObject("Outlook.Application") 
Set OlObjects = OutlookApp.GetNamespace("MAPI") 
Set newmsg = OutlookApp.CreateItem(olMailItem) 
'add recipients 
'newmsg.Recipients.Add ("Name Here") 
newmsg.Recipients.Add ("") 
'add subject 
newmsg.Subject = "Subject line of auto email here" 
'add body 
newmsg.Body = "body of auto email here" 
newmsg.Display 'display 
newmsg.Send 'send message 
'give conformation of sent message 
MsgBox "insert confirmation box test here", , "title of confirmation box" 

End If