Email Report Automation - Access & LotusNotes

[bkf7911]

I know access, and have seen Visual Basic. Looking for code that will:

Cycle through a list of users in a table, creating a report specifically for the user, and sending that report to the user via LotusNotes.

Table1:

Name Email
Bob Bob@bob.com
Sue Sue@sue.com

The code should create Bob’s report as an attachment and send it to him. Then it should create Sue’s report and send it to her. And further down the list.

I’m currently using a list-box on a form to populate Query Criteria for the report, then a macro with SendObject to push the report to the email.

The only issue, is that the user must then enter the correct email address for the recipient, send the email, return to the form, change the list-box to the next person, and initiate the macro (via command button) again.

How would it be possible to:

1. Set focus on the first NAME field in the table/query.
2. Create a report, saved as an attachment to location X
3. Initiate LotusNotes Email, addressing it to the Email field associated with the current name.
4. Attach the report from location X
5. Send the email
6. Rinse/Repeat steps 1-6 until completing the routine for the last person in the table/query.

So far, I’ve got:
*****************************
Private Sub Command19_Click()
Dim NotesDB As Object
Dim NotesDoc As Object
Dim NotesRTF As Object
Dim NotesSession As Object
Dim Email As String

Email = Me!Email


Set NotesSession = CreateObject("Notes.NotesSession")
Set NotesDB = NotesSession.GetDatabase("", "")

Call NotesDB.OpenMail

Set NotesDoc = NotesDB.createdocument
Call NotesDoc.replaceitemvalue("Sendto", Email)
Call NotesDoc.replaceitemvalue("Subject", "Moto Exceptions")

Set NotesRTF = NotesDoc.CreateRichTextItem("body")
Call NotesRTF.appendtext("Please click on the attachment box below. Select ""Launch"" if you are given the option.")
Call NotesRTF.addnewline(2)
Call NotesRTF.embedObject(1454, "", "C:\thisisatest.txt", "thisisatest.txt")

Call NotesDoc.Send(False)

Set NotesSession = Nothing
MsgBox "Message Sent to" & Email



End Sub
*********************************

How can i change this to cycle through query results, transferring the report to the drive for each, then sending it to the email on file? Any help is appreciated.

 

[Zygor]

Perhaps creating a recordset from the query and looping through it.....

something like set rst=db.openrecordset (SQL goes here)

for IntI = 1 to rst.recordcount
docmd.openreport ReportName (somewhere here pass parameter of person)
docmd.save acreport....
then call your email routine
next IntI

This code Will Not work. syntax is wrong...just jotting down the ideas....