Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Wellington, North Island, New Zealand
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email report data (Access 2000)

    We use a run-time stand alone Access DB as a case management tool. A copy is in use by about 100 users geographically dispersed each with their own data. Report data is extracted each month in an Excel file format. The report data is cumulative and is automatically extracted, zipped up and emailed to HO at the click of a button. The extracted data is collated at head office and imported into a central Access DB. It was especially written this way to be simple and straightforward to use as users are not technical or IT literate.

    The problem is that the report data for some users is getting very large (4-5MB) and emailing the zipped file is becoming difficult. Also the Excel file format has a 255 characters limit which is problematic as the database has many free text fields.

    Does anyone know of any ways to build in a function to Access that will extract the report data, zip it up, and present it in such a way that it is easily emailed, but in a format that is space efficient?

    Thanks,
    JOTO

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Email report data (Access 2000)

    Is it really necessary to send cumulative data all the time? Whatever method you choose, cumulative data will get larger and larger by definition.

    Exporting to text file results in smaller files than exporting to Excel. In a small test I did, a zipped text export was less than half the size of a zipped Excel export. But a noted, this gain will not help against cumulative data.

    BTW, when I export to Excel, I don't see a 255 character limit. Do you have formatting on memo fields? That is known to truncate them.

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Wellington, North Island, New Zealand
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email report data (Access 2000)

    Hi Hans,

    We have been looking at the issue of the export truncating the data to 255 characters.

    The code that is in use for exporting and sending the data is similar to:

    DoCmd.SendObject acSendTable, "tblDataTestMemo", acFormatXLS, "abc@xyz.co.nz", , , "Test XLS output", "Test XLS output"

    If we change the format to text

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Email report data (Access 2000)

    If you're e-mailing with Outlook, you can use DoCmd.TransferSpreadsheet to export the table to an Excel workbook on disk; this will not truncate the memo fields, and use Automation to control Outlook in code. You can create an e-mail, add attachments etc. See How to use Automation to send a Microsoft Outlook message using Access 2000.

    If you have the latest service pack for Office 2000, you'll want to download and install the free utility Express ClickYes. This will suppress the annoying warning "A program is trying to send an e-mail on your behalf...".

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Wellington, North Island, New Zealand
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email report data (Access 2000)

    Hi Hans,

    Thanks for all your help, that seems to be working OK.

    The only issue we now have is that some of our users have Outlook Express rather than Outlook. Is there an equivalent to "Microsoft Outlook 9.0 Object Library" that we could use for Outlook Express or one that covers both.

    Many thanks, we appreciate your time and effort.

    JOTO

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Email report data (Access 2000)

    Outlook Express has no object model exposed to the programmer. Sorry.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •