I am trying to figure out how to email pages of a mailmerge report to different recipients. The document in question is a letter to our suppliers (over 3000). The letters are generated by Access and then sent to a Word Mailmerge. Each page of the report is a different supplier's letter. I need each page of the report to be emailed only to that supplier (sometimes more than one person at that supplier).

I have an Auto-email program in Lotus Notes, but in order to use that program, I would need to write the mailmerge letter to 3000+ separate files. I consider that a last resort!

Also, can the "sender" of the email be set to that Source's Account Manager (instead of me)? I don't want to get 3000 replies to the message!

I am not a programmer, so please include detailed directions if possible. Any sample code would also be a big help!

Someone here at work was able to provide some code to do what I needed. The code takes two tables, a query and a report. The code creates the query which splits table A into files based on a list in table B. I then created the report based on the query written by the module. The module runs the query, and then exports the report to an rtf file. This solution works for what I am doing so maybe it will help someone else. Thanks.

Option Compare Database

Public Function CompSumWperf()
' don't forget to add in DA0 3.6 under Tools...references in order to use DAO code.
' otherwise you will need to write ADO code.'

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Dim strSource_No As String

'open recordset for looping
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("select Source_code from Source")

'go to the first record
rs.MoveFirst


'while not at the end of the recordset....
While Not rs.BOF() And Not rs.EOF()

'load the value from the first record
strSource_No = rs!Source_code


'delete the query if it exists

For Each qdf In db.QueryDefs

If qdf.Name = "QueryForLoopSummary" Then

db.QueryDefs.Delete (qdf.Name)

End If

Next qdf


'create a stored query
Set qdf = db.CreateQueryDef("QueryForLoopSummary", _
"Select * from Perfw where Source_code = '" & strSource_No & "'")


'create a Word document using the report "Weekly_Perf" as the source and
'create as w:\Shipdisn\Z-Auto-email\Perf concatenated with the source code.

DoCmd.OutputTo acOutputReport, "WEEKLY_PERFormance", "RichTextFormat(*.rtf)", "c:\data\" & strSource_No & ".rtf", False



rs.MoveNext

Wend

'close and clean up
rs.Close
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing

End Function