E-mail Access Report as message, not attachment

I have a simple text report in MS Access, and want to click a button and send it in the body of the e-mail message (not as an attachment).  I don't care about the format...it can be HTML or TXT.

The e-mail field is called ParentEMail
Who is Participating?
I spent a considerable amount of time on this last year so heres my (somewhat) polished solution. Create a new button on an existing or new form. For its on click event add the following:

Dim fso As FileSystemObject
Dim msgbody As TextStream
Dim body As String
Dim outl As Object
Dim outlmail As Object

Set outl = CreateObject("Outlook.application")
Set fso = New FileSystemObject
    Set outlmail = outl.createitem(olmailitem)
    ' Output reminder_email report to text file
    DoCmd.OutputTo acOutputReport, "PUT YOUR REPORT NAME HERE", acFormatTXT, "c:\temp\email.txt", False
    ' Open the text file with the filesystemobject and read its contents
    Set msgbody = fso.OpenTextFile("c:\temp\email.txt", ForReading, False, TristateUseDefault)
    body = msgbody.ReadAll
    ' Close the text file when finished
    With outlmail
        .Subject = "SUBJECT"
        .body = body
    End With

You will need to add a reference to "MS Scripting runtime" for this to work. To do this you open the visual basic window and click tools>references. Then make sure theres a check against the above reference.

If you want to get the recipient email from the database create a subform based on the same query as your report on the same form as the new button. You would then replace  .To ="PUT RECIPIENT HERE" with .To = forms![yourformname]![yoursubformname]![parentemail]

The code works by outputting your access report to a .txt file and then reading it back in as the body of a new email in microsoft outlook. When its done it will open display the email ready to be sent.

P.S I know im using late binding which runs slightly slower than early binding but its the best way to make sure it works in all office versions.

Hope this helps

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd have to "build" the report in VBA, then use that as the Message Body. How you'd do that depends on many things, but basically you open a recordset using the same SQL as you do to open your report, then build your message body from there. If you have several different items in the Report Body (i.e. you have multiple Detail sections in the report), then you'd need to loop through the recordset and build the body:

Dim sBody as string
Dim rst AS DAO.Recordset
Set rst = Currentdb.OpenRecordset("Same SQL As Your Report")

Do Until rst.EOF
  sBody = sBody & rst("SomeField") & vbTab & rst("SomeOTherfield") & vbcCrLf

DoCmd.SendObject acSendNoObject, "", "", "someone@somedomain.com", , , "Subject", sBody
Lawrene_FranceAuthor Commented:
I don't have multiple "detail sections"...just one.  

"Dear <first_last>,
Blah, blah, blah...

I am pretty new at this...
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If all you're really doing is emailing text, then LSM's VBA approach is the correct answer.

afaik you cannot paste the output of an Access report into the body of an email (i.e. not as an attachment).  
Even if you could, the cosmetic formatting would be messed up.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.