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

Report
Posts
4
Registration date
Friday September 20, 2013
Status
Member
Last seen
September 20, 2013
-
ac3mark
Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
-
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

1 reply

Posts
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,686
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
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,686
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
13029
Registration date
Monday June 3, 2013
Status
Member
Last seen
October 11, 2019
1,686
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.