[AccessD] Embedding Multiple Access Reports in an E-mail Body (asHTML)

A.D. Tejpal adtp at airtelmail.in
Fri Mar 30 22:44:34 CDT 2012


Brad,

    In this context, my sample db named Report_SendInMailBody might be of interest. It is in access 2000 file format and is available at Rogers Access Library. Link:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

    When an access report is converted to HTML format, it translates to one file per page. While concatenating the contents of these files, care has been taken to eliminate unwanted hyperlink labels (First Previous Next Last) appearing at the bottom of each page. 

    Display of horizontal lines in final HTML output is accomplished by using line markers (*^*) in the report. This  overcomes a well known limitation otherwise faced in transferring lines from access reports to HTML format. This approach is found to provide more pleasing results as compared to use of labels with string of hyphens as caption. 

    Although the sample db demonstrates incorporation of one report in email body, you could try to adapt it suitably for handling multiple reports - as even for a single report, the amalgamation process covers multiple html files (one per page of report).

    Note (while using the sample db): 
    For faster results and improved alignment, it would be preferable to stick to the default option of  "Report Output To HTML Direct".  The other option "Make HTML After Output To Word" is provided from an academic point of view only.

    For ready reference, a brief description is placed below.

Best wishes,
A.D. Tejpal
------------

Report_SendInMailBody
Explanatory Notes
===========================
    This sample db demonstrates sending an access report by placing it in the body of eMail message. 

    Though sending a report as an attachment, using DoCmd.SendObject command is pretty straightforward, including it in the body of eMail itself, requires some extra measures.

    Word and Outlook objects are used in this approach and should therefore be available on sender's computer.

    Two styles are covered, one deals with a single report at a time, while the other provides for sending multiple reports as selected in a list box. Temporary output files are stored in folder named ReportOutput located in the same folder as the sample db. This folder gets created programmatically, if not already existing.

    An important feature is the display of horizontal lines in final HTML output by using line markers (*^*) in the report. This  overcomes a well known limitation otherwise faced in transferring lines from access reports to HTML format. Even though  labels with string of hyphens as caption, could be used as replacement for lines, the final results in that case are not quite satisfactory.

    When an access report is converted to HTML format, it translates to one file per page. While concatenating the contents of these files, care has been taken to eliminate unwanted hyperlink labels (First Previous Next Last) appearing at the bottom of each page.

    In each case, two alternative modes for transferring the report to mail body are demonstrated:
    (a) Output the report pages to HTML files (one file per page), read these files, consolidate the content and assign the same to HTMLBody property of outlook application's mail item object.
    (b) Output the report to word file, save it in HTML format, read this file and assign the content to HTMLBody property of outlook application's mail item object. This method is slower than (a) above, mainly on account of the time taken in opening the word application. Moreover, the text alignment and rendering of horizontal lines (through line markers) is not completely  satisfactory. This method is given from an academic point of view only.

    Note: Each time Outlook is used for sending messages programmatically, the user is required to clear a warning message (informing that some program is trying to send a message through Outlook). If manual intervention in this regard is to be circumvented, third party utility like ClickYes could be used. 

Reference required: 
    (a) Outlook Object Library
    (b) Word Object Library
    (c) Microsoft Scripting RunTime

Version: - Access 2000 file format
=======================

  ----- Original Message ----- 
  From: Brad Marks 
  To: Access Developers discussion and problem solving 
  Sent: Saturday, March 31, 2012 00:06
  Subject: [AccessD] Embedding Multiple Access Reports in an E-mail Body (asHTML)


  All,

  I have an automated Access report job that runs at a specific time every
  night and automatically e-mails multiple Access reports in PDF format as
  e-mail attachments.  This has worked nicely for a long time. 

  Now there is a need to "embed" the reports in the body of the e-mail
  rather than send them as attachments.

  I know that I can export the multiple reports from Access into HTML
  files.  This would probably work Okay if there was only one report.

  I have experimented with building the HTML "by hand" via VBA code.  This
  may work, but it takes some time to do the VBA coding and I am not 100%
  sure that I can write the code to generate the HTML for summary reports
  that have a lot of sub-totals, etc.

  It would be nice if a person could export an Access report to a jpg
  file.  Multiple jpg files could then be embedded in the HTML file that
  is embedded in the e-mail body.

  Anyway, I am curious if anyone else has ever run into this type of
  situation.

  Thanks,
  Brad


More information about the AccessD mailing list