Create mutiple mails w/ .xls attachments from one spreadsheet

sapper

Hi

I have a spreadsheet (exported from MS Access) which contains the following info in each column;

Column A: Client Name
Column B: Client Code
Column C: Client E-mail address
Column D-Z: Information for clients

I want to be able to create an e-mail for each client with an attachment clientcode.xls containing all the info in all the rows containing that client code, sent to the client e-mail address mentioned.

Can anyone out there point me to a macro that might to this? I've found one that can e-mail a list of existing files to a list of corresponding e-mail addresses,so I guess I just need a macro that can split out the rows by clientcode and create a new temporary file for each called clientcode.xls.....

Any help much appreciated!

FSL

I wouldn't bother with the extract. I'd read a recordset containing the distinct client name,code and email direct from the Access Database. Loop through it creating the clientnumber.xls and filling it from a second recordset containing the rest of the client specific data, again read directly from the Access database. Save it, email it and move on to the next client. You have a macro which creates an email with an attachment and sends it. There are examples in Access & Excel help showing how to open a connection and read a recordset, and how to create, fill and save a workbook.

CptSternn

Google the term MAIL MERGE.

cyberbob

FSL wrote: »

I wouldn't bother with the extract. I'd read a recordset containing the distinct client name,code and email direct from the Access Database. Loop through it creating the clientnumber.xls and filling it from a second recordset containing the rest of the client specific data, again read directly from the Access database. Save it, email it and move on to the next client. You have a macro which creates an email with an attachment and sends it. There are examples in Access & Excel help showing how to open a connection and read a recordset, and how to create, fill and save a workbook.

+ 1 !


I did something similar-ish to this at work a while ago. It was interesting to learn some of the capabilities of VB... I did have to have the security settings around email tweaked a slight bit to allow Access to send the mail. Worked quite well in the end tho.

AccessQuery

Hi,
I currently send e-mails directly from Access. In my case I build multi line XLS files per client and send them as attachments via MS-Outlook.

The attached .TXT file contains the two functions

1) Function ExtractXLSData() - This builds the XLS files per client and saves the data as <Client Code>.xls in a pre-defined directory.

2) Private Sub cmdSendEMail_Click() - This generates an e-mail per client and attaches the relevant .XLS file and sends to MS-Outlook.

E-mails are only sent when Outlook is open and a send/receive is issued. While testing use your own e-mail address!!

It'll save you all that needless cutting, pasting and e-mailing.

Feel free to get back to me with any queries.