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

Include query result in body of email

I need help to modify my email code to include the results of a query in the body of the email and not as an attachment.

After researching many methods, I find myself totally confused.  Could some one please assist me in modifying my existing code to include the query results.

How do I modify the SendObject string to include the query results in the body,  or what other method could be used?

Thanks,

Karen
Function SendEmail()
   
   On Error GoTo SendEmail_Error
 
    RightNow = Time()
    
    fHandleFile ClickYes_Location
    ' Give the fHandle function a little time to actually open "ClickYes"
    Do Until Time > DateAdd("s", 2, RightNow)
        Sleep 5000
    Loop
    'Opens recordset, reads all the emails note this query reads the values from the field called EmailAddr, table tblEmails
    Set curdb = CurrentDb
    strSQL1 = "Select EMail_Address from [" & gQName & "] GROUP BY EMail_Address"
    Set rs = curdb.OpenRecordset(strSQL1)
    'Goes through the list of records returned by the above call
    MyString = vbNullString
    Do While Not rs.EOF
        MyString = MyString & rs!EMail_Address
        MyString = MyString & ";"
        rs.MoveNext
    Loop
        gBody = "Please review the attached document for a list of Assigned Emergent Work Tasks."
        gQName1 = "Select Seq_No, Tech_Grp_Person, Task_Description, Status, Status_Date, InScope, Management_Action, MgmtReviewDate" & _
                " From [" & gQName & "]"
                Debug.Print gQName1
    Resume_ClickYES
        If x <> 1 Then
            If rs.RecordCount > 0 Then
                DoCmd.SendObject acSendQuery, gQName1, acFormatHTML, MyString, , , gSubject, gBody & vbCrLf & strLink, False
            Else
                Call MsgBox("NO RECORDS FOUND.", vbCritical, "The current & gQname &  does not contain any records!!!!!")
            End If
        Else
            If rs.RecordCount > 0 Then
                DoCmd.SendObject acSendQuery, gQName1, acFormatHTML, strVI_TechGrp_Mgr & ";" & strVI_TechGrp_Lead & ";" & MyString, , , gSubject, gBody & vbCrLf & strLink, False
            Else
                Call MsgBox("NO RECORDS FOUND.", vbCritical, "The current & gQname &  does not contain any records!!!!!")
            End If
        End If
    Suspend_ClickYES
    If QueryExists(gQName) = True Then
         DoCmd.DeleteObject acQuery, gQName
    End If
'Close and clear all
rs.Close
Set rs = Nothing
Set curdb = Nothing
 
   On Error GoTo 0
   Exit Function
 
SendEmail_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendEmail of Module Email"
End Function

Open in new window

0
Karen Schaefer
Asked:
Karen Schaefer
  • 3
  • 3
1 Solution
 
jmoss111Commented:
Hi karen,

See the attached code snippet

Jim
strBody = strBody & vbCrLf & "  Count     Type    Date" & vbCrLf
            strBody = strBody & "  ======    =====   ====================" & vbCrLf
            Dim i As Integer
 
 
            Set rs = CurrentDb.OpenRecordset("qrsFormattedLoadStats")
            Do While rs.EOF = False
            'Print contents of row into one line
                    'For i = 0 To rs.Fields.Count - 1
                    strBody = strBody & "  " & Format(rs!RowsCount, 0) & vbTab & rs!App & vbTab & "  " & rs!DateLoaded
                    'Next i
                    strBody = strBody & vbCrLf
                rs.MoveNext
            Loop
            rs.Close
            Set rs = Nothing

Open in new window

0
 
Karen SchaeferAuthor Commented:
Thanks for the quick response - still confused on where do I incorporate this code:

Note gQname is variable passed by the form depending on the option selected by the user?

How do I modify it for the following query string>

       gQName1 = "Select Seq_No, Tech_Grp_Person, Task_Description, Status, Status_Date, InScope, Management_Action, MgmtReviewDate" & _
                " From [" & gQName & "]"
 
0
 
Karen SchaeferAuthor Commented:
Do I still use the sendObject line of code to actual send email?

K
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jmoss111Commented:
All of the recordset logic (I loop thru a recordset in the sample) and body layout is going to have to be incorporated into building gBody.

I don't use SendObject or Outlook but I guess that you should be able to.
0
 
Karen SchaeferAuthor Commented:
decided to abandon this approach and went back to an attachment - the formatting was messy and unreadable.

 Thanks for the input.
0
 
jmoss111Commented:
Karen,

The formatting isn't easy, especially if you have too many columns to display. I basically use this approach for load status statistics notifications and the like when I only have two  to four columns of data. Too much info and it gets ugly quick.

Have a great day!

Jim
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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