E-mail query results as text

[pbaldy]

I have to leave for lunch in a minute, but offhand this jumps out:

msgtext = strAccounts
.HTMLBody = msgtxt

See the problem? If you're still having trouble, can you post the db?

 

[TallMan]

Thank you very much for your help PBaldy. I have it working. It is taking some time to get the HTML the way I want it but nontheless it works.

I have one last question for you.

When you set the strAccounts = to the recordsets, why do you have to write it with an additional straccounts

example, I would think you could just have strAccounts = & rs.fields(0) &
not sure why you have to have the strAccounts = strAccounts & rs.fields(0) &

 

[pbaldy]

You saw why earlier, when your final SQL only included the last record. If you have a recordset with 5 records numbered 1 thru 5, this

strAccounts = & rs.fields(0)

will set the variable to 1 the first pass, replace the 1 with 2 the second pass, and so on, leaving 5 as the value at the end. In other words, the code is saying "put this value in the variable", so it will only hold the last value given it. This

strAccounts = strAccounts & rs.fields(0)

says "take what's already in the variable and add this value to it". You can watch either happen by setting a breakpoint and stepping through the code, watching the variable as the code executes.

 

[TallMan]

Hello,

I am not sure if I should start a new thread for this or not but I have some questions concerning some details of my string. The second record set field is supposed to show up in my email as Currency but it is showing as a regular text. I need $5,000 instead I am getting 5000

I have tried the CCUR(rs.fields(1)) in the string but it has not changed any of the formatting. I also need to have it where all of the fields are right justified so that they are all in line.

Everything else is working like a charm!

Any ideas?

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) & "    " & CCUR(rs.Fields(1)) & "    " & rs.Fields(2) & "    " & rs.Fields(3) & "    " & rs.Fields(4) & "    " & rs.Fields(5) & "    " & rs.Fields(6) & "    " & rs.Fields(7) & "<br>"
rs.MoveNext
Wend
msgtxt = "Please see the below information regarding PIPS for processing.<br> Please contact the AMS Cash Operations department with any questions.<br><br><br>" & _
         "Regards,<br>AMS Cash Operations<br>Extension 74530<br><br>" & _
         "<TABLE cellpadding='5' cellspacing='5'><TR><TD><u>Account</u></td>    <TD><u>Amount</u></td>    <TD><u>PPM Code</u></td>    <TD><u>Pay Date</td>    <TD><u>Pay Month</u></td>    <TD><u>Start Date</u></td>    <TD><u>End Date</u></td> </tr></table>" & _
         strAccounts
 
 
Destination = "Tall.Man@RaymondJames.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

 

[pbaldy]

Try

Format(rs.Fields(1), "Currency")

By the way, your method of referring to the field is perfectly valid, but I prefer

rs!FieldName

since then I don't have to keep track of what field "1" is.

 

[TallMan]

Great advice. It would make it a lot easier to track for future code. Thanks PBaldy.

Do you know how to Right Justify a field? I am trying to format the HTML code but my fields are all over the place like below. I need to have them all Right justified so that I can have the right spaces in the HTML code. I have searched around but can not find anything on this.




Account Amount PIP_Code PAY_Type Start_Date
88888888 87878.00 A 7 March - June - Sept - Dec 12/12/2010
10101010 897897.00 A 7 March - June - Sept - Dec
22222222 6500.00 A 7 June - Dec 10/10/2012 10/10/2013
22222223 545.00 A 7 March - June - Sept - Dec 11/11/2014 12/12/2014
33333333 600000.00 A 21 Monthly 12/12/2012 12/12/2013
55555555 1000.00 A 7 March - June - Sept - Dec 12/12/2012 12/12/2013
77777777 2000.00 A 7 Monthly 1/1/2013 12/15/2022 Test