April 5, 2005 at 11:19 am
Hi All,
Please help me. In Access email, is that possible for me to attach a query result query into the email body instead of sending an attachment? Or is there any way to send a better attachment? The format of a txt file for my attachment does not look good at all. I'm writing an time-off taking for employees. Everytime jthey submit a PTO, my application will automatically send informing emails to manager. The info will be like this:
Request_Date_From Request_Date_TO #ofHours Username
4/1/05 4/4/05 32hrs dlee
I'd like to my query result will be imported directly to the email body. Please give me a hand. Thanks so much.
Minh Vu
April 6, 2005 at 12:52 am
Hi,
There are some very good examples of using Access emails in site http://www.helenfeddema.com .
Alternatively, why not use a macro "sendobject" which formulates your email and send your query as a "Snapshot" type attachment. If you want the query to be presented better create a report on it.
Richard
April 6, 2005 at 10:30 am
Hi!
I agree that Helen Feddema's site is a great source for code with Access and other MS Office products.
Regarding Richard's suggestion, the SendObject method of DoCmd in Access will attach a file to the email. In your situation, Minh Vu, it would create and attach an HTML file of your query to the email. It doesn't seem that's what you want.
What you've said you need, which was to include the query results in the email body in a format that looks decent, is not available in a single method or function available in Access, as far as I know.
What you can do is below. I worked up this example and it seems to work well. This code exports the query as an HTML file, then gets the HTML content of that HTML file, creates an email message, and places the HTML in the email body.
I'll admit that saving the HTML file to the harddrive and then importing it seems to be a very round about way to get the HTML. But I don't know how else to do it.
This code assumes you're running Access and have Outlook installed as well. You'll also have to select Outlook in the list of references (Tools | References) while in your VBA code window.
There are some issues you'll likely have to deal with such as the security "feature" added to Outlook by Microsoft which shows a warning message that you must deal with when manipulating Outlook from another application. There are ways to get rid of that.
There are probably better ways of doing what I've done. I'd love to hear other suggestions.
Here's the code:
Dim strFile As String
Dim strTo As String
Dim strBody As String
Dim intFile As Integer
Dim varTmp As Variant
Dim varOut As Variant
Dim objOutlook As New Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
strFile = "c:\temp\mydata.html"
strTo = "myemail@abc.com"
' Save the query as an HTML file on the hard drive.
DoCmd.OutputTo acOutputQuery, "qryMyData", acFormatHTML, strFile
' Get the contents of that HTML file.
intFile = FreeFile
Open strFile For Input As intFile
Do Until EOF(intFile)
Input #intFile, varTmp
varOut = varOut & varTmp
Loop
Close #intFile
strBody = varOut
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
' Set the recipients
.Recipients.Add strTo
.Recipients.ResolveAll
.Subject = strSubject
' Set the body format and content
.BodyFormat = olFormatHTML
.HTMLBody = strBody
' Send the email
.Display
' .Send
End With
Set objOutlook = Nothing
Regards,
Kevin
April 6, 2005 at 10:36 am
I think the way to do this is to open a recordset which contains the data to be placed in the message body. Then simply use DoCmd.SendObject to send the email.
Sub sEmailNotification()
Dim rst As New ADODB.Recordset
Dim strMsg As String
'get data for request (assumes only 1 record in table)
rst.Open "SELECT REQUEST_DATE_FROM, REQUEST_DATE_TO, REQUEST_HOURS, USERNAME FROM T_REQUESTS", _
CurrentDb.Connection
'build message
strMsg = rst("REQUEST_DATE_FROM") & _
vbTab & rst("REQUEST_DATE_TO") & _
vbTab & rst("REQUEST_HOURS") & _
vbTab & rst("USERNAME")
'build email
DoCmd.SendObject acSendNoObject, , , "Manager@work.com", , , "PTO Request", strMsg
End Sub
April 6, 2005 at 12:38 pm
Thank you so much for a great help. I tried Andy method, but it did not work for me. I'm not sure that your method will work in Access 97? That the version that I'm working on. Here is my codes:
Dim rst As Recordset
Dim strMsg As String
Dim db As Database
Dim supervisoremail As String 'Supervisor's email
supervisoremail = DLookup("[username]", "supervisoremail") & "@chrontel.com"
Set db = CurrentDb()
Set rst = db.OpenRecordset("qry EmailNotification")
'build message
strMsg = rst("Request_From") & _
vbTab & rst("Request_To") & _
vbTab & rst("Request_Hours") & _
vbTab & rst("Start_Time") & _
vbTab & rst("USERNAME")
'build email
DoCmd.SendObject acSendNoObject, , , rtrot@ework.cm, , , "Employee Submitted a PTO. Please Approve or Deny!", strMsg
______________________
I have another question. Can I use this method in Sqlserver if I use sp_SQLSMTPMail?
exec sp_SQLSMTPMail 'rstrot@ework.com ',--email receivers--
'', --email body--
'PTO alert',--email subject--
'',--atachements''
'select * from table_PtoAlert', --query result--
'System',--sender--
'', --cc--
Really appreciate again.
Minh Vu
April 6, 2005 at 12:51 pm
April 6, 2005 at 1:01 pm
Regarding Andy's method not working, what exactly isn't working? What error messages are you getting and where in your code? Or is it that the code runs without error but the data isn't formatted nicely in your email?
Regarding your new question, if you mean can you have the query results shown in the email body as you were asking before, I think yes. The @vcQuery parameter of the sp_SQLSMTPMail stored procedure seems to support that (if I'm reading the comments inside the sp correctly). You can see a thread on it here:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=112330
Kevin
April 6, 2005 at 1:15 pm
I correct my mistake to put my string in quotes, but it gave me this message:
"Run-time error 3061. Too few parameters. Expected1".
Is that because my query result has more than 1 records?
Minh
April 6, 2005 at 1:29 pm
April 6, 2005 at 1:34 pm
It stops at the line:
Set rst = db.OpenRecordset("qry EmailNotification").
I'm still debugging it. thanks
Minh
April 6, 2005 at 1:38 pm
Can you post the SQL in your Access query?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply