Matthew has attended:
Excel VBA Intro Intermediate course
Not running code for certain users
I have created a tracker sheet for other departments to use that email myself & my manager when a change is made.
This is the code below that I am using
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim answer As String
answer = MsgBox("Do you wish to save changes?", vbYesNo + vbQuestion, "Save")
If answer = vbNo Then Cancel = True
If answer = vbYes Then
'open outlook
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
newmsg.Recipients.Add ("Email Address")
'add subject
newmsg.Subject = "Appraisal Tracker"
'Add body
newmsg.body = "Changes have been made to the appraisal tracker"
newmsg.Display 'display
newmsg.Send 'send message
End If
End Sub
Is there way to stop the code from running when I make a change to the document?
Thank you in advance
Matt
RE: Not running code for certain users
Hi Matthew,
Thank you for the forum question.
You can do it by using one more IF decision code. The Application.UserName function will test the user name'
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.UserName <> "Jens Bonde" And Application.UserName <> "Matthew" Then
answer = MsgBox("Do you wish to save changes?", vbYesNo + vbQuestion, "Save")
If answer = vbNo Then
Cancel = True
end if
If answer = vbYes Then
'open outlook
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
newmsg.Recipients.Add ("Email Address")
'add subject
newmsg.Subject = "Appraisal Tracker"
'Add body
newmsg.body = "Changes have been made to the appraisal tracker"
newmsg.Display 'display
newmsg.Send 'send message
End If
End If
End Sub
I hope this make sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
