send email in lotus notes from access

[Guest]

i would like to use a query in access to generate an email in lotus notes.
Any suggestions? If you need additional information, I can provide.

Thank you!

 

[Arvin Meyer [MVP]]

This is from an old newsgroup posting. I didn't write it and haven't tested
it:
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

This code is courtesy of John Hawkins.

'Public Sub SendNotesMail(Subject as string, attachment as string,
'recipient as string, bodytext as string,saveit as Boolean)
'This public sub will send a mail and attachment if necessary to the
'recipient including the body text.
'Requires that notes client is installed on the system.


Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient
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
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)

'Start a session to notes

Set Session = CreateObject("Notes.NotesSession")

'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 = Left$(UserName, 1) & Right$(UserName, (Len(UserName) -
InStr(1, UserName, " "))) & ".nsf"

'Open the mail database in notes

Set Maildb = Session.GETDATABASE("", MailDbName)

If Maildb.ISOPEN = True Then

'Already open for mail

Else

Maildb.OPENMAIL

End If

'Set up the new mail document

Set MailDoc = Maildb.CREATEDOCUMENT

MailDoc.Form = "Memo"

MailDoc.sendto = Recipient

MailDoc.Subject = Subject

MailDoc.Body = BodyText

MailDoc.PostedDate = Now()

MailDoc.SAVEMESSAGEONSEND = SaveIt

'Set up the embedded object and attachment and attach it

If Attachment <> "" Then

Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")

Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment,
"Attachment")

MailDoc.CREATERICHTEXTITEM ("Attachment")

End If

'Send the document

MailDoc.send 0, Recipient

'Clean Up

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing

End Sub

 

[Guest]

I have an example that I've been told works, by a person that I helped a few
years ago. I don't have Lotus Notes installed, so I have not personally
tested it.

This cmdSendEmail_Click() procedure was for a command button named
"cmdSendEmail" on a bound form. Lotus Notes apparently requires multiple
recipients to be submitted as an array, thus the use of the Split function,
as shown in this line of code:

Call doc.replaceitemvalue("SendTo", Split(strRecipients, ","))


Here was his code module:

From: {Name Removed}
To: Tom Wickerath
Sent: Friday, February 04, 2005 9:29 AM
Subject: Fixed it!!!!!!!!!!!!!!!


Tom,

I tried another route and it fixed the problem. I can now send the string
of addresses!! I've enclosed the completed code for this "little" email
detail!!!!!!!!!
Note the new module on the end. Your help has been invaluable. Thanks so
much!!

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Compare Database
Option Explicit

Private Sub cmdSendEmail_Click()
On Error GoTo ProcError

If Me.Dirty Then
Me.Dirty = False
End If

Dim strRecipients As String
Dim strBody As String
Dim SendTo As String
Dim strAttachpath As String
Dim Session As Object
Dim db As Object
Dim doc As Object
Dim rtitem As Object
Dim tmp As Object

Set Session = CreateObject("Notes.Notessession")
Set db = Session.GetDatabase("", "")

Call db.OPENMAIL
strRecipients = BulkEmail()
strAttachpath = "s:\ECR Temp\LotusNotus ECN Report.rtf"

Set doc = db.CreateDocument
Set rtitem = doc.CreateRichTextItem("body")

DoCmd.OutputTo acOutputReport, _
"LotusNotus ECN Report", acFormatRTF, _
strAttachpath, False

strBody = _
"THIS IS ONLY A TEST. Please reply so I know you got this. " & _
"I am testing an auto email feature of the new ECR DB. " & _
"You may be recieving more than one of these. " & _
"Thanks for your help!!"

Call rtitem.AppendText(strBody)
Call rtitem.AddNewLine(2)

Set tmp = rtitem.EmbedObject(1454, "", strAttachpath)

Call doc.replaceitemvalue("SendTo", Split(strRecipients, ","))
Call doc.replaceitemvalue("Subject", "NEW ECN")
Call doc.Send(False)

MsgBox ("Report has been sent!")

ExitProc:
'Cleanup
On Error Resume Next
Set Session = Nothing
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdSendEmail_Click Event Procedure..."
Resume ExitProc
End Sub

Function BulkEmail() As String
On Error GoTo ProcError

'Return a concatenated string from individual records in a recordset

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strOut As String
Dim lngLen As Long
Const conSEP = ", "

Set db = CurrentDb()
Set qdf = db.QueryDefs("quniAllRecipients")

qdf![[Forms]![ECN Form]![ECR Number]] = [Forms]![ECN Form]![ECR Number]

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

With rs
Do While Not .EOF
strOut = strOut & !Recipient & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)
If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If

ExitProc:
'Cleanup
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set qdf = Nothing
Set db = Nothing
Exit Function

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure BulkEmail..."
Resume ExitProc

End Function

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tony Toews [MVP]]

i would like to use a query in access to generate an email in lotus notes.
Any suggestions? If you need additional information, I can provide.

Also see Microsoft Access Email FAQ - Lotus Notes
http://www.granite.ab.ca/access/email/lotusnotes.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/