Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Emailing Query Results into Body from MS Access 2003

Posted on 2010-11-19
12
Medium Priority
?
672 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

580 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