How to automatically email each page of a Report seperately?

[critofur]

Hi,
We have a database of books for our little library. We've got reports to generate letters with one page for each borrower, with the borrower's name etc. at the top followed by the list of their overdue books.

For some of the borrowers we'll mail them a letter, for others we'll email them.

Could someone tell me how I can send each page of the report from access as an email (not the whole report as one email)? I'm assuming (but don't know) that SendObject is what we'll want to use?

Thank you,

Christopher

 

[PaulBricker]

The SendObject Method will do the trick, but you will have problems doing it by page. The preferred way to do this is to filter the report based on the borrower.
You have to have a recordset of borrowers that you can loop thru. Then open the report with the filter, use the SendObject Method to sent the report, close the report and then loop to the next borrower.

The code would look something like this.

Dim rst as DAO.Recordset
Dim strRptName as String
Dim strWhere as String
strRptName = "ReportNameHere"
Set rst = CurrentDb.OpenRecordset("BorrowersTable", dbOpenDynaset)
While Not rst.EOF
rst.MoveFirst
strWhere = "BorrowerFieldonReport = '" & rst!BorrowerField & "'"
DoCmd.OpenReport strRptName,acViewPreview,,strWhere
DoCmd.SendObject acSendReport...etc....
DoCmd.Close acReport, strRptName, acSaveNo
rst.MoveNext
Loop



Paul

 

[rr191]

Just a simple correction. The rst.Movefirst line should be moved to before the While Not rst.EOF. Otherwise you would have an infinite loop.

-- Rob

 

[critofur]

Ok, thank you. I haven't done VB scripting? (Macros?) yet so I've got a bit of learnign to do to implement that.

In the mean time, anyone know of an example anywhere which shows how to simply export or save each record of a report as a seperate text file? I don't know enough to know how to search for and find these things which probably already exist in multiple websites/forums...

Could I find this stuff in the example "north wind" database? I keep seeing that mentioned, maybe it comes with Access?

I can just send those as emails manually in the mean time until I've learned how to setup Access to send emails automatically.

Or, alternatively, any simple (with files, or a complete tutorial) examples of Access sending emails (preferably, but not nesc. from reports) where one email is sent to each person in the database which meets the certain criteria? (Or where one email is sent for each record/page in the report)?

 

[critofur]

I'll try reading pages like this one:

http://www.databasedev.co.uk/email-from-ms-access-form.html

Hopefully I'll find one that is simple enough to start with.

 

[PaulBricker]

I gather from your last two posts that you need help with other stuff as well. My suggestion would be to set up a query that has all the relevent information in it. Then try and build a report from the query. This will allow you to filter the report based on some input. Once you get there, we can work on the code to actually email your customers.


Paul