Auto notify or mail when excel sheet is updated. [Closed]

Posts
4
Registration date
Friday September 20, 2013
Status
Member
Last seen
September 20, 2013
- - Latest reply: ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
- Sep 23, 2013 at 09:28 AM
Hello,

I found this code on the site, however I am trying to get it to launch Lotus Notes instead of Outlook. Any help would be appreciated.


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 ("Name Here")
newmsg.Recipients.Add ("sampleemailaddresshere@email.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
See more 

1 reply

Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1539
0
Thank you
Have Fun!

THis is what I found out there about lotus notes API

Set objSession = CreateObject("Notes.NotesSession")
Set objMailDB = objSession.GetDatabase("", "")

'Open database if it's not ready
If Not objMailDB.IsOpen Then
objMailDB.OpenMail
End If

Set objMailDoc = objMailDB.CreateDocument

I hope this helps, and I better not be helping any spambot!
darkxerxes
Posts
4
Registration date
Friday September 20, 2013
Status
Member
Last seen
September 20, 2013
-
Lol! Thanks.. not at all.. they're trying to work a maintenance log that sends an auto email notification when the sheet is saved.. the code will work, then I just needed to figure out the Notes part.. .cause we are in the dark ages for email lol
darkxerxes
Posts
4
Registration date
Friday September 20, 2013
Status
Member
Last seen
September 20, 2013
-
Ok.. So I'm not great at working through VBscript... let me check and see if I got this correct..

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



Replace this section right here
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)

With this section right here

Set objSession = CreateObject("Notes.NotesSession")
Set objMailDB = objSession.GetDatabase("", "")

'Open database if it's not ready
If Not objMailDB.IsOpen Then
objMailDB.OpenMail
End If

Set objMailDoc = objMailDB.CreateDocument


'add recipients
'newmsg.Recipients.Add ("Name Here")
newmsg.Recipients.Add ("sampleemailaddresshere@email.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


When I do this the line "newmsg.recipients.add excel gives a debug code saying object expected"

What am I doing wrong..

Thanks
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1539 -
Your newmsg variable still points to an outlook item. Point it t the new varialbe of
objMailDB.

Try that!
darkxerxes
Posts
4
Registration date
Friday September 20, 2013
Status
Member
Last seen
September 20, 2013
-
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 objSession = CreateObject("Notes.NotesSession")
Set objMailDB = objSession.GetDatabase("", "")

'Open database if it's not ready
If Not objMailDB.IsOpen Then
objMailDB.OpenMail
End If

Set objMailDoc = objMailDB.CreateDocument
'add recipients
'objMailDB.Recipients.Add ("Name Here")
objMailDB.Recipients.Add ("sampleemailaddresshere@email.com")
'add subject
objMailDB.Subject = "Subject line of auto email here"
'add body
objMailDB.Body = "body of auto email here"
objMailDB.Display 'display
objMailDB.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

Its still not working (although I think I misunderstood)

objMailDB.Recipients.Add ("sampleemailaddresshere@email.com") is Yellow on Debug Error 438

object doesn't support this property or method
ac3mark
Posts
13034
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
October 11, 2019
1539 -
Well, I am sorry to end my help here, but I do not have a NOTES license to test the code. What you are tryng to do is hook into an API, look for help with the NOTES API.

Sorry, but I really just wanted to head you in the right direction.

Good Luck.