Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Include query result in body of email

Posted on 2008-10-14
6
Medium Priority
?
508 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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