Solved

Emailing Query Results into Body from MS Access 2003

Posted on 2010-11-19
12
634 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:Chris_Sizer
  • 5
  • 4
  • 3
12 Comments
 
LVL 10

Expert Comment

by:conagraman
ID: 34172559
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
 
LVL 10

Expert Comment

by:conagraman
ID: 34172563
here is the sample databse
SendEmail03.mdb
0
 
LVL 10

Expert Comment

by:conagraman
ID: 34172574
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34176591
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
 

Author Comment

by:Chris_Sizer
ID: 34176780
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
 

Author Comment

by:Chris_Sizer
ID: 34176893
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
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.

 

Author Comment

by:Chris_Sizer
ID: 34178016
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 34178229
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
 

Author Comment

by:Chris_Sizer
ID: 34178484
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
 

Author Comment

by:Chris_Sizer
ID: 34178485
objOutlook          As Outlook.Application
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34178569
Yes, you can remove v12 and then add in your version of this library (v11).
Then Compile.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34183882
;-)
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

919 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

17 Experts available now in Live!

Get 1:1 Help Now