Populating email body with 3 different Access queries.

N

NAVYTECH79

New Member
Joined
Jul 9, 2015
Messages
11
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
 
Sort by date Sort by votes
My apologies, I didn't let the correct line process first. RTFBody1 = <HTML>. RTFBody2 = <HTML>. RTFBody3 = <HTML>. All debug.print statements displayed the appropriate data. Here is the updated code:

Code: 
Sub RTFBodyX()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Dim RTFBody1 As String, strTo As String
Dim RTFBody2 As String, RTFBody3 As String, RTFBody4 As String
Dim Body1 As String
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
Debug.Print RTFBody1

DoCmd.OutputTo acOutputQuery, "OSQuery", acFormatHTML, "OSQuery.htm"
Set f = fs.OpenTextFile("OSQuery.htm", ForReading)
RTFBody2 = f.ReadAll
f.Close
Debug.Print RTFBody2

DoCmd.OutputTo acOutputQuery, "ParsQuery", acFormatHTML, "ParsQuery.htm"
Set f = fs.OpenTextFile("ParsQuery.htm", ForReading)
RTFBody3 = f.ReadAll
f.Close
Debug.Print RTFBody3

RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3
Set MyItem = MyApp.CreateItem(olMailItem)
With MyItem
   .To = "test@email.com"
   .Subject = "Item Master Updates From Shared Services"
   .HTMLBody = RTFBody1
   RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3
End With
MyItem.Display
End Sub
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You don't need this second variable setting
.HTMLBody = RTFBody1
RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3
you already have it here

RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3
Set MyItem = MyApp.CreateItem(olMailItem)

Other than that, nothing jumps out. If you're saying it still only outputs the text for the first query but each debug print shows a full query read, then I'm running out of ideas. What I would have done is check that the 3 are being concatenated into RTF1 at this point by debug.print immediately after:
RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3
debug.print RTFBody1
Let me know if this shows the text for all 3 reads.
 
Upvote 0
I removed the second variable setting ".HTMLBody = RTFBody1". The debug.print shows all three reads, but the email only shows the first read. Could it have something to do with HTML's only allowed to have one open/closed HTML tag? Thanks again for your help.
 
Upvote 0
My friend, I'm a very black-and-white person. For me, you need to be very specific and provide more info. When you say "all three reads" I cannot figure out if you mean individually (which would be of no help now) or this one:
RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3, and I assume it is not this one. Thus we are really no farther ahead. You could also provide more info, like the value of RTFBody1 at the point where they are concatenated, otherwise I'm in the dark as to what you're trying to add to the body.
 
Upvote 0
The immediate window for the Debug.Print RTFBody1, which is right under the "RTFBody1 = RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3" line, it displays the results for the 3 queries. The current code is below. The value of of RTFBody1 = "HTML", and this is at the point that they are concatenated. I am trying to add all 3 queries to the body of the email, but it only populates the first query.

Code: 
Sub RTFBodyX()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Dim RTFBody1 As String, strTo As String
Dim RTFBody2 As String, RTFBody3 As String
Dim Body1 As String
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 f = fs.OpenTextFile("OSQuery.htm", ForReading)
RTFBody2 = f.ReadAll
f.Close

DoCmd.OutputTo acOutputQuery, "ParsQuery", acFormatHTML, "ParsQuery.htm"
Set f = fs.OpenTextFile("ParsQuery.htm", ForReading)
RTFBody3 = f.ReadAll
f.Close

Set MyItem = MyApp.CreateItem(olMailItem)
With MyItem
   .To = "test@email.com"
   .Subject = "Item Master Updates From Shared Services"
   .HTMLBody = RTFBody1
   RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3
   Debug.Print RTFBody1
End With
MyItem.Display
End Sub

Sorry, but I just realized that when I typed the value of RTFBody1 2 and 3 above that it removed the "HTML" from it. I guess it doesn't like <> within text.
 
Last edited:
Upvote 0
HTML: 
Set MyItem = MyApp.CreateItem(olMailItem) 
With MyItem    
.To = "test@email.com"    
.Subject = "Item Master Updates From Shared Services"    
.HTMLBody = RTFBody1    
RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3

You have the concatenation AFTER you assign RTFBody1 to the email body, yet in your July 17 post it looks correct, and you didn't post the concatenated value as I suggested yesterday. Fix as follows, and if this doesn't work, I am out of ideas and could only help if I had a copy of the db.

HTML: 
RTFBody1 = RTFBody1 & vbCrLf & RTFBody2 & vbCrLf & RTFBody3 
Set MyItem = MyApp.CreateItem(olMailItem) 
With MyItem    
.To = "test@email.com"    
.Subject = "Item Master Updates From Shared Services"    
.HTMLBody = RTFBody1
 
Last edited:
Upvote 0
Sorry about the code mix-up. I had actually tried both and posted the last one I tried, my apologies. Neither populate the email correctly, but the "debug.print" does actually populate all 3 queries within the intermediate window. Someone suggested to me that this may not be working correctly because the module is producing 3 separate "HTML" files and when it opens an "HTML" email, it only allows for one "HTML" to populate. If this is the case, is there a way to populate the "Body" of each "HTML" query within one "HTML file as separate "Body's" within one HTML tag?
 
Upvote 0
NAVYTECH79 said:
... is there a way to populate the "Body" of each "HTML" query within one "HTML file as separate "Body's" within one HTML tag?
Click to expand...

I think this is what I have been trying to do. Note that in my last post, I asked a second time to see the results of the debug.print statements: "and you didn't post the concatenated value as I suggested yesterday." and still no output from the statements in your last post. I'm afraid I cannot respond to this thread any more. Sorry.
 
Upvote 0
There really isn't any reason for you to be rude. As I have stated before, I am very new to this. Not once have you posted that you were trying to populate an HTML file as I stated in post #17. Maybe that's what you have been doing within the code, but like I said, I am very new to this and didn't understand that. What is it that you want me to post, the results from the intermediate window or as you indicated very early in the board, the results of pointing to the variable? I did replay that when pointing to the variable it simply say's "HTML". I also apologized for not realizing that my post was not actually displaying the "HTML" part. I will continue to work on this with or without your help. The results from the intermediate window are too large to post on here. Thanks for the help you did offer.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Get Email address from Query table to send emails
Replies
5
Views
772
welshgasman
welshgasman
G
  • Question Question
ACCESS VBA code to send email's on Lotus 8.5
Replies
6
Views
1K
Ghostline
G
I
  • Question Question
E-mail Module problem
Replies
2
Views
721
Trevor G
Trevor G
A
  • Question Question
Send Query Results as E-mail Body
Replies
6
Views
9K
Micron
M

Forum statistics

Threads
1,226,168
Messages
6,189,383
Members
453,541
Latest member
Apl1966

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top