Solved

Include query result in body of email

Posted on 2008-10-14
6
433 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now