Solved

Include query result in body of email

Posted on 2008-10-14
6
413 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

746 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

11 Experts available now in Live!

Get 1:1 Help Now