Question Sending to multiple email address from table in Lotus Notes

[SteveBriscoe]

I am trying to send to multiple email addresses taken from multiple rows in a table using lotus notes. I have the lotus notes part of it working, I think it's the retrieving and sending to multiple rows/addresses I am having the problem with.

I have a table called "Subscriptions" and within there, a field called product and a field called "subscriber_email_address". The way the form works if you select a product on the form and click send to send an email to all users subscribed to that product (taken from the subscriptions table). My code so far, and apologies if it's glaringly obvious but I am by no means an expert user...

Dim MyRS2 As DAO.Recordset
Set MyDB2 = CurrentDb()

'retrieve list of subscribers from the subscriptions table...

Set MyRS2 = MyDB.OpenRecordset("Select FROM Subscriptions where product_name = '" & Forms!Add_New_Article.Combo2.Value & "'", dbOpenDynaset)

'Start the code to generate the emails...

Dim Product As String
Dim notesdb As Object
Dim notesdoc As Object
Dim notesrtf As Object
Dim notessession As Object
Set notessession = CreateObject("Notes.Notessession")
Call notesdb.openmail

'Start the loop to email each subscriber...

Do While Not MyRS2.EOF
Set notesdb = notessession.getdatabase("", "")
Set notesdoc = notesdb.createdocument
Call notesdoc.replaceitemvalue("Sendto", "" & subscriber_email_address)
Call notesdoc.replaceitemvalue("Subject", "" & Text0.Value)
Set notesrtf = notesdoc.createrichtextitem("body")
Call notesrtf.appendtext("" & Text4.Value)

'Send message...

Call notesdoc.Send(False)
Set notessession = Nothing

MyRS2.MoveNext
Loop
DoCmd.Close

Else
DoCmd.Close

End If

 

[SteveBriscoe]

Have made some progress on this after searching through some of the posts on this site, got the bit working I wanted to get working, i.e. retrieving all the email addresses and looping through them to send the mail, however, then started getting an error 91. From what I read up on this, the 91 can be ignored so I added error handling to ignore it, however, now get an error 7063 on the first address and all subsequent addresses are ignored.

From what I have read about this, the 7063 is handled by telling the user to log into notes, however, I am already logged into Notes (v8). My code now looks like this...

'Define Recordset Variables for Mail

Dim rst As New ADODB.Recordset
Dim NumRecords, CurrentRecord
Dim Str As String

'Email Variables

Str = "Select subscriber_email_address from Subscriptions Where product_name = " & Forms!Add_New_Article.Combo2.Value

With rst
.CursorLocation = adUseServer
.Open Str, CurrentProject.Connection, 3, 3
.MoveFirst
NumRecords = .RecordCount
End With
MsgBox NumRecords & " records."
Dim Product As String
Dim notesdb As Object
Dim notesdoc As Object
Dim notesrtf As Object
Dim notessession As Object
Set notessession = CreateObject("Notes.Notessession")
Call notesdb.openmail

'Loop

Do While Not rst.EOF
CurrentRecord = rst!subscriber_email_address
MsgBox "Current: " & CurrentRecord

*** MY CODE WORKS TO HERE AS THE CURRENT RECORD IS DISPLAYED BEFORE THE ERROR 7063

Set notesdb = notessession.getdatabase("", "")
Set notesdoc = notesdb.createdocument
Call notesdoc.replaceitemvalue("Sendto", "" & Str)
Call notesdoc.replaceitemvalue("Subject", "" & Text0.Value)
Set notesrtf = notesdoc.createrichtextitem("body")
Call notesrtf.appendtext("" & Text4.Value)
Call notesdoc.Send(False)
Set notessession = Nothing

rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End If

 

[SteveBriscoe]

Have made a bit more progress, have added the following line between the "Call notesdb" and "Call notesdoc" lines...

Call notesdb.OPENMAIL

And the code now falls over with a 7294 error when it reaches...

Call notesdoc.Send(False)

 

[SteveBriscoe]

Got there in the end, the value of searching through this site got me most the way there so sorry if I wasted anybodys time looking.

So final code below for anybody else who has similar problems...

I do get errors 91 and 0 with this code, but added a bit of handling to ignore them as they don't seem to cause any problems when ignored...

Dim rst As New ADODB.Recordset
Dim NumRecords, CurrentRecord
Dim Str As String

'Email Variables
Str = "Select subscriber_email_address from Subscriptions Where product_name = " & Forms!Add_New_Article.Combo2.Value
With rst
.CursorLocation = adUseServer
.Open Str, CurrentProject.Connection, 3, 3
.MoveFirst
NumRecords = .RecordCount
End With

Dim notesdb As Object
Dim notesdoc As Object
Dim notesrtf As Object
Dim notessession As Object
Set notessession = CreateObject("Notes.Notessession")
Set notesdb = notessession.getdatabase("", "")
Call notesdb.OPENMAIL

'Loop
Do While Not rst.EOF
CurrentRecord = rst!subscriber_email_address
Set notesdoc = notesdb.createdocument
Call notesdoc.replaceitemvalue("Sendto", "" & CurrentRecord)
Call notesdoc.replaceitemvalue("Subject", "" & Forms!Add_New_Article.Text0.Value)
Set notesrtf = notesdoc.createrichtextitem("body")
Call notesrtf.appendtext("" & Forms!Add_New_Article.Text4.Value)
Call notesdoc.Send(False)

Set notessession = Nothing

rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End If

 

[sunilvedula]

how do i attach an result of a query without saving it anywhere. I mean once this is run i want also an query to run and attach it in lotus as an attachment automatically. Any Ideas?

 

[sunilvedula]

.CursorLocation = adUseServer
.Open Str, CurrentProject.Connection, 3, 3

what do these steps actually do? i would love toknow them before i use.