Send Access report in Outlook Message Body.

Posted on 2006-05-05
Last Modified: 2012-06-27

Hello, I am sending an emai message from Access 2003 with an html attachment. (Its a report that was just exported to html).  I would like to include the (report) in the body of the message. Not just an attachment.  it could be rtf, htm or whatever, just as long as the report in inthe body.  The report is not very long. Any help would be greatly appreciated.  Thanks!

  Dim objOutlook As Outlook.Application
  Dim objOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim objOutlookAttach As Outlook.Attachment

  objOutlookAttach = C:\myfile.htm

    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Set objOutlookRecip = objOutlookMsg.Recipients.Add(TheAddress)
    objOutlookRecip.Type = olTo

    objOutlookMsg.Subject = (Subject)
    objOutlookMsg.BodyFormat = olFormatHTML
    objOutlookMsg.Body = (MessageBody)   '  Here I want to add the contents of myfile.htm  
    objOutlookMsg.Attachments = AttachmentPath
Question by:sourcelink
    LVL 11

    Expert Comment

    Can you reference the text from a field in your form?

     objOutlookMsg.Body = Forms!FormName!TextBoxName
    LVL 8

    Accepted Solution

    Use the recordsource behind your report.

    Loop through it in your e-mail code and you can write whatever fields out that you want. You may have to play with the way it formats in the message. What I would do is just load the data into one variable and then make the message equal to that variable.

    So say:

    2005     10     Mickey Mouse
    2006     11     Minnie Mouse

    (Example of report data)

    Then when you get the first record.

    variable = !Year & " " & !Num & " " & !Name & Chr(13) & variable

    Then the second record comes into the same line and it will add a carriage return after each record.

    The loop would be

    Dim rs as recordset
    Dim varStr as string

    varStr = ""
    Set rs = currentdb.openrecordset("recordsourcetable")

    With rs
    Do Until .eof
        varStr = !Year & " " & !Num & " " & !Name & Chr(13) & variable
    End With

    Make varStr = to the message part you send out.

    LVL 44

    Expert Comment

    by:Leigh Purvis
    So is this just an three part process?

    1) Save your report as an html output file.
    2) Open the file and grab the source text.
    3) Insert the source text into your Outlook email object.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Of course Pigster's solution is also viable (you could elaborate on it with a few well chosen html tags to format it when inserted into your email.)
    I did an example of that type of thing here on EE once... damn I can never remember where... umm...
    I'll have a look.
    LVL 44

    Assisted Solution

    by:Leigh Purvis

    Probably just the final suggestion towards the bottom worth having a peek at.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    733 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

    19 Experts available now in Live!

    Get 1:1 Help Now