MS Access and Lotus Notes

[sharonc]

Does anyone know if MS Access can call Lotus Notes for email?

 

[pdupreez]

I have created the module emailer below to use whenever I want to e-mail something. I use Lotus Notes 4.63. If I have more than one user to e-mail to (on a regular basis) I have a database with email addresses and selection fields which will enable mailing specific reports when required. By using a do until rst.EOF (see below) you can e-mail selectively to specific users in your database.

The code requires Lotus Notes to be open and a user to be logged in. I must still figure out how to automatically login/assign a password.

************************
tblMailAddresses Table
************************
email Text Sets email address
Orders Yes/No Orders are mailed/Not
DailyReports Yes/No Reports are mailed/Not
etc....

***********************************
HOW TO SELECTIVELY E-MAIL USERS USING EMail FUNCTION
***********************************

Set rst = dbs.OpenRecordset("Select * from tblMailAddresses where Orders = True&quot
Do Until rst.EOF
Recipient = rst!EMail
EMail Subject, Bodytext, Recipient, Attachment, SaveIT
rst.MoveNext
Loop

***************************
EMail FUNCTION BELOW
***************************

Public Function EMail(Subject As String, Bodytext As String, Recipient As String, Attachment As String, SaveIT As Variant)

Dim Maildb As Object
Dim UserName As String
Dim MailDBName As String
Dim MailDoc As Object
Dim Session As Object
Dim AttachME As Object
Dim EmbedObj As Object

Set Session = CreateObject("Notes.NotesSession&quot
UserName = Session.UserName
MailDBName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " &quot)) & ".nsf"
Set Maildb = Session.getdatabase("", MailDBName)

If Maildb.isopen = True Then
Else
Maildb.openmail
End If

Set MailDoc = Maildb.createdocument
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient
MailDoc.Subject = Subject
MailDoc.Body = Bodytext
MailDoc.SavemessageonSend = SaveIT

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 (Attachment)
End If

MailDoc.send 0, Recipient
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing

Exit Function

If Err.number = 7063 Then
MsgBox &quot;Notes Mail System not open&quot; & Chr(13) & _
&quot;Please Activate Mail system and Retry again&quot;
Resume Next
End If

MsgBox Err.Description
Resume Next

End Function

 

[Klopper]

pdu,

is it possible to send a single mail to more than one recipient? i ask because looping through lots of mail addresses may take a little time, especially if there is an attachment

 

[sharonc]

Thanks for your help. It works great.

 

[pdupreez]

Hi Klopper

The code is written as a function so that you can just feed in the recipient's name into the Recipient variable for the &quot;EMail Subject, Bodytext, Recipient, Attachment, SaveIT&quot; function.

If the Recipient variable contains more than one name ie:
joebloch@anywhere.com, another@everywhere.com,etc .... I foresee no problem.

As a matter of fact, you can stll read in the specific names from a database, ascii file etc, and concatenate them to the Recipient variable ie:

Recipient = name1 &&quot;,&quot; & name2 & &quot;,&quot; etc...!

Hope it helps

Regards

Pieter