• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 31
  • Last Modified:

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
0
steven
Asked:
steven
  • 2
1 Solution
 
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
 
NorieVBA ExpertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now