Populating email body with 3 different Access queries.

[NAVYTECH79]

Hello everyone,

I have been searching this website as well as google for a week now and haven't found any examples that a novice like myself can modify to make work.

I am trying to automatically populate an email with the results of 3 different queries. I have been able to successfully populate one query into the email, but I'm not sure where to go next. Any help would be greatly appreciated. Below is the current vba code I am using:

Sub RTFBodyX()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f,
Dim RTFBody1, strTo
Dim Body1
Dim MyItem As Outlook.MailItem
Dim MyApp As New Outlook.Application
DoCmd.OutputTo acOutputQuery, "BUQuery", acFormatHTML, "BUQuery.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("BUQuery.htm", ForReading)
RTFBody1 = f.ReadAll
f.Close
Set MyItem = MyApp.CreateItem(olMailItem)
With MyItem
.To = "email@test.com"
.Subject = "Test Subject"
'.HTMLBody = RTFBody1
End With
MyItem.Display
End Sub

 

[ranman256]

you would export the 3 queries, then attach them in email...
OR,
export all 3 to a single file, then attache the 1 file.

vFile = "c:\myfile.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsPhones", vFile, True, "phones"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsClients", vFile, True, "Clients"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qsHobbies", vFile, True, "Hobbies"

     'then attach the 1 file
.Attachments.Add vFile

 

[NAVYTECH79]

Thanks for the quick response. I'm actually trying to automate it because I will have to do this for about 40 different members and they will have different data (although, some data may be the same). I would like to eventually just click a button and depending on the "Member" field, it will populate an email with all three queries and that members appropriate data. I figured if I could do this in little steps, it may be easier for me to understand and remember. I was able to get one query to populate the body of the email. I think that I may have not clarified my self above. I would like to populate the queries within the body of the email, rather than attach them.

Thanks again.

 

[Micron]

Not necessarily better:

Create RTFBody'n' for each query, repeat Set f = fs.OpenTextFile("Name.htm", ForReading) for each query and open each file and copy to the nth variable. When done all, make MyItem equal RTFBody1 & vbcrlf & RTFBody2 & vbcrlf & RTFBody3, etc. You could also move this part to a function that returns RTFBody'n' after you pass the query name to it and just call it as many times as you need to:

DoCmd.OutputTo acOutputQuery, "BUQuery", acFormatHTML, "BUQuery.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("BUQuery.htm", ForReading)
RTFBody1 = f.ReadAll
f.Close

 

[NAVYTECH79]

Thanks Micron. I added your recommendations but I'm not sure where to add the part about the "make MyItem equal RTFBody1 & vbcrlf & RTFBody2 & vbcrlf & RTFBody3...." Here is what I have to date, but it is still only populating the first query in to the email.

Sub RTFBodyX()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f, es, e, ds, d
Dim RTFBody1, strTo
Dim RTFBody2, RTFBody3
Dim Body1
Dim MyItem As Outlook.MailItem
Dim MyApp As New Outlook.Application
DoCmd.OutputTo acOutputQuery, "BUQuery", acFormatHTML, "BUQuery.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("BUQuery.htm", ForReading)
RTFBody1 = f.ReadAll
f.Close
DoCmd.OutputTo acOutputQuery, "OSQuery", acFormatHTML, "OSQuery.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("OSQuery.htm", ForReading)
RTFBody2 = f.ReadAll
f.Close
DoCmd.OutputTo acOutputQuery, "ParsQuery", acFormatHTML, "ParsQuery.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("Item Master Udpate Report.htm", ForReading)
RTFBody3 = f.ReadAll
f.Close
'Debug.Print RTFBody
Set MyItem = MyApp.CreateItem(olMailItem)
With MyItem
.To = "brandon.keefer@ahss.org"
.Subject = "Item Master Updates From Shared Services"
.HTMLBody = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3
End With
MyItem.Display
End Sub


Thanks again for any and all help. I really appreciate it.

 

[ranman256]

you dont want to populat the body.
attachement retains all formats

 

[NAVYTECH79]

Unfortunately I need it to populate the email body, not an attachment. An attachment seems to be a lot easier than this process.

 

[Micron]

Two things you MUST do before going further.
1) Step through the code and confirm that each read from the file (f.readall) is actually returning something. If you mouse over each variable, you should see part of your returned text. You could also insert trial debug statements or "query" the variable in the immediate window while in break mode (type ?RTFBody1 & hit Enter). I'm assuming you know how to view and use the immediate window. Your #2 & #3 queries might not be returning any records.

2) Your Dim statement approach invites trouble. Any variable dim'd but not typed (data type expressly set) is a variant. Every variable you have declared before MyItem is a variant. As I recall, this is correct for file system scripting objects, (your f, fs) but you are assigning text to variants. I have no idea what es, e, ds, d are for, but they are variants too. Multiple declarations on one line must be like
Dim svText1 as String, svText2 as String, svMsg as String etc. or else my last two are variants if I do not do this. Even if you find that the queries are the problem, you should fix this as well. Some text operations cannot be performed on variants.
Minor points:
- you only have to set fs once. f needs to be reset after each close, so that looks OK.
- adopt the habit of separating related code chunks. Makes it easier to read. E.g. a line after your last dim statement, between file open,read and close instances, etc.
Lastly for now, I would have thought your concatenation of the body texts was OK, but you could try this if the other two checks/fixes don't solve it:

RTFBody3 = f.ReadAll
f.Close
'Debug.Print RTFBody
RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3
Set MyItem = MyApp.CreateItem(olMailItem)
With MyItem
.To = "brandon.keefer@ahss.org"
.Subject = "Item Master Updates From Shared Services"
.HTMLBody = RTFBody1
RTFBody1 = RTFBody1 & vbcrlf & RTFBody2
'Debug.Print RTFBody

Stick with it, I think you'll solve it soon!

 

[NAVYTECH79]

Micron,

Thank you again for your help. I stepped through each read and it did in fact return the correct results for each of them. When I hovered my mouse over each variable it says that they ="".

I corrected all "Dim" statements. I removed the fs statements after the initial one. I also separated related code chunks.

Lastly, I tried the above code for the concatenation of the body, but it's still only populating the first query in the body of the email.

I want to thank you all again, as I have learned a great deal already, and look forward to learning more from you.

 

[Micron]

When I hovered my mouse over each variable it says that they ="".

Which variables? RTFBody1 and RTFBody2 and RTFBody3? If all 3, it doesn't make sense because the first is working. Did you check these while the line was highlighted or when the next line was highlighted? If the former, it's because you haven't processed the line yet, so go to the next line and check again. Maybe a debug.print statement after each ReadAll line would be better in order to confirm what's in the variables. Do that and report back with the results of that test and post your amended code too.