E-mail Access Report as message, not attachment

Posted on 2007-07-24
Last Modified: 2013-11-28
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 can be HTML or TXT.

The e-mail field is called ParentEMail
Question by:Lawrene_France
    LVL 84
    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, "", "", "", , , "Subject", sBody

    Author Comment

    I don't have multiple "detail sections"...just one.  

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

    I am pretty new at this...
    LVL 65

    Expert Comment

    by:Jim Horn
    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.

    LVL 2

    Accepted Solution

    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
            .To ="PUT RECIPIENT HERE"
            .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


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now