Hi,
I am creating a Leave Tracker for my Team. The code below allows everyone to update the excel sheet and sends an email to our team email automatically in outlook however i want the code to also say in the email (MAYBE in the MSG BODY) which cell was updated and what's the content of that cell e.g. "Joey VL" for joey Vacation Leave or Joey SL for sick leave so everyone in the team know who is on leave on that particular day. Can you help me with this please? Below is my code, I am lost as to which modification i need to do to get the results i want.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _Cancel As Boolean) Dim answer As String answer = MsgBox("Do you want to send an update to the team?", vbYesNo, "Save the file") If answer = vbNo Then Cancel = TrueIf answer = vbYes Then'open outlook type stuffSet OutlookApp = CreateObject("Outlook.Application")Set OlObjects = OutlookApp.GetNamespace("MAPI")Set newmsg = OutlookApp.CreateItem(olMailItem)'add recipients'newmsg.Recipients.Add ("ETI TEAM")newmsg.Recipients.Add ("eti.team@eti.com")'add subjectnewmsg.Subject = "2015 Leave Tracker"'add bodynewmsg.Body = "Leave tracker was updated"newmsg.Display 'displaynewmsg.Send 'send message'give conformation of sent messageMsgBox "Email will be sent to Markets ETI Team", "Email Sent" End If 'save the document'Me.Worksheets.Save End Sub</pre>
I am creating a Leave Tracker for my Team. The code below allows everyone to update the excel sheet and sends an email to our team email automatically in outlook however i want the code to also say in the email (MAYBE in the MSG BODY) which cell was updated and what's the content of that cell e.g. "Joey VL" for joey Vacation Leave or Joey SL for sick leave so everyone in the team know who is on leave on that particular day. Can you help me with this please? Below is my code, I am lost as to which modification i need to do to get the results i want.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _Cancel As Boolean) Dim answer As String answer = MsgBox("Do you want to send an update to the team?", vbYesNo, "Save the file") If answer = vbNo Then Cancel = TrueIf answer = vbYes Then'open outlook type stuffSet OutlookApp = CreateObject("Outlook.Application")Set OlObjects = OutlookApp.GetNamespace("MAPI")Set newmsg = OutlookApp.CreateItem(olMailItem)'add recipients'newmsg.Recipients.Add ("ETI TEAM")newmsg.Recipients.Add ("eti.team@eti.com")'add subjectnewmsg.Subject = "2015 Leave Tracker"'add bodynewmsg.Body = "Leave tracker was updated"newmsg.Display 'displaynewmsg.Send 'send message'give conformation of sent messageMsgBox "Email will be sent to Markets ETI Team", "Email Sent" End If 'save the document'Me.Worksheets.Save End Sub</pre>
