[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

E-mail Access Report as message, not attachment

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 format...it can be HTML or TXT.

The e-mail field is called ParentEMail
0
Lawrene_France
Asked:
Lawrene_France
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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")

rst.MoveLast
rst.MoveFirst
Do Until rst.EOF
  sBody = sBody & rst("SomeField") & vbTab & rst("SomeOTherfield") & vbcCrLf
  rst.MoveNext
Loop

DoCmd.SendObject acSendNoObject, "", "", "someone@somedomain.com", , , "Subject", sBody
0
 
Lawrene_FranceAuthor Commented:
I don't have multiple "detail sections"...just one.  

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

I am pretty new at this...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.

0
 
Adam_BarrettCommented:
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
    msgbody.Close
    With outlmail
        .To ="PUT RECIPIENT HERE"
        .Subject = "SUBJECT"
        .body = body
        .display
    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

Adam
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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