jc*****@celestica.com (Jen) wrote in message news:<93**************************@posting.google. com>...
Trying to take one table in access and split it into multiple excel
files(using an excel template); and then email based on email
addresses in Table2; Of course, I would like to do all of this with
minimum user-interface...If there is anyone out there that can help...
please feel free to share!:)
PS... Using MSOffice 2000
Example:
Table1
Record# Vendor Field1 Field2 Field3 Field4 Field5
1 12345 450000 $1.00 12 $12.00 10-Nov-2004
2 24689 50000 $3.00 1 $ 3.00 10-Dec-2004
3 12345 808500 $5.00 5 $25.00 10-Dec-2004
4 12345 450000 $1.00 12 $12.00 10-Nov-2004
.
.
n 56798 12000 $10.00 1 $10.00 15-Mar-2005
Result I want in an Excel spreadsheets:
Spreadsheet 1 File name: Vendor_12345_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
12345 450000 $1.00 12 $12.00 10-Nov-2004
12345 808500 $5.00 5 $25.00 10-Dec-2004
12345 450000 $1.00 12 $12.00 10-Nov-2004
Spreadsheet2 File name: Vendor_24689_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
24689 50000 $3.00 1 $ 3.00 10-Dec-2004
Spreadsheet3 File name: Vendor_56798_22-Oct-2004.xls
Vendor Field1 Field2 Field3 Field4 Field5
56798 12000 $10.00 1 $10.00 15-Mar-2005
Table2
Vendor# Contact Email
12345 Smith, Joe jo******@xxxx.com
24689 Doe, Jane ja*****@xxxx.com
56798 Help, Me he****@xxxx.com
Start by reading this article:
http://www.mvps.org/access/modules/mdl0035.htm
This is ONE of several ways to put your data into Excel. If you want
others, I'm sure other people have different methods. I'm sure Rich
P, who usually hangs out in the SQL Server NG will have some good
ideas. He generally uses VBA, but it gives you a LOT more control
over what's going on - like filenames etc. But in your case it looks
like VendorName & "_" & SomeID & Day(Date)..., in other words VERY
easy to automate.
if you wanted to output the files only to e-mail them, you could use
SendObject, but it does have its limitations.
So you want to do something like (Anybody feel free to correct/augment
whatever):
1. open a recordset based on a SQL statement on your addresses table,
call ir rs Recipients.
2. use that as criteria for another recordset, say rsData.
3. use the data from rsRecipients to filter rsData. If you use ADO,
you can pass the recordset stuff directly to an Excel file/template.
(This is where RichP could take over...)
Okay, so now you have your data from Access and into Excel. (I'll
assume that anyway.)
So now all you have to do is automate your e-mail. Dmitri Furman has
code that automates GroupWise... best bet, check Tony Toew's page on
how to do all this good stuff. Can't remember his URL off the top of
my head, but he has zillions of postings here, and it's in his sig.
Either that or check the MVPs section of AccessWeb (open the url I
gave you earlier).
You basically automate your favorite e-mail client and then create a
new message, insert Recipient, Subject, BodyText, and Attachment info,
and Send. If you don't like mucking with the Outlook object model, at
least there used to be a library available from MS, called CDONTS,
which is a wrapper class that simplifies automating Outlook. Don't
know what it's morphed into now, but that greatly simplifies things.
You just pass the information into this wrapper class and it takes
care of the automation internally. Or you could use BLAT. Ask Steve
Jorgensen about that, as I know he uses that.
Hope that gives you some options. If I were you, I would search the
NG for the names etc I gave you. I know for sure all the answers are
here, because I've read them over the years. All you have to do is
find them. Happy Hunting!
HTH,
Pieter
