I have one table that contains the following fields:
Vendor Name (Third Party)
Vendor Email Address
Ultimately I would like a report to pull all of the records for one vendor from the table send the invoice via the email address within the table. Then move to the next vendor and repeat that process until all of the vendors on the table have been accounted for.
I am guessing that I would have to use a For...Next but I am not sure how to pull it all together.
Dennis, what you need is two RECORSET loops to handle this (the outside loop for each vendor, then the inner loop for each invoice). Here's an example. To loop through ONE recordset, let's say your vendors, you would say:
dim db as database
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("VendorTable")
while not rs.EOF
' get other fields here
' generate your email here!
set rs = nothing
set db = nothing
Now you'd need an INNER loop where you would loop through each of the invoices for that vendor (rs2) and create your text or html-based invoice. You would, of course, also need the code to send the email.
I just finished recording a whole series (about 8 hours) of tutorials on recordset development (starting with Access 320) that will be available on my web site later this week. Plus I have a free tutorial that I'll have up in my Tips & Tricks section that gives a better example of recordsets.
I am sorry but I beleive my lack of knowledge is throwing into a loop of my own.
Do I need to create a seperate module with your code suggestions and do everything in there or do I create the report and manipulate the data through the control source or do I am I manipulating a query that becomes the data source for a report?
Just make an unbound form. Create your recordset loops. In the loop, build text or HTML file that contains your email attachment (or drop all the records into a Temp table), then send the email there. You don't NEED to use an actual Access Report unless you want to. I assume you were just sending text-based emails out via Outlook.