2

I have an access report which is modified by a SQL statement so that one report to corresponding recipient is generated. I am using CDO in my VBA code in access 2007. I am interested in taking this report and emailing it. How can I make the HtmlBody have the access report?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Sithelo
  • 307
  • 9
  • 28

1 Answers1

2

Perhaps something like this. The idea is to save the report as HTML or RTF, then read it in:

Const ForReading = 1

DoCmd.OutputTo acOutputReport, "Report1", acFormatHTML, "Report1.htm"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("Report1.htm", ForReading)
sBody = f.ReadAll
f.Close

obj.HTMLBody = sBody
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Its working and its great learning from you. Thanks a million. Where is the report saved? Is it deleted after every loop? – Sithelo Jun 21 '12 at 11:31
  • No, in the above example it will be saved in the default documents folder (My Documents). You can include a path (`"C:\docs\Report1.htm"` ) if you wish. It will be overwritten at every loop. Make sure that you are not picking up a previous version in your email! You will only have a limited set of the formatting options available in MS Access reports when using HTML output, for example, lines may not always be included. – Fionnuala Jun 21 '12 at 11:46