Embed query results into an email body (3 Viewers)

J

jco23

Registered User.
Local time
Today, 17:17
Joined
Jun 2, 2015
Messages
83
i appear to be a roadblock here, where my VBA code is producing some weird results.

essentially, everything works perfectly fine except that the first field in the row of the query results in the e-mail contains additional text. that text has the ending string of the path where the app resides.

below is my code:

Dim subj As String
Dim msgbody As String
Dim fs, f
Dim RTFBody

Set fs = Nothing
Set f = Nothing

DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatHTML, "QueryResults.html"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("QueryResults.html", ForReading)
RTFBody = f.ReadAll
f.Close

subj = "MySubject"
msgbody = "Hello," & "<br/>" & "<br/>" & "Can you review the following?" & "<br/>" & "<br/>" & RTFBody

funcDisplayEMail_nosend "", subj, msgbody, , , , , False

below is my result:

Hello,

Can you review the following?


MyQuery​
Field1​
Field2​
Field3​
Field4​
Item1mypath\app\DEV\app.accdb
20​
Item2
37​
Item3
62​

"mypath\app\dev\app.accdb" is just part of the full path of where the app resides.

basically, I'd like to NOT display the app path in the e-mail.

I've seen other examples on how to get this to work, but when I do, I get "too few parameters, expected 2"

thanks!
 
I cannot replicate that issue.
What do you see if you open the html as text?
What does the query actually show?
 
Gasman said:
I cannot replicate that issue.
What do you see if you open the html as text?
What does the query actually show?
Click to expand...
when add this line: DoCmd.OutputTo acOutputQuery, "MyQuery", acFormatRTF, "C:\Users\" & Adent & "\Desktop\QueryResults.rtf", the file saved looks fine.

basically, the same as above but without the file path
 
Try right clicking the file and open with notepad. I use notepad+.
Just trying to see where it is coming from.
I would check the query first though.
Step by step.
 
i also tried using the results from a table, and when the table gets created, it looks fine. however, when i attempt to embed the table into an e-mail, I get the same problem.
thus, I suspect something is awry with one of these two lines:

Set f = fs.OpenTextFile("QueryResults.html", ForReading)
RTFBody = f.ReadAll
 
This could be one of those times a small repo database with sanitized data could be useful in helping diagnose a problem.
 
Please post code between CODE tags to retain indentation and readability, especially longer snippets.

I tested code with my query, except for funcDisplayEMail_nosend because its code is not provided. No file is saved. I had to specify full destination path.
All data is presented as ASCII codes when read into the string. However, it looks just fine when viewing file directly. No file path included.

Learned something new. This is a nice alternative to looping recordset to build HTML formatted data.
 
I got it to work by using delete and append queries to a local table, and then bringing in that table:

Private Sub Command57_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sHTML As String
Dim strQuery As String
Dim msgbody As String

DoCmd.SetWarnings False
DoCmd.OpenQuery "dqry_MyQuery"
DoCmd.OpenQuery "aqry_MyQuery"
DoCmd.SetWarnings True

Set db = CurrentDb
strQuery = "SELECT * FROM [tbl_MyTables];"
Set rs = db.OpenRecordset(strQuery)

sHTML = "<HTML><BODY><table border='1' cellpadding='5' cellspacing='0'>" & _
"<tr><th>Field1 Header</th><th>Field2 Header</th><th>Field3 Header</th><th>Field4 Header</th></tr>"

Do While Not rs.EOF
sHTML = sHTML & "<tr>" & _
"<td>" & rs![field1] & "</td>" & _
"<td>" & rs![field2] & "</td>" & _
"<td>" & rs![field3] & "</td>" & _
"<td style='text-align: right;'>" & FormatCurrency(rs![field4], 2) & "</td>" & _
"</tr>"
rs.MoveNext
Loop

sHTML = sHTML & "</table></body></html>"

msgbody = "Hello," & "<br/>" & "<br/>" & "Please review the following." & "<br/>" & "<br/>" & sHTML

funcDisplayEMail_nosend "", "For your review", msgbody, , , , , False

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

field4 is a currency
 
Again, please post code between CODE tags.

Should be able to query source table(s) and not need local "temp" table.
 
You must log in or register to reply here.

Similar threads

J
Display query results in Email body as hyperlink
Replies
11
Views
1,086
theDBguy
theDBguy
M
Signature Image in GMail email body
Replies
7
Views
737
Marshall Brooks
M
oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
821
oxicottin
oxicottin
Uncle Gizmo
Custom chat interface DEMO using two LLM's - Ollama Mistral and open AI GPT4
Replies
1
Views
1,192
Uncle Gizmo
Uncle Gizmo
A
Solved VBA Code to generate email and place query result in Message body
Replies
8
Views
1,578
Ashfaque
A

Users who are viewing this thread

Total: 4 (members: 1, guests: 3)
Back
Top Bottom