Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-19
8
Medium Priority
?
1,270 Views
Last Modified: 2012-06-27
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
Comment
Question by:CafeTica
  • 5
  • 2
8 Comments
 
LVL 15

Assisted Solution

by:Berkson Wein
Berkson Wein earned 400 total points
ID: 36993000

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
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 1600 total points
ID: 36993180
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36993199
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:CafeTica
ID: 36993205
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36993210
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36993217
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36993242
If you post your code, I could modify it to use the Envelope object to send the RTF Word doc.
0
 

Author Comment

by:CafeTica
ID: 36993525
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
There can be many situations demanding the conversion of Outlook OST files to PST format and as such, there is no shortage of automated tools to perform this conversion. However, what makes Stellar OST to PST converter stand above the rest? Let us e…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

810 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