Send Access report in body of Outlook email

[ryaanmichael]

Is there a way to insert data/information from a report into the body of an Outlook email message?

I want the VBA code to pull the data from my report, input it into the body of my email message, and then send, without using any attachments.

Thanks for any assistance!

[twinnyfo]

ryaan,

There are several ways to do this, but hte best is to use a recordset to pull the specific data from your database, then build the Body Text of your e-mail and then send it, using the SendObject method. There are other ways, also, but just depends on your preferences.

What have you tried so far?

[ryaanmichael]

Hi Twinnyfo,

I've tried the recordset, but I can't get it to work. I've got a report called "TripCSA" that contains all the data I want to include. I want to take all that data and put it in my Outlook email body.

Thanks in advance,
Ryan

[twinnyfo]

You will need to provide examples of your report and queries underneath that report. What about the recordset could you not get to work?

You will need to provide moreinformaiton about your database and your desired result before any of us can even begin to assist you with this. Depending on your report, this could be rather simple, or rather complex.... But since we don't even know what your report looks like, or the data behind it, I can't estimate that.

[ryaanmichael]

Hi Twinnyfo,

My report data is gathered from a query called "qryCurrentTrip". All the information for the query is pulled from my table "tblCurrentTrip". The query ("qryCurrentTrip") filters all the data in the "Name" field which is equal to "th98". The report shows only records with "th98" in the "Name" field (usually only about 5-10 records). The report contains 5 fields: "tripNumber", "Name", "City", "State", and "Date". As of now, every time I need to send out this report, I just copy the report data and paste it in my email. But I know there has to be a way to click a "send" button and an email will generate with my report data in the body.

Thanks,
Ryan

[twinnyfo]

What have you tried so far and what is not working about it? This should be relatively straightforward, since you neither have many rows or collumns, but the approach will always be the same.

Create a recordset in your code that pulls the data from qryCurrentTrip, and count how many records your have.

To build the e-mail body, create your headers for each field, then cycle through your records one at a time, inserting tabs between the fields for formatting purposes.

Then all you need is to designate the e-mail address(es) and subject line and you should be good to go. You will have to play with the formatting until it suits your liking, but that is an outline for how you would do it. I'll be glad to help if you come across snags, but I won't write the code for you...

[zmbd]

ryaanmichael
Asking for full code is against site rules. However, as twinnyfo has started the quest and it's your first post... (read the FAQ) in the future the thread might be deleted.

Of Major importance... what is your email client?
If Outlook, then there are built in methods. (open access, press [F1] and search the help file for send object)
If others then you may need to use the SMTP methods.

If you are already knee-deep in code, please post it (remember to format it using the <CODE/> button) along with an explanation of any errors and what is or is not working.

In either case, there are tons of thread here in BYTES that address this very question and have some very useful code (in-fact, I just changed one of my codes to use part of one I found here! :) ) A quick use of the search will turn up the threads for you.

[ryaanmichael]

Hi Twinnyfo,

Thank you so much for your continued assistance. I'm not understanding this recordset thing. At first it seemed like a really simple task. Anyways, here is my non-working code:

Private Sub sentRPT_Click()
 
Dim openTrip As Recordset
' "rptOpenTrip" references the Report I want to include in my Outlook email
' My report gathers data from my query "qryCurrentTrip" when the values in "Name" field = "th98"
Set openTrip = CurrentDb.OpenRecordset("rptOpenTrip")
 
'***********************************
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
'Components of Outlook Email
With objEmail
    .To = "TO ADDRESS"
    .Subject = "Subject Test"
    .HTMLBody = openTrip
    .Display
End With
'***********************************
End Sub

[twinnyfo]

Ryaan,

Although you declared a recordset, you must also first declare the database from which the recordset will get its information. Also, when you tried to set your recordset, you simply used the report name, which is an invalid means of establishing that recordset.

If you look below, you will see that I declare my variables, and in order to create the recordset, I must write a short SQL query that will pull data from the database (line 12) using your query as the source.

I begin bulding the E-mail content in line 15, in which you may add whatever text you want. The vbCrLf's are line feeds, so you don't have one huge block of text. You will have to play with the spacing of lines 16-17 to enable them to match up with your fields.

In lines 19-25, we are putting the content of your report (not the report itself, but the data behind it) into the e-mail body. The "Chr(9)" is to insert a [Tab] character to align the columns. In between lines 26 and 27 you could also add some more text, like your signature block or contact information.

I have always preferred to use the DoCmd.SendObject Method, because it's very easy to use (even though we are not sending an object). I know there are some other featurees of creating an Outlook Object which may be helpful, but in this particular case, it does not seem necessary to create those objects.

I hope this helps to get you closer to your goal.....

Option Compare Database
Option Explicit
 
Private Sub sentRPT_Click()
On Error GoTo EH
    'Recordset variables
    Dim db As Database
    Dim rstOpenTrip As Recordset
    Dim strSQL As String
    Dim strSubject, strBody, strAddresses As String
    Set db = CurrentDb()
    strSQL = "SELECT * FROM qryCurrentTrip;"
    Set rstOpenTrip = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rstOpenTrip.EOF Then
        strBody = "blah blah blah" & vbCrLf & vbCrLf
        strBody = strBody & "NAME     Field2    Field3 ....etc." & vbCrLf
        strBody = strBody & "==================================" & vbCrLf
        rstOpenTrip.MoveFirst
        Do While Not rstOpenTrip.EOF
            strBody = strBody & rstOpenTrip("Name") & Chr(9) & _
                rstOpenTrip("Field2") & Chr(9) & _
                rstOpenTrip("Field3") & Chr(9) & _
                rstOpenTrip("etc.") & vbCrLf
            rstOpenTrip.MoveNext
        Loop
    End If
    strSubject = "Here's my report!"
    strAddresses = "[e-mail]"
    DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
End Sub

[ryaanmichael]

Thank you so much for your time! I am going to try this out and let you know how it goes.

Thanks again,
Ryan

[ryaanmichael]

Twinnyfo,

Just wanted to say thank you and let you know the code works perfectly! Thanks again for your help!

[twinnyfo]

Ryaan,

My pleasure. Glad I could help!