How To Mail Merge An Access Report?

[MHNI]

Here is my dilemma: I have a nice report that lists transactions with EACH one of my customers. I would like to send the report results to EACH customer individually (electronically). The problem is that if I mail merge, a customer with say 10 transactions will be getting 10 EMAILS.... I am anxiously waiting for your advice.

Thanks

[Paul Komski]

There are a lot of different approaches and some of them depend on whether you want to embed the results in the email or just add an attachment to the mail. There are also different possibilities and methods in the different versions of office.

The simplest approach might be to go to the file menu and choose export and then choose htm or rtf. Then go to the exported files and send them to your email application.

As long as the report has each client on a separate page (or your report only shows the transactions for one client at a time) then all the transactions should be included page by page.

Setting up MailMerge Fields in a Word Document and linking to the records in an Access database can be done with multiple transactions but it can be hard enough to setup if the number of transactions differs each time.

[MHNI]

You caught my attention. What I like with Mail merge is the Electronic mail format where you define the FIELD that is the EMail and then off the emails go to 200 customers without manual involvement on my part. So...if I have my report in Access with a page break (I force a new page after each customer multiple transactions are displayed....how do I get to EMAIL EACH CUSTOMER their own report without having to copy and paste the report from an RFT file and individually email them ? I have been searching online and with so called mail merge gurus at my company and I cannot believe that NO ONE has had the need to EMAIL each report to separate email addresses all at once?

[Paul Komski]

Just so no wires get crossed, which version of office are you using?

For further edification, when you say that you define the field that is the eMail, is this (a) a special function of Mail Merge, with the eMail address stored in an underlying text field or (b) is the field actually a hyperlink field in Access using a mailto: url that you would subsequently click on in Word to activate the sending process?

The fundamental point about mail merge is that each individual field must be laid out for each "mailing" record. Typically for snail mail the text of a letter would be written in word and the fields for that individual client laid out for the letter (and envelope) before printing the letter for each client in the underlying database. There would typically be fields for date, forename, surname, street, town, etc. Instead of typing the main text of the letter in word it would be possible to include the same text but written into the underlying database into a single memo field.

A mail merge document needs to be based on a table or query and the only way it can emulate an Access report is to lay out the fields identically in Word to the way they are laid out in your report. You cant use the report, per se, using mail merge. I suspect that the transactions are just grouped in your report's detail area so that multiple records appear as a series of rows (but nicely packaged for that page) but that in design view there would only be one row to be seen. For mail merge to work you are going to have to somehow collect together all these transactions into one field in an underlying query or table (and this could certainly be done programatically using VBA) or arrange them as "fixed" columns in a cross-tab query.

To do the latter you would have to use the same number of transactions for each client/mailing in the same way you would have the same number of name and address fields for each client's mail merge record. Not only would the same number of transaction fields need to be specified for each record but each one would need a unique name (specified in the cross tab query's properties under "Column Headings"). One additional problem would be if you were using a hyperlink field (on of a number of OLE fields) that you would not be able to group on such a field in a cross tab query.

Hope I haven't confused the area so post back if you need clarification.