To send auto email notification upon confirmation in MS Excel 2010

Try this sub first. If it works, put it in the Save event.

Sub test()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("name1@mail.com", "name1@mail.com")
 
  Set noSession = CreateObject("Notes.NotesSession")
  Set noDatabase = noSession.GETDATABASE("", "")

  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
  Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = vaRecipients
    .Subject = stSubject
    .Body = vaMsg
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

I'd apply some changes:
- use a Lotus.NotesSession instead of Notes.NotesSession
- why SaveMessageOnSend= True ?

@Kimputer, nothing happens.
So i follow sijef's comments to change to Lotus.NotesSession but also nothing happens.
note that I have changed the first part of the name1@mail.com

("name1@mail.com", "name1@mail.com")

to who i want to send. but still nothing happens.


@Sijef, could you assist ?
Thanks!

just to clarify, when user click on 'Save',

1. There should be a dialog box stating:
 "You have saved the document. A email notification will be sent to xxxx and yyyy to notify them on your changes."
xxxx and yyyyy are 2 different email addresses.

for ("name1@mail.com", "name1@mail.com")

I presume it is used for 2 person.
However, in my above testing, I only test for 1 person first, ie. I only changed the first part of " ... " the second part , I leave it the same - "name1@mail.com"

("name1@mail.com", "name1@mail.com")



2. It is only when this unique user "AAA" who save the document then the above action will be evoked. if anyone else save the document, then this action will not happen. it will just work as per normal.

The code above is a subroutine that should be called. So you have to put a Call test in your Save event or you have to remove the Sub test and put the rest of the code directly into the Save event.

The best IMHO would be to rename the Sub test into Sub NotifyMe and add a Call NotifyMe to your Save event.

pls advise. thanks

The code above is a subroutine that should be called. So you have to put a Call test in your Save event or you have to remove the Sub test and put the rest of the code directly into the Save event.

The best IMHO would be to rename the Sub test into Sub NotifyMe and add a Call NotifyMe to your Save event.

How to put in save event? I search Help in MS Excel and there is only Application.WorkbookBeforeSave Event and before close event.

If you don't like before_save, in Excel 2010, the new after save was introduced

http://msdn.microsoft.com/en-us/library/office/ff836466(v=office.14).aspx

Since your tag mentions 2010, this should work for you.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 Sub NotifyMe()
End If
End Sub


Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("name@mail.com", "name@mail.com")
 
  Set noSession = CreateObject("Lotus.NotesSession")
  Set noDatabase = noSession.GETDATABASE("", "")

  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
  Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = vaRecipients
    .Subject = stSubject
    .Body = vaMsg
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

The above doesn't work.

May i know what is the full coding ?

Where are the variables with the values that you intend to put in the mail, i.e. where are these 3 variables:
    .SendTo = vaRecipients
    .Subject = stSubject
    .Body = vaMsg

When the code is executed, and you exited your Notes client, is it started and do you have to log in?
Did you try to run your code with the debugger?

There is no need to exit the notes client.
notes is started so no need to log in.

.SendTo = vaRecipients
    .Subject = stSubject
    .Body = vaMsg

will be :

   .SendTo = "name1", "name2"
    .Subject = "Table in Document updated by 'name' "
    .Body = "This is to inform you that the table in document has been updated today."

Again, did you try to run your code with the debugger?

Yes, there are still errors :


This is my code:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 Sub NotifyMe()
End If
End Sub


Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("name@mail.com", "name@mail.com")
 
  Set noSession = CreateObject("Lotus.NotesSession")
  Set noDatabase = noSession.GETDATABASE("", "")

  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
  Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = "name1""name2"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

Line #3: remove the word "Sub"...

After remove Line 3 "sub", nothing was executed after i saved doc.
So i press alt +F8.
Error:

Then i clicked on Debug and error :

i have increased points to 400.

I was away, on vacation.

After creating a NotesSession object, you need to initialize it. See http://www-12.lotus.com/ldd/doc/domino_notes/Rnext/help6_designer.nsf/b3266a3c17f9bb7085256b870069c0a9/11945a2865ecab0985256c54004d4685?OpenDocument

Still the same., i received the error :

Compile Error:
Ambiguous name detected: NotifyMe

My code is

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 NotifyMe()
End If
End Sub

Private Sub NotifyMe()
Dim s As New NotesSession
Call s.Initialize
MsgBox s.CommonUserName, , "Common user name"
End Sub



Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("name@mail.com", "name@mail.com")
 
  Set noSession = CreateObject("Lotus.NotesSession")
  Set noDatabase = noSession.GETDATABASE("realname@realdomain.com", "realname@realdomain.com")

  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
  Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = "name1""name2"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

in my code

Private Sub NotifyMe()
Dim s As New NotesSession
Call s.Initialize
MsgBox s.CommonUserName, , "Common user name"
End Sub

Select allOpen in new window

I did put a name in "Common user name" but the error message still occur.

Well... There are indeed two subs NotifyMe in your code, so it can't be compiled.

Remove lines 7-11, and add one line to your code:

Set noSession = CreateObject...
Call noSession.Initialize

done but nothing happen when i clicked on save.

and when i press alt f8, there is still error.

error:

Run-time error '-2147217013 (8004118b)':
Session has not been initialized

when clicked Debug, the following line is highlighted:

Set noDatabase = noSession.GETDATABASE("realname@realdomain.com", "realname@realdomain.com")

my code:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 NotifyMe()
End If
End Sub

Set noSession = CreateObject
Call noSession.Initialize


Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("name@mail.com", "name@mail.com")
 
  Set noSession = CreateObject("Lotus.NotesSession")
 Set noDatabase = noSession.GETDATABASE ("realname@realdomain.com", "realname@realdomain.com") 
  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
  Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = "name1""name2"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

Goodness... I don't mean to be impolite, but coding isn't one of your stronger points, now is it?

Please refer to the code you got from Kimputer (https://www.experts-exchange.com/questions/28469467/To-send-auto-email-notification-upon-confirmation-in-MS-Excel-2010.html?anchorAnswerId=40176381#a40176381). All it needs, basically, is an extra line with Call noSession.Initialize, after the first CreateObject. See the suggestions above.

Anyway, this is not a rent-a-coder site, meaning: you do the work, we advise. So sorry...

no it is not my strong point for sure.

I have already followed Kimputer's code and also noted the extra line Call noSession.Initialize, after the first CreateObject.

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 NotifyMe()
End If
End Sub

Set noSession = CreateObject
Call noSession.Initialize


Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("mail@domain.com", "mail@domain.com")
 
  Set noSession = CreateObject("Lotus.NotesSession")
 Set noDatabase = noSession.GETDATABASE("mail@domain.com", "mail@domain.com")
  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
  Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = "name1""name2"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

but there is still errror at :
Set noDatabase = noSession.GETDATABASE("mail@domain.com", "mail@domain.com")

of course i did change the mail@domain.com to the real email address of the intended recipients.

Please remove those two lines, I mean lines 7 and 8 above. I assume they are never executed, so they are useless.

But, you do need to call Initialize, so line  8 should be moved inside the NotifyMe subroutine, after the first CreateObject in it.

Line 30 should have this format: GetDatabase("yourserver", "yourdatabase");
Or with empty parameters if it needs to be opened later as in ("","") as my first code.
Did you ever test it with the real server name and databse name?

Line 30 should have this format: GetDatabase("yourserver", "yourdatabase");
Or with empty parameters if it needs to be opened later as in ("","") as my first code.
Did you ever test it with the real server name and databse name?

no, i leave it as "", ""

for server name, i locate by going to Edit location > Servers tab
In the servers tab, there are several fields:

Home/Mail server:
Passthru server:
Catalog/domain search server:
Domino directory server:

I choose the first one "Home/Mail Server".


how do i know the database name ?

While this could be relevant, I'd like to solve the error first. What's the status of that problem, does execution still stop at the same line? And can you post your current code?

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 NotifyMe()
End If
End Sub

'Set noSession = CreateObject
'Call noSession.Initialize


Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("realname1@realdomain.com", "realname2@realdomain.com")
 
  Set noSession = CreateObject("Lotus.NotesSession")
  Set noDatabase = noSession.GETDATABASE("", "")
 

  If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
 
  Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = "name1""name2"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

Encounter run time Error at this line:
Set noDatabase = noSession.GETDATABASE("", "")


Error message:
Run-time error '-2147217013 (8004118b)':
Session has not been initialized


So I am trying to find out how to get the database name to test if I still have the error.

Add one line, in between lines 29 and 30:

Call noSession.Initialize

and then check again.

After adding the one line, in between lines 29 and 30:

Call noSession.Initialize

The following dialog box pop up:

"A password is required to access ID file
AccessID\AppData\Local\Lotus\Notes\Data\(cannot be see anymore, trucated)

Enter the password (case sensitive):
_________

OK | Cancel"

After entering password, there is an error message
"Run-time error '-2147217415 (80040ff9)':
A database name must be provided"

Debugging line at :
Set noDatabase = noSession.GETDATABASE("", "")

So back to my last question, how do i identify the database name ?

Ah, progress... :-)

You shouldn't have to identify the database, that's what the OpenMail is supposed to do. The preferred code would be:

Set noDatabase= noSession.GetDatabase("","")
Call noDatabase.OpenMail

The test for IsOpen is pointless, it can be removed. OpenMail will try to find the current user's mail database and open it.

1) If i use:
Set noDatabase= noSession.GetDatabase("","")
Call noDatabase.OpenMail


Then they still ask for database name.

Code is the same as number 2), just that
Set noDatabase= noSession.GetDatabase("","")

is used.


2) If I use:
 Call noDatabase.OpenMail
my code:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 NotifyMe()
End If
End Sub

'Set noSession = CreateObject
'Call noSession.Initialize


Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("realname@domain.com", "realname@domain.com")
 
  Set noSession = CreateObject("Lotus.NotesSession")
  Call noSession.Initialize

  Call noDatabase.OpenMail ' Added in on 23 Aug

  
  Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = "name1""name2"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

password prompt and after keying the password, i get an error.

Error :

Run time error '91':
Object variable or with block variable not set

When Debug, the line is at

Call noDatabase.OpenMail

#2 is what's to be expected: at the Call, the noDatabase variable is still empty, it doesn't hold an object, so what is it supposed to do with the OpenMail method? Let's forget #2.

As for #1: IMHO the code should be correct, because the NotesDatabase object is created (and left empty, which is ok) and then initialized. Your development and/or test system, does it have a Notes client installed? The Notes client is properly configured, i.e. you can start it, log in and open the mail database? For that's what the OpenMail method does: it fetches the mail server and database names from the local settings, for the current Notes user, and opens the database. I assume that, if there is no current user, execution will stop with the error message you got.

ok forget #2.

for #1, yes, the pc already has notes client installed, and that notes is already opened in the PC.

The following is the lotus notes version:

IBM Lotus Notes 8.5

Release 8.5.3FP6
Revision 20131126.1400-FP6 (Release 8.5.3FP6)
Standard Configuration

Can you check File/Security/User Security, open the ID and make sure that the option Don't prompt for a password from other Notes-based programs is ticked? It's on the Security Basics tab.

Ah, OpenMail isn't the correct one... Here's an example from the Designer Help database:

Private Sub OpenMail_Click()
Dim s As New NotesSession
s.Initialize
Dim dir As NotesDbDirectory
Dim db As NotesDatabase
Set dir = s.GetDbDirectory("")
Set db = dir.OpenMailDatabase
MsgBox db.Title & " on " & db.Server, , db.FilePath
End Sub

Select allOpen in new window

So you need to declare one more Object, the dir. Modified code:

  Set noSession = CreateObject("Lotus.NotesSession")
  Call noSession.Initialize
  Dim noDir As Object
  Set noDir= noSession.GetDbDirectory("")
  Set noDir.OpenMailDatabase ' Added in on 28 Aug

Select allOpen in new window

There is a comile error on

Set noDir.OpenMailDatabase

"Expected:="

which means the code must be something

Set noDir.OpenMailDatabase = ?????

Oh, sorry, a typo ( but you could have looked at the example, line 7!).

Set db = noDir.OpenMailDatabase

ok noted thks

There is still debugging error:

Run-time error '91':
Object variable or with block variable not set

error highlight at
.Form = "Memo"

My code:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 NotifyMe()
End If
End Sub
Private Sub OpenMail_Click()
Dim s As New NotesSession
s.Initialize
Dim dir As NotesDbDirectory
Dim db As NotesDatabase
Set dir = s.GetDbDirectory("")
Set db = dir.OpenMailDatabase
MsgBox db.Title & " on " & db.Server, , db.FilePath
End Sub

Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("realname@domain.com", "realname@domain.com")
 
   
 Set noSession = CreateObject("Lotus.NotesSession")
  Call noSession.Initialize
  Dim noDir As Object
 Set noDir = noSession.GetDbDirectory("")
 Set db = noDir.OpenMailDatabase 'added 28 Aug 14


  With noDocument
    .Form = "Memo"
    .SendTo = "name1""name2"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

You removed a line, see https://www.experts-exchange.com/questions/28469467/To-send-auto-email-notification-upon-confirmation-in-MS-Excel-2010.html?anchorAnswerId=40269615#a40269615 line 35...

error

Compile error:
User-defined type not defined

error highlight at :
Private Sub OpenMail_Click()

My code:

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
 NotifyMe()
End If
End Sub
Private Sub OpenMail_Click()
Dim s As New NotesSession
s.Initialize
Dim dir As NotesDbDirectory
Dim db As NotesDatabase
Set dir = s.GetDbDirectory("")
Set db = dir.OpenMailDatabase
MsgBox db.Title & " on " & db.Server, , db.FilePath
End Sub

Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("realname@domain.com", "realname@domain.com")
   
 
 Set noSession = CreateObject("Lotus.NotesSession")
  Call noSession.Initialize
  Dim noDir As Object
 Set noDir = noSession.GetDbDirectory("")
 Set db = noDir.OpenMailDatabase 'added 28 Aug 14

Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = "name1""name2"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub

Select allOpen in new window

Did you include the Domino olb libraries in your project? If not, replace the types in all declarations that NotesSession, NotesDocument etc. by Object, just like the declarations in NotifyMe with Object.

What do you mean by  Domino Type libraries?

I have already included the following to open the mail database as advised earlier

Set noSession = CreateObject("Lotus.NotesSession")
  Call noSession.Initialize
  Dim noDir As Object
 Set noDir = noSession.GetDbDirectory("")
 Set db = noDir.OpenMailDatabase 'added 28 Aug 14

These Type libraries allow you to use the NotesSession and other NotesXxx types instead of Object. The advantage is that your editor knows these types, and their properties and methods. The editor can then help you write your code faster.

Code works?

Sorry i am not familiar with Domino Type libraries.
Is it possible for you  to show examples and then i change the data accordingly?
thanks.

It is not specific to Domino at all. Please read http://support.microsoft.com/kb/140629 and then http://www.ibm.com/developerworks/lotus/library/domino-msnet/

Ok done.


but still there is error :

Run time error 91:
object variable or with block variable not set.
Lotus-Notes-Domino-Objects.JPG

Sorry, was away for a week.

The run-time error: where does it occur, at which line?

Error :

Run time error '91:
Object variable or With block variable not set

Line highlighted at :
Set noDocument = noDatabase.CreateDocument

My code :

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then '26 Sept 2014
 NotifyMe()
End If
End Sub
Private Sub OpenMail_Click()
Dim s As New NotesSession
s.Initialize
Dim dir As NotesDbDirectory
Dim db As NotesDatabase
Set dir = s.GetDbDirectory("")
Set db = dir.OpenMailDatabase
MsgBox db.Title & " on " & db.Server, , db.FilePath
End Sub

Sub NotifyMe()

Const stSubject As String = "Clicked Save"

Const vaMsg As Variant = "Clicked Save"

  Dim stFileName As String
  Dim vaRecipients As Variant
 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
 
  vaRecipients = VBA.Array("realname@domain.com", "realname@domain.com")
   
 
 Set noSession = CreateObject("Lotus.NotesSession")
  Call noSession.Initialize
  Dim noDir As Object
 Set noDir = noSession.GetDbDirectory("")
 Set db = noDir.OpenMailDatabase 'added 28 Aug 14

Set noDocument = noDatabase.CreateDocument

  With noDocument
    .Form = "Memo"
    .SendTo = "realname@domain.com""realname@domain.com"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
 

  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
 
End Sub
                      

Select allOpen in new window

Change line 39 into

Set noDatabase = noDir.OpenMailDatabase

There is still error:

Run-time error '438':
Object doesn't support this property or method

End | Debug

When debug, error is highlighted at :

.Form = "Memo"

Could you replace the line with
      .Form = "Memo"
by this line:
      Call .ReplaceItemValue("Form", "Memo")
and see if it still stops on the same line?

If execution continues to the next line, all direct assignment lines have to be replaced in a similar fashion.

No there is error on the next line and when debug, the error is

Run-time error '438':
Object doesn't support this property or method

End | Debug


How to i replace from line 45 and onwards ?

Ok, sorry for my late reply.

So it's this line:

     .SendTo = "realname@domain.com""realname@domain.com"

Change into
     .SendTo = "realname@domain.com"

Run time error  '438'
object doesn't support this property or method

and the line is highlighted at

 .SendTo = "realname@domain.com"

Could you replace the line with
      .SendTo = "realname@domain.com"
by this line:
      Call .ReplaceItemValue("SendTo", "realname@domain.com")
and see if it still stops on the same line?

If execution continues to the next line, all direct assignment lines have to be replaced in a similar fashion.

It doesn't stop at next line but the previous line.
 .Form = "Memo"

error also after i change to
 Call .ReplaceItemValue("Memo")

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

I was on holiday.
Please reopen the thread.
The solution doesn't work and if administrator insist on closing, then only 250 points should be applied and not full points.

Yes i used:
Call .ReplaceItemValue("Form", "Memo")

followed by:
Call .ReplaceItemValue ("name1""name2")  and error occurs.

After more than 6 months, I get the impression you don't have a clue about (Notes) programming... And IMHO I deserve double points for hanging in here for so long. :-)

By the way, I don't have many clues either, about what we are doing and where this is going to: I get fragmented information, some errors, and that's all. If you want me to help you, I absolutely need you to help me. Otherwise, it will take us another 6 months to get it solved.

This statement:
isn't valid. The correct syntax is:
where fieldName is a String (value) and newValue can be a String or number or a list.

After more than 6 months, I get the impression you don't have a clue about (Notes) programming... And IMHO I deserve double points for hanging in here for so long. :-)

= Yes i already told you in the beginning that i know nothing on notes programming.
Ok.


By the way, I don't have many clues either, about what we are doing and where this is going to
= objective:
Lotus notes sent an automated email reply to users once one user has saved the file in the common directory.

Call .ReplaceItemValue (fieldName, newValue)
= I use

Call .ReplaceItemValue (myfilename280115, 280115)

and error:

Run time error '-2147217351(80041039)':
You must provide an item value


myfilename280115 denotes my file name
280115 denotes  the date that the file was updated

Call .ReplaceItemValue (myfilename280115, 280115)

Here the first parameter looks like a variable, the second is a numeric value. I suppose this isn't exactly what you need.

Call .ReplaceItemValue ("myfilename280115", "280115")
With double-quotes they are strings. Using this statement will create a field with the name "myfilename280115" in the mail document, and it will contain the value "280115" as a string. What exactly is it that you want?

The correct syntax is:
Call .ReplaceItemValue (fieldName, newValue)

where fieldName is a String (value) and newValue can be a String or number or a list.

= i don't really know how to use this syntax so since is string anda String or number or a list respectively, i use myfilename280115, 280115 to make it more meaningful.

I will use Call .ReplaceItemValue ("myfilename280115", "280115") then.

as the original code has a subject :

With noDocument
    .Form = "Memo"
    .SendTo = "realname@domain.com""realname@domain.com"
    .Subject = "Table in Document updated by 'name'"
    .Body = "This is to inform you that the table in document has been updated today."
    .SaveMessageOnSend = True
    .PostedDate = Now()

Select allOpen in new window

so i thought myfilename280115, 280115 will be stated in the subject of the email "myfilename280115, 280115"