Excel On save send email

We are trying to send an email when a excel file is saved we are currently using 2010 cant seem to get it work.  We have never created a maco, and it doesn't seem to see the below exists as a macro.  Below is the code that we are trying attempting to use:


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 ("JIMMY John")
newmsg.Recipients.Add ("jimmy@hotmail.com")
'add subject
newmsg.Subject = "test Save"
'add body
newmsg.Body = "This is a test from excel"
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
stevendeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls correct
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")

Open in new window

to
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")

Open in new window

Regards
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
Where have you put the code?

It should go in the ThisWorkbook module of the workbook you want this to happen in.
0
stevendeveloperAuthor Commented:
we did a alt + f11 then right clicked on ThisWorkbook which opened the This workbook Code window,  the code is pasted there but it always asks for save and doesn't seem to see the code.
0
stevendeveloperAuthor Commented:
This worked, I didn't realize it.  THANKS SO MUCH
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.