Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Emailing Query Results into Body from MS Access 2003

Posted on 2010-11-19
12
Medium Priority
?
671 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

972 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