Consulting

Results 1 to 3 of 3

Thread: Compile Error VBA Code to notify through email upon workbook gets updated

  1. #1
    VBAX Regular
    Joined
    Jan 2018
    Posts
    32
    Location

    Compile Error VBA Code to notify through email upon workbook gets updated

    Hi Friends,

    I was looking for the VBA Code when workbook gets saved and closed i will get the notification that it has updated, googled and found the below code.
    pasted that in This Workbook however getting a Compile Error Expected :list separator or) on the 3rd line

    "answer = MsgBox("Are You Sure You want to Save" vbYesNo,"Updated")"

    Here is the code

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
    Cancel As Boolean)
    Dim answer As String
    answer = MsgBox("Are You Sure You want to Save" vbYesNo,"Updated")
    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 Here")
    newmsg.Recipients.Add ("sachin.gupta2209@live.com")
    'add subject
    newmsg.Subject = "Subject line of auto email here"
    'add body
    newmsg.Body = "body of auto email 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
    Kindly help i don't know how to write the codes
    Last edited by Aussiebear; 12-15-2018 at 03:58 PM. Reason: Added tags to code

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    You forgot the comma after the prompt string.
    Dim answer, OutlookApp As Object, OlObjects As Object, newmsg As ObjectDim olMailItem As Integer
    
    
    olMailItem = 0
    
    
    answer = MsgBox("Are You Sure You want to Save", vbYesNo, "Updated")

  3. #3
    VBAX Regular
    Joined
    Jan 2018
    Posts
    32
    Location
    Thank You Kenneth , Worked ☺

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •