We help IT Professionals succeed at work.

VBA SEND STATIONERY LOTUS NOTES FROM ACCESS

I can get a stationery from access, change the body and send it,
 but the problem I have is that every time  I do it a new stationery is being created automatically
in the view stationery.
I'm using the sentence notesdoc.send 0, recipient

Could you help me, please? I just want to send it without creating new stationeries.
Comment
Watch Question

Steve KnightIT Consultancy
CERTIFIED EXPERT

Commented:
Can you show us the rest of the notes part of the VBA code you are doing.... are you grabbing a document from the stationery view, populating it then sending?

Steve

Author

Commented:
Exactly. I want to get the stationery from the stationery view, populating it and sending it, but the problem is that every time I send it,  a new stationery appears in the stationery view.
Many thanks in advance four your attention and your time. Could you help me?
This is the code I'm using:

Sub sendemail()
Dim db As Database      
Dim mail As String
Dim rs As Recordset  

Set db = CurrentDb      
Set rs = db.OpenRecordset("SELECT [RIC Pendientes Aceptacion Prueba].Correo FROM [RIC Pendientes Aceptacion Prueba]", dbOpenDynaset)
    rs.MoveFirst            

    Do While Not rs.EOF    
       
        Dim notesSession As Object 'The notes session
        Set notesSession = CreateObject("Notes.NotesSession")
        Dim notesDb As Object      
        Set notesDb = notesSession.GETDATABASE("B3/Spain/ERBG", "mail\i1111.nsf")
        If notesDb.IsOpen = False Then  
            notesDb.OPENMAIL
        End If
        Dim UserName As String      
        UserName = notesSession.UserName
        Dim notesDoc As Object
        Set notesDoc = notesDb.CREATEDOCUMENT
        Dim view As Object
        Set view = notesDb.GetView("Stationery")
        Dim notesDocv As Object
        Set notesDocv = view.getfirstdocument("RIC Prueba")
        Set notesView = Nothing
        Set notesDoc = notesDocv
        mail = rs("Correo")
        notesDoc.Form = "Memo"
        notesDoc.sendto = mail
        Set view = notesDb.GetView("Inbox")
       
        notesDoc.SEND 0, Recipient
       
        Set notesDoc = Nothing
        Set notesSession = Nothing
        Set notesDb = Nothing
       
      rs.MoveNext
    Loop  

End Sub
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
Let's clean up the code a little, before moving to the more difficult stuff (because I still fail to understand why the stationery is saved).

Just a question: when multiple mails are to be sent, are they always to be sent by the same user? If so, the mail database can be opened once.

The code below should do at least the same as the original code.
Sub sendemail()
	Dim db As Database      
	Dim rs As Recordset  
	
	Set db = CurrentDb      
	Set rs = db.OpenRecordset("SELECT [RIC Pendientes Aceptacion Prueba].Correo FROM [RIC Pendientes Aceptacion Prueba]", dbOpenDynaset)
	rs.MoveFirst            

        Dim notesSession As Object 'The notes session
        Set notesSession = CreateObject("Notes.NotesSession")
        Dim notesDb As Object      
        Set notesDb = notesSession.GETDATABASE("B3/Spain/ERBG", "mail\i1111.nsf")
        If notesDb.IsOpen = False Then  
            notesDb.OPENMAIL
        End If
       	Dim view As Object
       	Set view = notesDb.GetView("Stationery")
        Dim UserName As String      
        UserName = notesSession.UserName
        Dim notesDoc As Object

	Do While Not rs.EOF    
       
'        	Set notesDoc = notesDb.CREATEDOCUMENT
        	Dim notesDocv As Object
        	Set notesDocv = view.getfirstdocument("RIC Prueba")
        	Set notesDoc = notesDocv
        	notesDoc.Form = "Memo"
        	notesDoc.sendto = rs("Correo")
       
        	notesDoc.SEND 0, Recipient
       
        	Set notesDoc = Nothing
        	Set notesSession = Nothing
        	Set notesDb = Nothing
       
      	rs.MoveNext
    Loop  

End Sub

Open in new window

Commented:
I think this could be a simple matter of not tidying up the mail properly before sending.
Your using the stationery doc as a template for your memo and chaning form to memo.
When you send the document it is being shown in the sent view and also the stationery view.

Check as there is an additional field ISMAILSTATIONERY that has to be set to 0 to mark as not stationery anymore, it is not the form field that determines what is or is not stationery.
just add
notesDoc.ISMAILSTATIONERY = 0
after setting form value

Sjef's tidy up of the code could be a good help to you also.
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
@doninja: there is no explicit Save in the code, nor is there the implicit way that can be activated using SaveMessageOnSend... What would make the mail appear in the Sent view??

Commented:
@sjef, I believe if user client system has save by default then the .send will save a copy, don't need a specific save, if run on sever then your right as it defaults to not.
Also the fact it is showing as an additional doc in stationery view would suggest it is being saved by some mechanism, and .send is the most logical since there is no .save
 
Apart from the send/save part I think the isstationery field is what OP is looking for

Author

Commented:
It was simplier than I thought.
Just adding notesDoc.ISMAILSTATIONERY = 0 changes the form to a standard memo and it is not saved in the Stationery view anymore.
Now it works perfectly!!

Many thanks!!
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
Please don't use the cleaned-up code... ;-)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.