create and email multiple reports

[jkid817]

Here is what I have. I have a table with customer account numbers, a table with customer emails, and a query that creates a report on the customer’s equipment (Equipment Health Report). I need to keep my two tables seperate so I can't combine them into one table.

What I need is a way to have the report run by getting a customer account number, run the report, email the report to the customer, and then go get the second account number. I need this to repeat until all of the customers have received a report without any further input from me except to hit the outlook send button. I guess I need a macro but not sure.

P.S. – I’m a Visual Basic noob so please no techno speak.

Thanks

[Seth Schrock]

Actually, I think that VBA would be easier.

Having the email addresses and the account numbers in different tables will only work if there is a link between the tables (like a customer ID number).

Are you wanting to have the report be the email, or are you wanting the report to be an attachment? Both are possible, but the second method is more complicated.

[jkid817]

@Seth Schrock
I need the report to be an attachment.

[Seth Schrock]

Sorry it took so long to respond. I've been very busy all weekend.

To create a file on your computer that you can attach to an email, you would use the DoCmd.OutputTo command (here is the MSDN website with the syntax for it: DoCmd.OutputTo Method). You would then need to keep track of the location and the file name you give it so that you can attach it.

For the email part, you will need to add a reference to Outlook. To do this, you need to open your VBA editor, open the Tools menu and select References. Scroll down to Microsoft Outlook x Object Library where the x is the version of Outlook you are running (2007 = 12.0, 2010 = 14.0, 2013 = 15.0, etc.) and put a checkmark in the box next to it. You now have access to all the Outlook commands in VBA.

For the email coding part, here is a link on how to generate an email with an attachment: Using Automation to Send a Microsoft Outlook Message. So what you need to do is loop through generating each report, get the email address of the recipient and the file path of the attachment and pass it to the function that sends the email.

[jkid817]

Thanks Seth,

After a little trial and error I was able to get this to work.

[Seth Schrock]

Good! Glad I could point you in the right direction.