Automate: insert Access Report (RichText) into Body of Outlook email.

Posted on 2011-10-19
Last Modified: 2012-06-27

I am trying to automate the process of inserting an Access Report (Rich Text) into the body of an Outlook email. Presently, I use an Access macro to create the report as a rich text file and then open the file in Word. Then I copy and paste the rich text from the Word file into the body of my Outlook email. (using OutputTo Macro)

I tried to do automate the process using a SendObject Macro (or VBA Method), however, it only allows me to attach the report as a file rather than insert the rich text report into the body of the email.

How do I automate the insertion of an Access Report into the body of an Outlook email from Access?

Thank you
Question by:CafeTica
    LVL 15

    Assisted Solution


    Have you considered sending an HTML email instead?  Unfortunately the SendObject command will only attach the file as you're seeing.  To put the data right in the email, you'll need to do some hand coding.

    Here's an example:

    That's from excel, but the same concept holds from Access.  Just get the data you want from a query and then format as html.

    Hope this helps.
    LVL 31

    Accepted Solution

    From Word, you can send a document using the Envelope object (this preserves formatting).  Here is some Access VBA code using the Envelope object to create and send a Word doc programmatically:
    Public Sub CreateWordDocs()
    'Created by Helen Feddema 7-16-2004
    'Last modified 7-17-2004
    On Error GoTo ErrorHandler
       Dim doc As Word.Document
       Set pappWord = GetObject(, "Word.Application")
       'Get user templates path from Word options dialog
       strTemplatePath = pappWord.Options.DefaultFilePath(wdUserTemplatesPath) & "\"
       Debug.Print "Templates folder: " & strTemplatePath
       strTemplate = strTemplatePath & "Test"
       Debug.Print "Template: " & strTemplate
       'Check for existence of template in template folder,
       'and exit if not found
       strTestFile = Nz(Dir(strTemplate))
       Debug.Print "Test file: " & strTestFile
       If strTestFile = "" Then
          MsgBox strTemplate & " template not found; can't create documents"
          GoTo ErrorHandlerExit
       End If
       'Check that at least one contact has been checked for sending an email
       strQuery = "qrySendWordDocs"
       strSQL = "SELECT * FROM tblContacts WHERE [SendDoc] = True And " _
          & "Nz([EmailName]) <> ''"
       Debug.Print "SQL for " & strQuery & ": " & strSQL
       lngCount = CreateAndTestQuery(strQuery, strSQL)
       Debug.Print "No. of items found: " & lngCount
       If lngCount = 0 Then
          MsgBox "No contacts selected for sending documents; canceling"
          GoTo ErrorHandlerExit
       End If
       Set dbs = CurrentDb
       Set rst = dbs.OpenRecordset(strQuery)
       With rst
          Do While Not .EOF
             'Create a new document based on the selected template
             Set doc = pappWord.Documents.add(strTemplate)
             'Write information to Word custom document properties
             Set prps = doc.CustomDocumentProperties
             strName = Trim(Nz(![FirstName]) & " " & Nz(![LastName]))
             prps.Item("Name").Value = strName
             prps.Item("Street").Value = Nz(![StreetAddress])
             prps.Item("City").Value = Nz(![City])
             prps.Item("State").Value = Nz(![StateOrProvince])
             prps.Item("Zip").Value = Nz(![PostalCode])
             strEMail = Nz(![EmailName])
             'Update fields in Word document
             With pappWord
             End With
             'Set envelope properties of open Word doc
             pappWord.ActiveWindow.EnvelopeVisible = True
             With doc.MailEnvelope
                .Introduction = "Here is the document you requested"
                With .Item
                   .To = strEMail
                   .Subject = "Document for " & strName
                End With
             End With
       End With
       MsgBox lngCount & " documents created and ready to send"
       Set pappWord = Nothing
       Exit Sub
       If Err = 429 Then
          'Word is not running; open Word with CreateObject
             Set pappWord = CreateObject("Word.Application")
          Resume Next
          MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
          Resume ErrorHandlerExit
       End If
    End Sub

    Open in new window

    LVL 31

    Expert Comment

    You can do a test first by sending the Word doc created from the RTF report manually, by selecting Send as Email from the Save & Send option in Word 2010, or File, Send To, Mail Recipient in Word 2003.  This will make the Envelope pane appear, where the email address is entered for sending the document.

    Author Comment

    Thanks for the suggestion. I want to avoid complicated coding to keep things as easy as possible for anyone else who wants to make changes to the database.

    Intuitively, it seems strange that there isn't an easier solution. It's as close as cutting and pasting. IOW Access already took me 99.99999% of the way there. It can generate a Word file with the exact right formatting (OutputTo) and it can generate an email (SendObject) with everything I want except placing the report into the body of the email.

    Basically, it's "that" close and I'd hate to go a complicated coding route to do just one last thing.
    LVL 31

    Expert Comment

    You would need to write code to save the report in RTF format, then open it in Word, then use code from my code sample to open the Envelope pane and fill in the subject and email address.
    LVL 31

    Expert Comment

    Actually, the coding that opens and fills the Envelope pane is pretty simple -- my code sample does a lot more, but you may only need a small portion of it.
    LVL 31

    Expert Comment

    If you post your code, I could modify it to use the Envelope object to send the RTF Word doc.

    Author Comment

    I've gotten a lot of good suggestions here that I won't be using. The answers helped me to understand the level of complexity necessary to complete the task and make the decision to NOT pursue automation. Consequently, what is the proper way to distribute the points/accepted answers?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Sorry for my English and Italian screenshots..... Problem: Every Year the number of E-Mails grow dramatically and the space that they take on Exchange Servers or .PST files, grow much more faster, once it was only text, now our E-Mail Archives …
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    759 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

    12 Experts available now in Live!

    Get 1:1 Help Now