E-mail query results as text

[BamaColtsFan]

I need to come up with a way to place the results of a query into the body of an e-mail message that will be sent with Outlook. I already have a function that sends e-mail based on individual records in a table but I would like a seperate process that sends the entire contents of a query as text in the body. Currently, I use a Replace function to insert information into the body of the message and I'm thinking the same would be needed to do this. What I don't know how to do is to call the query results as text that can then be inserted using the Replace function. Any ideas would be deeply appreciated....

 

[pbaldy]

I would probably open a recordset on the query and cycle through the fields/records building a string to use with whatever method you use to send the email.

 

[BamaColtsFan]

Paul -

So you think I can run through the query results and use the replace function for all the records? I assume that would mean a loop but wouldn't the loop just replace the text each time? Or can I append the results with a loop? I'm not too sure how that might work though... The replace function uses tokens in the text of the message body and simply swaps out the value of the token with the related data from the table. This works nicely for a single record but not so much for a list. I guess I don't really understand how to go about what you suggest....

 

[pbaldy]

I'm not sure what you'd be using the Replace function in this situation. You can append with the loop:

YourVariableName = YourVariableName & " More text here" & vbCrLf & "This will be on the next line"

 

[TallMan]

PBaldy,

Could you explain this in greater detail? I am trying to execute the same command but am lost as to where to start.

I have the following code so far...

Private Sub Command104_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim msgtxt As String
Dim Summary As String
Dim Destination As String
Dim Sbject As String
Dim CarbonCopy As String
'Dim objOutlook As Outlook.Application
'Dim objEmail As Outlook.MailItem
' Dim rs As DAO.Recordset
 
 
msgtxt = "<font face='Arial'><font color=red><b>" & test & "</font></b><br><br>" & _
"PLease see the below information regarding PIPS for processing.<br>" & _
"Please contact the AMS Cash Operations department with any questions. <b><font color=red>" & test & "</font></b><br><br>" & _
"Regards<br>AMS Cash Operations<br>Extension 74530<br><br>" & _
"<table border='1' align=Left width='800' frame='box'><tr><th><font color='Blue' face='Arial'>" & _
"PIP/PPY Information</font></th></tr><tr><td><font color=Red face='Arial'>" & _
"Jenn, Hopefully we can get the Information to enter here =) .</font></td></tr></table><br"
 
Destination = "[email=tallman@blah.com][/email]"
Sbject = "Testing for the PIP Database!!!"
CarbonCopy = "[email=""].com[/email] "
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
With objEmail
' .FlagDueBy = DateAdd("w", 2, Now()) 'this gives a two day reminder
' .Importance = olImportanceHigh
.To = Destination
.CC = CarbonCopy
.Subject = Sbject
.HTMLBody = msgtxt
.Display
' .send
 
End With
 
End Sub

What I am thinking is to run an sql query then set it to the .openrecordset (SQL) then somehow bring that into the html. Do you think I am on the right track?

 

[pbaldy]

Sure. Generally:

msgtxt = "Some text here " & Recordset!FieldName & " more text here"

 

[TallMan]

Hmm not having any luck with this.....am I missing something in the code?

Private Sub Command104_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim msgtxt As String
Dim Summary As String
Dim Destination As String
Dim Sbject As String
Dim CarbonCopy As String
'Dim objOutlook As Outlook.Application
'Dim objEmail As Outlook.MailItem
Dim rs As DAO.Recordset
SQL = "select * from Main_PIP_TBL "
Set rs = db.OpenRecordset(SQL)
 
msgtxt = "<font face='Arial'><font color=red><b>" & test & "</font></b><br><br>" & _
         "PLease see the below information regarding PIPS for processing.<br>" & _
         " & rs!Account & " & _  BOB HERE IS WHERE I AM TRYING TO INSERT THE FIELDS.
         "Please contact the AMS Cash Operations department with any questions. <b><font color=red>" & test & "</font></b><br><br>" & _
         "Regards<br>AMS Cash Operations<br>Extension 74530<br><br>" & _
         "<table border='1' align=Left width='800' frame='box'><tr><th><font color='Blue' face='Arial'>" & _
         "PIP/PPY Information</font></th></tr><tr><td><font color=Red face='Arial'>" & _
         "Jenn, Hopefully we can get the Information to enter here =) .</font></td></tr></table><br"
 
Destination = TallMAn@BLAH
Sbject = "Testing for the PIP Database!!!"
'CarbonCopy = " "
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
With objEmail
'    .FlagDueBy = DateAdd("w", 2, Now())   'this gives a two day reminder
'    .Importance = olImportanceHigh
    .To = Destination
    '.CC = CarbonCopy
    .Subject = Sbject
    .HTMLBody = msgtxt
    .Display
'   .send
 
End With
 
End Sub

Thank you for any advice you can post.

 

[pbaldy]

Should we wait for Bob?

You have the recordset reference inside the quotes; it needs to be outside. I assume you're getting "rs!account" in your finished string, instead of the account number from the recordset.

 

[TallMan]

Sorry for the Bob, it has been an typical monday. You are correct in stating I am getting the "RS!Account" in the finished string. So now I am not pulling all the accounts that are in the table and inserting them into the table. It is only inserting the first account....not all 10 accounts that are in the table...

 

[pbaldy]

Sounds like you want a loop:

Do While Not rs.EOF
  'do your thing
  rs.MoveNext
Loop

 

[TallMan]

Pbaldy,

I am trying to learn loops, This would be the first time executing this type of code. Could you do me a huge favor and tell me where I would insert this information?

Private Sub Command104_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim msgtxt As String
Dim Summary As String
Dim Destination As String
Dim Sbject As String
Dim CarbonCopy As String
'Dim objOutlook As Outlook.Application
'Dim objEmail As Outlook.MailItem
Dim rs As DAO.Recordset
SQL = "SELECT Main_PIP_TBL.Account, Main_PIP_TBL.Amount, Main_PIP_TBL.PPM_Code, Main_PIP_TBL.Pay_Date, Main_PIP_TBL.Pay_Month, Main_PIP_TBL.Starts_On, Main_PIP_TBL.Ends_On, Main_PIP_TBL.Funding, Main_PIP_TBL.Delete, Main_PIP_TBL.Update, Main_PIP_TBL.Add_Date, Main_PIP_TBL.Update_Date, Main_PIP_TBL.Cancel_Date, Main_PIP_TBL.Associate_Name FROM Main_PIP_TBL;"
Set rs = db.OpenRecordset(SQL)
Do While Not rs.EOF
msgtxt = "Please see the below information regarding PIPS for processing.<br>" & _
         "Please contact the AMS Cash Operations department with any questions. <b><font color=red>" & test & "</font></b><br><br><br>" & _
         "" & rs.Fields(0) & "    " & rs.Fields(1) & "    " & rs.Fields(2) & "    " & rs.Fields(3) & "    " & rs.Fields(4) & "    " & rs.Fields(5) & "    " & rs.Fields(6) & "    " & rs.Fields(7) & "<br><br>" & _
         "Regards,<br>AMS Cash Operations<br>Extension 74530<br><br>"
 
rs.MoveNext
loop
 
Destination = "Tallman@blah.com"
Sbject = "Testing for the PIP Database!!!"
'CarbonCopy = " "
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
With objEmail
'    .FlagDueBy = DateAdd("w", 2, Now())   'this gives a two day reminder
'    .Importance = olImportanceHigh
    .To = Destination
    '.CC = CarbonCopy
    .Subject = Sbject
    .HTMLBody = msgtxt
    .Display
'   .send
 
End With
End Sub

I am still getting one record...I thought I had it in the right place. Anythoughts?

Thanks again in advance for your help!

 

[pbaldy]

Are you trying to send a separate email to each account, or a single email that lists all the accounts?

 

[TallMan]

a single email that list all of the accounts.

 

[TallMan]

Hey Pbaldy,

Is my code way off for what I am looking to do?
Thanks again.

Tallman

 

[pbaldy]

Sorry, didn't get an email on the reply. What will happen now is that each pass through the loop will simply overwrite the previous, so I assume your email currently contains the last value. Depending on the look you want, you can build a string to be used. Inside the loop:

strAccounts = strAccounts & rs.Fields(0) & ", "

and then after the loop, incorporate strAccounts into your message variable. You can get pretty creative, so like I say it really depends on the look you want.

 

[TallMan]

You are correct when saying the last record of the query is showing in the email body.

TO be honest I am confused with how to complete this. I am new to VBA.

I am assuming I would have to

Dim StrAccounts as String at the very top of the section

then in the loop write the code strAccounts = strAccounts & rs.Fields(0) & ", "

Sorry for the learning curve here Pbaldy.

 

[pbaldy]

Learning is why we're all here; don't be sorry about it. If the look you want is:

"blah blah for the following accounts: 123, 456, 789. More blah here..."

You would do the first two steps you mentioned, then after the loop:

msgtxt = "blah blah for the following accounts: " & strAccounts & ". More blah here..."

It occurs to me that you'd want to trim off the trailing comma, but that's easy.

 

[TallMan]

The way I would like it to read is:

PLease see the following list of accounts that blah blah blah.

Account Amount Start date End Date
11111111 $50 1/1/2000 12/1/2000
22222222 $100 1/1/2000 12/1/2000
33333333 $2000 1/1/2000 12/1/2000

 

[pbaldy]

Similar technique, though you may want to play with the spacing. I'm not strong with HTML, but I think this is close:

strAccounts = strAccounts & rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " & rs.Fields(3) & "<br>"

Not sure if you need " " instead of just the spaces.

 

[TallMan]

Ok so I have changed the code to this below. I am not getting any records on my email now. I am thinking where I have the msgtext = strAccounts it needs to have something more like & strAccounts & but I can not get anything to work.

Right now I am simply trying to get the recordset to show in the email then I will build the html around it.

.
Private Sub Command104_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim msgtxt As String
Dim Summary As String
Dim Destination As String
Dim Sbject As String
Dim CarbonCopy As String
Dim strAccounts As String
Dim rs As DAO.Recordset
    SQL = "SELECT Main_PIP_TBL.Account, Main_PIP_TBL.Amount, Main_PIP_TBL.PPM_Code, Main_PIP_TBL.Pay_Date, Main_PIP_TBL.Pay_Month, Main_PIP_TBL.Starts_On, Main_PIP_TBL.Ends_On, Main_PIP_TBL.Funding, Main_PIP_TBL.Delete, Main_PIP_TBL.Update, Main_PIP_TBL.Add_Date, Main_PIP_TBL.Update_Date, Main_PIP_TBL.Cancel_Date, Main_PIP_TBL.Associate_Name FROM Main_PIP_TBL;"
    Set rs = db.OpenRecordset(SQL)
While Not rs.EOF
  strAccounts = strAccounts & rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " & rs.Fields(3) & "<br>"
            
rs.MoveNext
Wend
msgtext = strAccounts
        
Destination = "Tall.Man@blah.com"
Sbject = "Testing for the PIP Database!!!"
'CarbonCopy = ""
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
 
With objEmail
'    .FlagDueBy = DateAdd("w", 2, Now())   'this gives a two day reminder
'    .Importance = olImportanceHigh
    .To = Destination
    '.CC = CarbonCopy
    .Subject = Sbject
    .HTMLBody = msgtxt
    .Display
'   .send
    
End With
End Sub