Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Create mutiple mails w/ .xls attachments from one spreadsheet

Options
  • 27-03-2008 11:12pm
    #1
    sapper
    Registered Users Posts: 423 ✭✭


    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!


Comments

  • Options
    #2
    FSL
    Registered Users Posts: 1,456 ✭✭✭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.


  • Options
    #3
    CptSternn
    Registered Users Posts: 1,530 ✭✭✭CptSternn


    Google the term MAIL MERGE.


  • Options
    #4
    cyberbob
    Closed Accounts Posts: 82 ✭✭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.


  • Options
    #5
    AccessQuery
    Closed Accounts Posts: 81 ✭✭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.


Advertisement