Query Report to Mass Email

[i5sfe]

I have a query that I output to an excel file using DoCmd.TransferSpreadsheet. Then all excel files are sent out using a command line emailer (blat). This works fine for a few Company Branches, but I need to send the same report to 50+ Salespeople and it must show only that Salespersons customers.

So I would like to populate a table with the Saleperson's name and email address. Run the query filtering it by the Salesperson's name and then use DoCmd.SendObject to send an emailed report to each salesperson.

I am a newbie to VBA so please excuse my ignorance.

[CreganTur]

Welcome to the forum- it's always good to see new members!

Hope it's not too late to offer you some help on this problem.

First off, the DoCmd.TransferSpreadsheet method will not work for this because you can only provide the name of a table or query to export. This would be a problem for you, as you would need a query for each salesman in order to send them just their report- and I bet you don't want to build that many queries!

To work around this, we're going to use a DAO (Data Access Object) connection- it's a way of connecting to a data source (within the same database as the code you're running, or an external data source). You can use it to do a lot of different things.

I've attached an example database that shows how you can accomplish something close to what you want. It has a table containing names and e-mail addresses and employeeIDs, and a separate table containing Employee data (all information is eroneous). It will create a new Excel spreadsheet (named for the employee whose information it contains) and fill the spreadsheet with the correct data.

You can adapt this code to accomplish what you want- creating specific reports for all of your salesmen. You'll just need to add in the part to send the e-mail (my example doesn't cover that).

Testing the Code:

Download the attached .zip file and extract the database.

Open the database and go to the Modules tab. Open the ExportQueriesToExcel module. Put your cursor anywhere inside the sub, and then press F5 to see it run. It will create 9 Excel files on your C:\ drive; 1 for each of the employees listed.

It's going to take some work to adapt this to your needs, but I really think it can work for you.

HTH:thumb

[ibgreat]

I was looking for a similar solution awhile back. Thanks!!