Embed query results into an email body (3 Viewers)

[jco23]

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!

 

[Gasman]

I cannot replicate that issue.
What do you see if you open the html as text?
What does the query actually show?

 

[jco23]

I cannot replicate that issue.
What do you see if you open the html as text?
What does the query actually show?

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

 

[Gasman]

Not what I asked?

 

[jco23]

Not what I asked?

sorry, I thought the output would be the same as opening the HTML as a text.... how do I that then?

 

[Gasman]

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.

 

[jco23]

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

 

[GPGeorge]

This could be one of those times a small repo database with sanitized data could be useful in helping diagnose a problem.