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

SendObject only as an attachment-need it in email body

I am using Access 2003 and Outlook 2010 (Cloud).  I am trying to use SendObject to write Query data results into the body of an email - not as an attachment.

Is this possible?

Thanks!
Nancy
0
NEC293
Asked:
NEC293
  • 3
  • 2
1 Solution
 
omgangCommented:
It should be possible.  You'll need to construct the message body as a string based upon the query result set.  Something like this

Dim rs As DAO.Recordset
Dim strBody As String

Set rs = CurrentDb.OpenRecordset ("MyQuery")

strBody = "Good Day.  Here's the daily report from the database:  " & vbCrLf & vbCrLf

rs.MoveFirst
Do Until rs.EOF
    strBody = strBody & rs(0) & ", " & rs(1) & ", " & rs(2) & ", " & etc. etc. & vbCrLf
    rs.MoveNext
Loop

strBody = strBody & vbCrLf & "That's it!"

    'send the report as an email msg
    'DoCmd.SendObject acSendNoObject is default, ObjectName, OutputFormat, To, CC, BCC, Subject, Body
DoCmd.SendObject , , , "TheRecipient@MyDomain.com", "ccAddress@Mydomain.com", , "Daily Report", strBody

    'destroy object variable
Set rs = Nothing



OM Gang
0
 
NEC293Author Commented:
I'm rather a novice at this ... the query I am running uses a date range entered by the user.
Does that make a difference in using this code?
0
 
omgangCommented:
Yes.  You need to explicitly declare the parameters for the parameter query when opening a DAO recordset upon it.

How are the users entering the date range?  Prompt from the query itself or via fields/controls on a form?

OM Gang
0
 
NEC293Author Commented:
Prompt from the query itself.
0
 
omgangCommented:
OK.  I'll work off the assumption your query has a criteria expression in it something like this
WHERE (((data.dDate) Between [Enter Start] And [Enter End]));

The bracketed values [Enter Start] & [Enter End] are called parameters.  We need to explicitly supply those when opening a recordset on the query object

So we make the following changes

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim dStart As Date, dEnd As Date
Dim strBody As String

    'here we prompt the user to inpute the start and end date values.
dStart = CDate(InputBox("Enter start date"))
dEnd = CDate(InputBox("Enter end date"))

Set qdf = CurrentDb.QueryDefs("MyQuery")
qdf.Parameters("[Enter Start]" = dStart
qdf.Parameters("Enter End]") = dEnd
Set rs = qdf.OpenRecordset ("MyQuery")

strBody = "Good Day.  Here's the daily report from the database:  " & vbCrLf & vbCrLf

rs.MoveFirst
Do Until rs.EOF
    strBody = strBody & rs(0) & ", " & rs(1) & ", " & rs(2) & ", " & etc. etc. & vbCrLf
    rs.MoveNext
Loop

strBody = strBody & vbCrLf & "That's it!"

    'send the report as an email msg
    'DoCmd.SendObject acSendNoObject is default, ObjectName, OutputFormat, To, CC, BCC, Subject, Body
DoCmd.SendObject , , , "TheRecipient@MyDomain.com", "ccAddress@Mydomain.com", , "Daily Report", strBody

    'destroy object variables
Set rs = Nothing
Set qdf = Nothing



OM Gang
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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