Email notification once the excel spreadsheet modified.

Asked By 0 points N/A Posted on -
qa-featured

We do not have any task tracking system in place. We usually maintained it through excel and then send it to associated users by email.  We want have this process automated and spreadsheet should itself send an email (internet email ids like gmail, yahoo)  whenever it gets modified. Can anyone help ?

SHARE
Answered By 0 points N/A #89960

Email notification once the excel spreadsheet modified.

qa-featured

just add this macro > Right-click the Worksheet's Name Tab and look for 'View Code' > On the VB window that'll open put this codes>

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

Dim answer As String

answer = MsgBox("Put the message for the prompt when it'll ask you to save" vbYesNo, "title of the prompt or 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")
newmsg.Recipients.Add ("[email protected]")
'add subject
newmsg.Subject = "place the subject for your email here"
'add body
newmsg.Body = "contents of your automail 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

'save the document
'Me.Worksheets.Save

End Sub

try it. I hope it helps.

 

Login/Register to Answer

Related Questions