DAO Recordset as a text string

[BamaColtsFan]

Okay - I'm not entirely sure how to ask this question so please bear with me... I need to place the contents of a DAO Recordset into a string value that can then be inserted into a text file that is the body of an e-mail message. The DAO Recordset is a query that will have from 1 to any number of rows and I need all of the rows and all of the columns to show in the text (ideally, in some kind of orderly fashion). What I hope to be able to do is send out an e-mail that provides generic instructions from the text file along with the detailed records from the query. I can get the code to cycle through the records and assemble the list for each respondent; I just can't seem to figure out how to drop the list of records into the body of the message... Any advice would be deeply appreciated!

 

[Endre]

Why not just send the output to a text file and use that?

rs_Query refers to your recordset ordered correctly from TABELDAT fields FieldA, FieldB, FieldC.

Dim rs_query as Recordset
Set rs_query = CurrentDb.OpenRecordset("SELECT * FROM TABLEDAT;")
Open "C:\NewtextFile.txt" For Output As #1
Do While Not rs_query.EOF
    Print #1, rs_query!FieldA & Chr(9) & rs_query!FieldB & Chr(9) & rs_query!FieldC
    rs_query.movenext
loop
Close #1

Then use this as the body of your eMail.

 

[BamaColtsFan]

Well, I'm already calling a text file for the body of my e-mail. I need to insert the data from the query into a specified point of that other text file. I believe I can do that using a token to mark where it goes and then a Replace command in the code to send the mail. I guess I need to look at your method a little closer to see if I can then call the second text file into the first one as a replacement for the token...

 

[Endre]

You can use VBA to open up the file, do a search for the location e.g. bookmark, textstring etc. Once found, then paste in the text and save. If you set up MicrosoftWord in your references and refer to Word Objects in VBA it should be a breeze.

And if your going to do that, don't bother sending the recordset output to a flat file as I suggested, just write it straight into the main text file.

Have a look at the CreateObject Function in MSAccess Help - But set up MSWord as a reference and your code will be easier to write. If your still stuck let me know.