Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Include query result in body of email

Posted on 2008-10-14
6
Medium Priority
?
514 Views
Last Modified: 2013-11-27
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
Comment
Question by:Karen Schaefer
  • 3
  • 3
6 Comments
 
LVL 18

Accepted Solution

by:
jmoss111 earned 2000 total points
ID: 22713561
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
 

Author Comment

by:Karen Schaefer
ID: 22713601
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
 

Author Comment

by:Karen Schaefer
ID: 22713717
Do I still use the sendObject line of code to actual send email?

K
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 18

Expert Comment

by:jmoss111
ID: 22714137
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
 

Author Closing Comment

by:Karen Schaefer
ID: 31505997
decided to abandon this approach and went back to an attachment - the formatting was messy and unreadable.

 Thanks for the input.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22741806
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

Industry Leaders: 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!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

886 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