Send Lotus Notes Email through Access?? 4

[jofarrell]

Is it possible to code in Access to send email of a report/results through Lotus Notes Email? That is what the company is set up on and they use the electronic signature feature which is key to the information that is required.

Any suggestions are greatly appreciated.


Joanne

 

[baronvont]

I have seen a few questions relating to this and would like to say that I have done a bit of work on it. I got as far as getting access to fire off mails via lotus using different bits of code, but due to the immense number of bugs in Lotus I kept on tripping and eventually gave it up. The one thing I really needed to do was to create folders in a notes database automatically - this I managed, but due to a bug, the folder was not visible till u closed and opened the database!!

Anyway, most of my reserch came from

http://www.notes.net

so go there and search around. Also look in the Agent FAQ on the left of the home page for useful info!

 

[jofarrell]

Thank you very much for the information. If I have any greater luck then you I will let you know

Joanne

 

[baronvont]

Good luck, BTW, I have resorted to using the good old 'SendObject' command for the moment. To get Access (indeed all of Office97) to recognise Notes as the default e-mail you have to go into Internet Explorer and in Tools-> internet options -> Programs select notes as the default e-mail.

If it still is not recognising notes, you'll have to load outlook (not Outlook Express!) after loading Notes! Don't ask me why - I had to on the 3 pc's I used here!

 

[Guest_imported]

Dear Jofarrell.

It is indeed possible to construct & send Lotus Notes Mail through Microsoft Access. We are doing here as we speak. It does require some VBA coding, but not much.

Fist you need to get the DNotesX ActiveX components. (

http://www.vsoft-tech.com.au/dnotesx.html)

Read their help files for coding instructions. If you can't figure it out, contact me on this message board.

Regards,

Gordon North

 

[Howiee]

There is some vba code @

http://www.fabalou.co.uk/VBandVBA/lotusnotesmail.htm

that may help you out. I used it a while back and it worked sweet.

Howiee.

 

[Klopper]

Baron12, did you ever manage to Lotus Notes to send the mails without opening Outlook?

If so, please could you post a code sample!


This forum post below confuses me further:

http://www-10.lotus.com/ldd/esforum...e472b3182ff8df8b85256bcf0041fc3b?OpenDocument

Klopper

 

[baronvont]

Hi Klopper,

I had resorted to using 'sendobject' which as you say opend outlook as the default mail server. The trick is to set the default mail servicee in Explorer to Notes and also to reload outlook. However as the company has now switched to outlook I havn't been too bothered with notes for a while..

Here's another bit of code that directly sends an attachment file from Access to Notes without any intervention. I works fine

Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, cc As String, BodyText As String, SaveIt As Boolean)

'Set up the objects required for Automation into lotus notes
Dim MailDb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself NotesDocument
Dim MailDoc2 As Object
Dim AttachME As Object 'The attachment richtextfile object
Dim session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
Dim CurrentDatabase As String
Dim NotesSS As New NotesSession
Dim NotesDb As New NotesDatabase
Dim SendFolder As String
Dim view As NotesView


On Error GoTo Errshow:


NotesSS.Initialize '("password&quot ' you can place your password here to go straight in if not logged in already

With NotesSS
Set NotesDb = .GetDatabase("servername",".nsf file",False)
End With

With NotesDb
'Start a session to notes
Set session = CreateObject("Notes.NotesSession&quot
'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string
UserName = session.UserName
MailDbName = NotesDb
Set MailDb = session.GetDatabase("", MailDbName)

If Not (MailDb.IsOpen) = True Then MailDb.OPENMAIL

'Set up the new mail document
Set MailDoc = MailDb.CreateDocument
With MailDoc
.Form = "Memo"
.sendto = Recipient
.CopyTo = cc
.Subject = Subject
.Body = BodyText
.SaveMessageOnSend = SaveIt
.principal = "user account" '(if different to your default account name)
'.PutInFolder SendFolder, True '(if you need a copy in the sent folder)
End With
'Set up the embedded object and attachment and attach it
If Attachment <> &quot;&quot; Then
Set AttachME = MailDoc.CreateRichTextItem(&quot;Attachment&quot
Set EmbedObj = AttachME.EmbedObject(1454, &quot;&quot;, Attachment, &quot;Attachment&quot
MailDoc.CreateRichTextItem (&quot;Attachment&quot
End If

End With

'MailDoc.PutInFolder (SendFolder)

'Send the document
MailDoc.Send 1, Recipient


MsgBox &quot;Sent!&quot;, vbExclamation

Cleanup:

Set MailDb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set session = Nothing
Set EmbedObj = Nothing

Exit Sub

Errshow:
MsgBox (Err.Description)
GoTo Cleanup

End Sub

 

[Klopper]

Hi Baron12, thanks for the response.

This code looks perfect, juts one question:
How do I find out what the values for the current user's servername and .nsf file for the statement below?

Set NotesDb = .GetDatabase(&quot;servername&quot;,&quot;.nsf file&quot;,False)

(My users are all using Notes R5 on NT network.)

Thanks
Klopper.

 

[baronvont]

Klopper,

In notes, go to File -> Database -> Properties

The server & filename details will be listed there for the user

Cheers

 

[Klopper]

Thanks Baron12, have a star for your efforts!

 

[baronvont]

You're too kind

 

[Klopper]

Me again!

Any ideas how I send the same mail to more than one recipient?

I know I could code something to loop through the code for each recipient but I will be e-mailing attachments and this will make it slllloooooo

http://wwwwww!

t.i.a
Klopper

 

[pdupreez]

You could still run a loop with all the recipients, just concatenting the recipients with a comma in between
eg:

Recipients = Recipients + &quot;,&quot; + nextRecipient

 

[tomvdduin]

Hi,

a question about the VBA code from Baron12:

VBA gives an error when compiling: I mustn't use the keyword New in the following line:

Dim NotesSS As New NotesSession

I have checked the lotus notes automation classes in references.

any idea whats wrong? I use MS Access 2002 and lotus notes r6

greetz

Tom

 

[dschomburg]

Tom,

I am also trying to use the code. But the error I'm receiving is &quot;Argument not Optional&quot; Perhaps if you can point out where I have gone wrong, we could work through this together.

Here's a function I use to call the sub;

Function fun_sendnotes()
Call SendNotesMail(&quot;TEST SUB&quot;, &quot;C:\Documents and Settings\schombud\Desktop\test.xls&quot;, &quot;dave_r_schomburg@bankone.com&quot;, , &quot;THIS IS A TEST&quot
End Function

AND HERE's WHAT THE SUB LOOKS LIKE;

Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, cc As String, BodyText As String, SaveIt As Boolean)

'Set up the objects required for Automation into lotus notes
Dim MailDb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself NotesDocument
Dim MailDoc2 As Object
Dim AttachME As Object 'The attachment richtextfile object
Dim session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
Dim CurrentDatabase As String
Dim NotesSS As New NotesSession
Dim NotesDb As New NotesDatabase
Dim SendFolder As String
Dim view As NotesView


On Error GoTo Errshow:


NotesSS.Initialize (&quot;password001&quot ' you can place your password here to go straight in if not logged in already

With NotesSS
Set NotesDb = .GetDatabase(&quot;CMHMS003/MS/SVR/BANCONE&quot;, &quot;mail1\davrscho.nsf&quot;, False)
End With

With NotesDb
'Start a session to notes
Set session = CreateObject(&quot;Notes.NotesSession&quot
'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string
UserName = session.UserName
MailDbName = NotesDb
Set MailDb = session.GetDatabase(&quot;&quot;, MailDbName)

If Not (MailDb.IsOpen) = True Then MailDb.OPENMAIL



'Set up the new mail document
Set MailDoc = MailDb.CreateDocument
With MailDoc
.Form = &quot;Memo&quot;
.sendto = Recipient
.CopyTo = cc
.Subject = Subject
.Body = BodyText
.SaveMessageOnSend = SaveIt
.principal = &quot;user account&quot; '(if different to your default account name)
'.PutInFolder SendFolder, True '(if you need a copy in the sent folder)
End With
'Set up the embedded object and attachment and attach it
If Attachment <> &quot;&quot; Then
Set AttachME = MailDoc.CreateRichTextItem(&quot;Attachment&quot
Set EmbedObj = AttachME.EmbedObject(1454, &quot;&quot;, Attachment, &quot;Attachment&quot
MailDoc.CreateRichTextItem (&quot;Attachment&quot
End If


End With

'MailDoc.PutInFolder (SendFolder)

'Send the document
MailDoc.Send 1, Recipient


MsgBox &quot;Sent!&quot;, vbExclamation

Cleanup:

Set MailDb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set session = Nothing
Set EmbedObj = Nothing

Exit Sub

Errshow:
MsgBox (Err.Description)
GoTo Cleanup

End Sub


ANY HELP WOULD HELP A HEAP!

 

[tomvdduin]

Hi Dave,

The problem you've got is easy to solve. Two things:
- if you skip an argument because you don't want to use it, don't just leave a blanc between the comma's but put a blanc string like &quot;&quot; inside it. This is because the function has an argument: cc witch is not optional so it always has to have an value. If you don't use it, use a blanc string. If you do want to make it optional, move it to the last place in the function call and add the word option before it, so it looks like this:
SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean, option cc As String)
- the second, fould in your call to the function: the last argument, SaveIt, used for determing if you want to save the message after sending, doesn't have a value assigned to it in your call.

So your call should look like this:

Call SendNotesMail(&quot;TEST SUB&quot;, &quot;C:\Documents and Settings\schombud\Desktop\test.xls&quot;, &quot;dave_r_schomburg@bankone.com&quot;, &quot;&quot; , &quot;THIS IS A TEST&quot;, True)

HTH.

Can you please tell me what references you've got checked? I think my problem lies with that.

Tnx in advance!

Greetz,

Tom

 

[tomvdduin]

Hi it's me again!

Does anybody use the code presented by Baron12 above? I'm trying to, but I'm getting an error while compiling it. I think I don't have the right reference checked. I checked the Lotus Notes Automation Classes. There are no other references from lotus in the list.

Please anyone???