Solved

Emailing Query Results into Body from MS Access 2003

Posted on 2010-11-19
12
624 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
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.

 

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

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.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 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

21 Experts available now in Live!

Get 1:1 Help Now