Windows XP 
Access 2010 32bit
Hello,
I am having a problem displaying an output string to the body of an email. I have already successfully displayed other content into the body of the email and and have used similar code changing certian variables so I'm not sure why this is not working. I have created a sql query that selects the data that I need:
OOB-Clients
SELECT ClientDiv.Client_Division, RTIClientTracker.Notes
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE RTIClientTracker.Notes Is Not Null
ORDER BY ClientDiv.Client_Division;
I have stepped through my code with my Immediate window and Watches window open which shows me that I am looping through my data and adding a value to the variable "OutputString3". When it comes to displaying this data in the body of the email all I get is OutputString3 and not the value. Here is the new code that I added to my function (in RED):
Private Sub Released_Click()
On Error GoTo Err_Released_Click ' Initialize error handling.
Dim db As Database
Dim rst_ClDiv As Recordset
Dim rst_ClDiv2 As Recordset
Dim rst_Notes As Recordset
Dim sClDiv As String
Dim sClDiv2 As String
Dim sClDiv3 As String
Dim iEMB As Integer
Dim iEMB2 As Integer
Dim sOOBFix As Variant
Dim sOOBFix2 As Variant
Dim sNotes As String
Dim Ns As String
Dim OutputString1 As String
Dim OutputString2 As String
Dim OutputString3 As String
Dim mess_body As String
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set db = CurrentDb
'************************************************* ***************************************
Set rst_ClDiv = db.OpenRecordset("ClientDiv-EMCARE")
rst_ClDiv.MoveFirst
Do While rst_ClDiv.EOF <> True
sClDiv = rst_ClDiv.Fields("ABC2")
iEMB = rst_ClDiv.Fields("EMB_OOB")
sOOBFix = rst_ClDiv.Fields("OOB_Fixed")
If Not IsNull(sOOBFix) Then 'BALANCED BOLD RED
OutputString1 = OutputString1 & "<strong><font color=""Red"">" & sClDiv & "</font></strong>" & ", "
ElseIf iEMB = -1 And IsNull(sOOBFix) Then 'OUT OF BALANCE : BLACK
OutputString1 = OutputString1 & "<strong><font color=""black"">" & sClDiv & "</font></strong>" & ", "
Else
OutputString1 = OutputString1 & sClDiv & ", "
End If
rst_ClDiv.MoveNext
Loop
Debug.Print OutputString1
rst_ClDiv.Close
'************************************************* ***************************************
Set rst_ClDiv2 = db.OpenRecordset("ClientDiv-RTI")
rst_ClDiv2.MoveFirst
Do While rst_ClDiv2.EOF <> True
sClDiv2 = rst_ClDiv2.Fields("ABC")
iEMB2 = rst_ClDiv2.Fields("EMB_OOB")
sOOBFix2 = Nz(rst_ClDiv2.Fields("OOB_Fixed"), 0)
If Not IsNull(sOOBFix) Then 'BALANCED BOLD RED
OutputString2 = OutputString2 & "<strong><font color=""Red"">" & sClDiv2 & "</font></strong>" & ", "
ElseIf iEMB = -1 And IsNull(sOOBFix) Then 'OUT OF BALANCE : BLACK
OutputString2 = OutputString2 & "<strong><font color=""black"">" & sClDiv2 & "</font></strong>" & ", "
Else
OutputString2 = OutputString2 & sClDiv2 & ", "
End If
rst_ClDiv2.MoveNext
Loop
Debug.Print OutputString2
rst_ClDiv2.Close
'************************************************* ***************************************
Set rst_Notes = db.OpenRecordset("OOB-Clients")
rst_Notes.MoveFirst
Do While rst_Notes.EOF <> True
sNotes = rst_Notes.Fields("Notes")
OutputString3 = OutputString3 & "<ul> " & sNotes & "</ul> "
rst_Notes.MoveNext
Loop
Debug.Print OutputString3
rst_Notes.Close
'************************************************* ***************************************
With MailOutLook
MailOutLook.BodyFormat = olFormatHTML
'MailOutLook.To = "Hiser, Kristina; Curran, Bruce; Dugan, Sharon; Durkin, Jill; Eccles, Keely; Chapman, Ashley; Foster, Mike; Jenkins, Renaye; Marie, Pamela; McColgan, Susan; Naidoo, Gloria; Ratton, Steve; Reagan, Sheryl; Washington, Kathy; Sanchez, Sherry; Scoggins, Andy; Scroggins, Danielle; Tarone, Stacy; Toth, Kimberly; Lehnortt, Sue; Teibowei, Ebi; Chayra, Melinda; Ruedas, Yolanda; Ribordy, Brook; Levasseur, Maxine; Meyers, Zackery; Beech, Paz Liduvina; Borges, Crystal; Pearson, Christopher; Smith, Shawna; Marrs, Scott; Jezek, John; Garcia, Flavio; Hargis, Shannon; Mitchell, Michelle"
'MailOutLook.CC = "Haag, Michele; Martin, Randy; Reyes, Jane; RTI Application Support; Tausch, Jane; Barefoot, Christine; Briggs, Joe; Dowd, Emily; Gaboda, Stephanie; Gahm, Darrell; Hartner, Robert; Henry, Colleen; Hunter, Grant; Johnston, Stephan; Lorinc, Chris; McGilvery, Dorothy; McMeekin, Daniel; Sarnak, Daniel; Dubois, Linda; Manderack, Jonathan; Mower, Cara; Helwig, Ted; Palish, Kim; Poole, Jamie; Stine, Kurt; Antal, Betsy; Dashiell, Dorothea; Grivner, Edward; Tang, Wing; Kerrigan, Sheila; RTI Practice Management Reporting; Coppola, Greg; Tuma, Michelle; Ruble, Linda; Keels, Tom; Kline, Michael"
MailOutLook.Subject = " RELEASED AND CORRECTED " & Format(Now, "mm/dd/yyyy")
MailOutLook.HTMLBody = "<HTML><BODY>" & _
"<strong><font face=""Arial"" size=""3"" color=""black"">" & "ALL EMCARE CLIENTS ARE AVAILABLE IN EMBILLZ THROUGH " & " " & UCase(Format(DateAdd("m", -1, Date), "mmmm yyyy")) & " FISCAL PERIOD." & "</font></strong><BR/><BR/>" & _
"<strong><font face=""Arial"" size=""2"" color=""black"">" & " EMCARE " & " " & UCase(Format(DateAdd("m", -1, Date), "mmmm yyyy")) & " available in the system: " & "</font></strong>" & _
"<font face=""Arial"" size=""2"" color=""black"">" & OutputString1 & "</font><BR/><BR/>" & _
"<strong><font face=""Arial"" size=""2"" color=""black"">" & " RTI CLIENTS: " & "</font></strong>" & _
"<font face=""Arial"" size=""2"" color=""black"">" & OutputString2 & "</font><BR/><BR/>" & _
"<strong><font face=""Arial"" size=""2"" color=""black"">" & " (BOLD = OUT OF BALANCE) " & "</font></strong>" & "<strong><font face=""Arial"" size=""2"" color=""red"">" & "(RED = CORRECTED) " & "</font></strong><BR/><BR/>" & _
"<strong><u><font face=""Arial"" size=""2"" color=""black"">" & " OUT OF BALANCE CLIENTS (" & Format(DateAdd("m", -1, Date), "mm/yy") & " DOS): " & "</font></u></strong><BR/><BR/>" & _
"<strong><font face=""Arial"" size=""2"" color=""black"">" & " <ul> " & " OutputString3 " & " </ul> " & "</font></strong>" & _
"</BODY></HTML>"
MailOutLook.Display
End With
Exit_Released_Click: ' Label to resume after error.
Exit Sub ' Exit before error handler.
Err_Released_Click: ' Label to jump to on error.
MsgBox Err.Description ' Place error handling here.
Resume Exit_Released_Click ' Pick up again and quit.
End Sub
Any help would be appreciated.
Thanks
Linda
Reply With Quote
Originally Posted by John_G
