Solved

Include query result in body of email

Posted on 2008-10-14
6
476 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 500 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Independent Software Vendors: 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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

713 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