Emailing Query Results into Body from MS Access 2003

Hi,

I have an MS Access 2003 database, when I open a form I have 2 buttons, when clicking on the buttons I wanted it to generate and email that goes to a specific email address, sets the subject, etc.  

Then I also need to included a tabled version of the information contained on the form in the BODY of the email.

For Example:-
Result 1 label       |     Result 1 Value
Result 2 label       |     Result 2 Value
Result 3 label       |     Result 3 Value
Result 4 label       |     Result 4 Value

If you need any further information please let me know.

Thanks in advance.
Chris_SizerAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
In order for the data to appear in the email body as you have specified, you need to create a simple Report that has that same layout (Columnar).

Again, you must study this sample file carefully, then adapt it for your specific situation


JeffCoachman
Access-EEQ-26626502-OutlookEmail.mdb
0
 
conagramanCommented:
i have attatched a sample database to look at
how you do it is put the following code into a button click event

'needs reference to Microsoft outlook 12.0 object library
'needs reference to Microsoft office 12.0 access databse ingine object
'-----------------start of code
Dim db As DAO.Database
Dim QD As QueryDef
Set db = CurrentDb()
On Error Resume Next
db.QueryDefs.Delete ("MyQuery")
On Error GoTo 0
Dim mysql As String
Dim strWhere As String

 Dim Path1 As String
 Dim fname As String
 Dim username As String
 Dim fullpath As String
 
   Path1 = "C:\Documents and Settings\"  
   username = Environ("USERNAME")
   fname = "\desktop\Export.xls"  
   fullpath = Path1 & username & fname

    Dim OLook As Outlook.Application
    Dim OlMsg As MailItem
    Dim CurrFile As String

    Set OLook = New Outlook.Application
    Set OlMsg = OLook.CreateItem(olMailItem)

'-----create excel file
mysql = "SELECT * FROM table1 "

Set QD = db.CreateQueryDef("MyQuery", mysql)
DoCmd.TransferSpreadsheet acExport, , "MyQuery", fullpath
db.QueryDefs.Delete ("MyQuery")

'-----create email
    With OlMsg
        .To = Me.txtto
        '.CC = "name2@domain2.com"
        .Subject = Me.txtsubject
        .Body = Me.txtBody
        .Attachments.Add fullpath
        .Display '<<<change this to .Send if you want
    End With

    Set OlMsg = Nothing
    Set OLook = Nothing
'-----delete excel file you created for the email

   If Len(Dir(fullpath)) >= 1 Then
   Kill (fullpath)
   End If

'-----end of code



0
 
conagramanCommented:
here is the sample databse
SendEmail03.mdb
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
conagramanCommented:
change the select statement to your query

mysql = "SELECT * FROM table1 "

and change the path to where you will temporarily save the excel file
fname = "\desktop\Export.xls"  
0
 
Jeffrey CoachmanMIS LiasonCommented:
Can you be a little more specific on what this mysterious: "tabled version of the information" is please?
Exactly what is this "Information"?
Table?, Query?, Report?, And external file?

Many "Outputs" wont lend themselves to be presented very "nicely" in the body of an email.
To do this you will have to use the .HTMLBODY property.  However, some email filtering programs will block emails with embedded HTML...

For example here is a sample that will take the output of a "Report" or a table/query, and email it out.

Here is a sample.
You must study it carefully and then adapt it for your purposes.
(See the notes in the code)

;-)

JeffCoachman
Access-EEQ-26626502-OutlookEmail.mdb
0
 
Chris_SizerAuthor Commented:
Hi Jeff,

The form uses information directly from a Table in the database itself i want to include the information that is open on the form in the HTML body of an email without any attachements?

Thanks,
0
 
Chris_SizerAuthor Commented:
Hi conagraman,

I actually have a query in the database that contains all the information that I need to email, i do not need the code to generate the query if this one can be used.  I simply need to have the information from the query put into the BODY part of the email and not as an attachement???

How would I go about doing this?

0
 
Chris_SizerAuthor Commented:
Ok, so now i  have moved a little bit futher forward and have been able to get the email to generate the way i wanted and now I just need to include the information i require from the form into the email body text.


Private Sub EmailSub_Click()
Dim objOutlook                  As Outlook.Application
Dim objOutlookMsg               As Outlook.MailItem
Dim objOutlookRecip             As Outlook.Recipient
    
'Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
'Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
 
    With objOutlookMsg
 
        'Add the To recipient(s) to the message.
        Set objOutlookRecip = .Recipients.Add(Me.SentTo.Column(2))
        objOutlookRecip.type = olTo
 
        'Subject
        .Subject = Request
        'Email Body text.
        .HTMLBody = "<table border=0 cellspacing=0 cellpadding=0><tr><td width=199 valign=top>Code:</td><td width=369 valign=top>code</td></tr><tr><td width=199 valign=top>Type:</td><td width=369 valign=top>& rs!SubType</td></tr><tr><td width=199 valign=top>Description:</td><td width=369 valign=top>Description</td></tr><tr><td width=199 valign=top>Serial Number:</td><td width=369 valign=top>serial Number</td></tr><tr><td width=199 valign=top>Purchase Date:</td><td width=369 valign=top>PurchaseDate</td></tr></table><br><br></font>"
 
        'Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
            If Not objOutlookRecip.Resolve Then
                objOutlookMsg.Display
            End If
        Next
 
        'Send email without viewing it.
        .Send
 
    End With
 
'Cleanup Code
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
End Sub

Open in new window

0
 
Chris_SizerAuthor Commented:
Hi Jeff,

Is ur example done using MS Access 2007, as it errored when i tried to run it, I checked out references and says MISSING: Microsoft Outlook 12.0 Object Library, i only have version 11.0 as i'm running MS Office 2003??
Below is the code it hightlighted when it errored?
0
 
Chris_SizerAuthor Commented:
objOutlook          As Outlook.Application
0
 
Jeffrey CoachmanMIS LiasonCommented:
Yes, you can remove v12 and then add in your version of this library (v11).
Then Compile.

0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.