• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1362
  • Last Modified:

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

Hello,

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
0
CafeTica
Asked:
CafeTica
  • 5
  • 2
2 Solutions
 
Berkson WeinTech FreelancerCommented:

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:
http://www.rondebruin.nl/mail/folder3/mail4.htm

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.
0
 
Helen FeddemaCommented:
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 Document.dot"
   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
            .Selection.WholeStory
            .Selection.Fields.Update
            .Selection.Fields.Unlink
         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
         .MoveNext
      Loop
   End With

   MsgBox lngCount & " documents created and ready to send"
   
ErrorHandlerExit:
   Set pappWord = Nothing
   Exit Sub

ErrorHandler:
   If Err = 429 Then
      'Word is not running; open Word with CreateObject
         Set pappWord = CreateObject("Word.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Open in new window

0
 
Helen FeddemaCommented:
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.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
CafeTicaAuthor Commented:
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.
0
 
Helen FeddemaCommented:
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.
0
 
Helen FeddemaCommented:
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.
0
 
Helen FeddemaCommented:
If you post your code, I could modify it to use the Envelope object to send the RTF Word doc.
0
 
CafeTicaAuthor Commented:
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?

Thanks
0
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now