Conditional formatting text in Outlook email generated from Access DB VBA

[LindaRuble]

Hello,



I can generate an email by clicking on a button on a MS Access Report. In the body of the resulting Outlook email you have formatted text. I have also included an array of values which are displayed in this email but I need to highlight certain items in the array in red based on whether or not another column for that record is checked. Here is an image of the email. Notice that I have circled "ZWO" and that it is red. I need for this to happen programmically. Hope you can help. Thanks Linda

[rpeare]

How are you currently generating this email, having it generate the body then manually changing the color yourself before sending?

I'm assuming you're using the sendobject method to generate the email?

If you are using sendobject I don't think you can do color formatting, you would have to blend html formatting (which I've never done) but here's an example using ASP which is similar to the code you'd use in a vb script

http://www.codeproject.com/Articles/...matted-Text-Ed

I've never done partial text formatting on email, I usually print reports to pdf's and email those out instead.

most of the stuff I looked at for your question was built around building an HTML based email rather than the sendobject command.

[LindaRuble]

Hello rpeare,

Here is an image of table "RTIClientTracker" in Access with my Data. When what is in column "Client_Division" is associated with column "EMB_OOB" that has a checked value, I would like to have that record appear "RED" in the generated Outlook email. The list of three lettered value come from an Array which in turn comes from a SQL statement/ Querie. When the value in column "EMB_OOB" is not checked, then I would want the associated three letter value in column "Client_Division" to be the default color black.



I am currently generating the Outlook email by clicking a button on an Access report using an "On Click" event to trigger the following code:

(Note: I can already make the code red conditionally. I know how to use HTML to do so. I am just having a problem understanding how to access column "EMB_OOB" in my table "RTIClientTracker" and associate it with the appropriate "Client_Division" so that it knows that the clients with the checked values should be colored red in my Outlook email.)

Thanks for your help

Linda

************************************************** *************************************************

Private Sub Released_Click()
Dim mess_body As String
Dim appOutLook As Outlook.Application
'Dim appOutLook As Object
Dim MailOutLook As Outlook.MailItem
'Dim MailOutLook As Object
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

'************************************************* ****

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim Count As Integer
Dim AnArray()
Dim i As Long
Dim ABCString As String
' Set up the database and query connections
Set db = CurrentDb()
Set rs = db.OpenRecordset("ClientDiv-RTI")
Count = 0
ReDim Preserve AnArray(0)


' Fill the array.
Do Until rs.EOF
' Fill the array row with the last name.
' NOTE: In version 2.0, type a space in [Last Name].
AnArray(Count) = rs![ABC]
' Increase the number of elements in the array
' by one to accommodate the next record.
ReDim Preserve AnArray(UBound(AnArray) + 1)
Count = Count + 1
rs.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve AnArray(UBound(AnArray) - 1)
rs.Close
' View the array contents.
For i = 0 To Count - 1
If [i] = Count - 1 Then
ABCString = ABCString + AnArray(i)
Else
ABCString = ABCString + AnArray(i) & ","
End If
Next i

'************************************************* ****

Dim db2 As DAO.Database
Dim rs2 As DAO.Recordset

Dim Count2 As Integer
Dim AnArray2()
Dim ii As Long
Dim ABCString2 As String
' Set up the database and query connections
Set db2 = CurrentDb()
Set rs2 = db2.OpenRecordset("ClientDiv-EMCARE")
Count = 0
ReDim Preserve AnArray2(0)

' Fill the array.
Do Until rs2.EOF
' Fill the array row with the last name.
' NOTE: In version 2.0, type a space in [Last Name].
AnArray2(Count2) = rs2![ABC2]
' Increase the number of elements in the array
' by one to accommodate the next record.
ReDim Preserve AnArray2(UBound(AnArray2) + 1)
Count2 = Count2 + 1
rs2.MoveNext
Loop
' Remove the remaining empty array row.
ReDim Preserve AnArray2(UBound(AnArray2) - 1)
rs2.Close
' View the array contents.
For ii = 0 To Count2 - 1
If [ii] = Count2 - 1 Then
ABCString2 = ABCString2 + "<strong><font color=""red"">" & AnArray2(ii) & "</font></strong>"
Else
ABCString2 = ABCString2 + AnArray2(ii) & ","
End If
Next ii


'************************************************* ****


Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
'MailOutLook.BodyFormat = olFormatRichText
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")
'.HTMLBody = Me.Mess_Text
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"">" & ABCString2 & "</font><BR/><BR/>" & _
"<strong><font face=""Arial"" size=""2"" color=""black"">" & " RTI CLIENTS: " & "</font></strong>" & _
"<font face=""Arial"" size=""2"" color=""black"">" & ABCString & "</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>" & _
"</BODY></HTML>"



'.Send
MailOutLook.Display
End With
'MsgBox MailOutLook.Body
Exit Sub
email_error:
MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
Resume Error_out
Error_out:
End Sub

************************************************** ************************************************** **

[rpeare]

Ok so this is cycling through a query named ClientDiv-RTI, extracting the field [ABC] (what is this in reality, a text field, the table's primary key? what? I assume it's just a straight text value?), adding that [ABC] Value to an array, then building a string out of the contents of that array. (by the way you don't need to build an array to do this) Then you move on to do a very similar thing with a query named ClientDiv-EMCare. I have the same set of questions as the previous recordset.

Are these both subsets of the RTIClientTracker table? you just have the separated in a Query object? Or do those queries you're using as recordsets reference other tables as well? Or are you trying to compare the same array item on two different tables. Let me give you an example of just cycling through your RTIClientTracker table and building the string. Then we can go from there:

These are the things I'm assuming.
1. You want to create a section of your report for each Division
2. You want to create an HTML string that doesn't do anything special with text if the EMB_OOB field is ANYTHING BUT Yes (-1) AND uses the formatting code to shade it red if EMB_OOB IS Yes (-1)

Dim db As Database
Dim rst_Divs As Recordset
Dim rst_ClDiv As Recordset
Dim sSQL As String
Dim sDivReg As String
Dim sClDiv As String
Dim iEMB As Integer
Dim OutputString As String

Set db = CurrentDb
sSQL = "SELECT Division_Region FROM RTIClientTracker GROUP BY Division_Region ORDER BY Division_Region"

Set rst_Divs = db.OpenRecordset(sSQL)
rst_Divs.MoveFirst

Do While rst_Divs.EOF <> True
    sDivReg = rst_Divs.Fields("Division_Region")
    
    sSQL = "SELECT Client_Division, EMB_OOB FROM RTIClientTracker WHERE (Division_Region = '" & sDivReg & "')"
    Set rst_ClDiv = db.OpenRecordset(sSQL)
    rst_ClDiv.MoveFirst
    
    Do While rst_ClDiv.EOF <> True
        sClDiv = rst_ClDiv.Fields("Client_Division")
        iEMB = rst_ClDiv.Fields("EMB_OOB")
        If iEMB = -1 Then
            OutputString = OutputString & "<strong><font color=""red"">" & sClDiv & "," & "</font></strong>"
        Else
            OutputString = OutputString & sClDiv & ","
        End If
        rst_ClDiv.MoveNext
    Loop
Debug.Print OutputString
OutputString = ""
    
    rst_ClDiv.Close
    rst_Divs.MoveNext
Loop
rst_Divs.Close

it's not exactly what you want but without knowing the contents of the two queries you're referencing this is the best I can do. I couldn't find the reference you're using for the outlook objects easily (I've forgotten the correct name) but let me know if this helps.

[rpeare]

Ok I found the reference an this is what I did for code:

Dim db As Database
Dim rst_Divs As Recordset
Dim rst_ClDiv As Recordset
Dim sSQL As String
Dim sDivReg As String
Dim sClDiv As String
Dim iEMB As Integer
Dim OutputString 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 appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set db = CurrentDb
sSQL = "SELECT Division_Region FROM RTIClientTracker GROUP BY Division_Region ORDER BY Division_Region"

Set rst_Divs = db.OpenRecordset(sSQL)
rst_Divs.MoveFirst

Do While rst_Divs.EOF <> True
    sDivReg = rst_Divs.Fields("Division_Region")
    
    sSQL = "SELECT Client_Division, EMB_OOB FROM RTIClientTracker WHERE (Division_Region = '" & sDivReg & "')"
    Set rst_ClDiv = db.OpenRecordset(sSQL)
    rst_ClDiv.MoveFirst
    
    OutputString = OutputString & "Client Division " & sDivReg & "<BR/><BR/>"
    Do While rst_ClDiv.EOF <> True
        sClDiv = rst_ClDiv.Fields("Client_Division")
        iEMB = rst_ClDiv.Fields("EMB_OOB")
        If iEMB = -1 Then
            OutputString = OutputString & "<strong><font color=""red"">" & sClDiv & "," & "</font></strong>"
        Else
            OutputString = OutputString & sClDiv & ","
        End If
        rst_ClDiv.MoveNext
    Loop

Debug.Print OutputString
    OutputString = OutputString & "<BR/><BR/>"
    rst_ClDiv.Close
    rst_Divs.MoveNext
Loop
rst_Divs.Close

OutputString = OutputString & "CLOSING COMMENTS GO HERE"

With MailOutLook
    MailOutLook.BodyFormat = olFormatHTML
    MailOutLook.To = "Tester@Tester.com"
    MailOutLook.CC = "Tester2@tester.com"
    MailOutLook.Subject = " RELEASED AND CORRECTED " & Format(Now, "mm/dd/yyyy")
    MailOutLook.HTMLBody = "<HTML><BODY>" & OutputString & "<HTML><BODY>"
    MailOutLook.Display
End With

You can see that you can build the body of the message as you go rather than trying to compile everything into arrays and build it afterward.

[LindaRuble]

Hello rpeare,

First of all I would like to thank you for your help with my conditional formatting question. The code that you gave me provided me with the information I needed to change the color of the text in my Outlook email when I clicked a button on my Access report.

Now I have another issue (It never ends). I want to formatt the same set of text for also another condition as well. In other words I want Clients who have OOBs (Out Of Balances) in bolded Black font and corrected Clients to show up as bolded Red font. The rest of the clients will show as a regular weight font.

I am having issues with what appears in red in my code. I want to satisfy two conditions, I want to check to see if the check box is checked and to see if the OOB_FIXED column is filled or not. iEMB = -1 And sOOBFix Is Not Null . If the OOB_FIXED column is filled with a date and not NULL and the EMB_OOB column is checked, I want the text to appear bold and red in the Outlook Email.

Note: I have removed the SQL statements from the VBA code since it references an Access Query Objects instead. I will include it below for your information. It all works.

Thanks again for your help!

Linda



This is ClientDiv-EMCARE

SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC2, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.Division_Region)='West') OR ((RTIClientTracker.Division_Region)='EPS') OR ((RTIClientTracker.Division_Region)='North') OR ((RTIClientTracker.Division_Region)='South')) AND ((RTIClientTracker.Cut)>=Date()-1)
ORDER BY (Mid(ClientDiv.Client_Division,1,3));


This is ClientDiv-RTI

SELECT DISTINCT (Mid(ClientDiv.Client_Division,1,3)) AS ABC, RTIClientTracker.EMB_OOB, RTIClientTracker.OOB_Fixed
FROM ClientDiv INNER JOIN RTIClientTracker ON ClientDiv.ID = RTIClientTracker.Client_Division
WHERE (((RTIClientTracker.Division_Region)='RTI') AND ((RTIClientTracker.Cut)>=Date()-1))
ORDER BY (Mid(ClientDiv.Client_Division,1,3));



Here is My reworked code:

************************************************** ***********************************

Private Sub Released_Click()
Dim db As Database
Dim rst_ClDiv As Recordset
Dim rst_ClDiv2 As Recordset
Dim sClDiv As String
Dim sClDiv2 As String
Dim iEMB As Integer
Dim iEMB2 As Integer
Dim sOOBFix As Variant
Dim sOOBFix2 As Variant
Dim Ns As String
Dim OutputString1 As String
Dim OutputString2 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 iEMB = -1 Then 'OUT OF BALANCE : BLACK
OutputString1 = OutputString1 & "<strong><font color=""black"">" & sClDiv & "</font></strong>" & ", "
ElseIf iEMB = -1 And sOOBFix Is Not Null Then 'BALANCED : RED
OutputString1 = OutputString1 & "<strong><font color=""Red"">" & 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 iEMB2 = -1 Then
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

'************************************************* ***************************************


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>" & _
"</BODY></HTML>"

MailOutLook.Display
End With

End Sub

************************************************** ***************************************

Ok I found the reference an this is what I did for code:

Dim db As Database
Dim rst_Divs As Recordset
Dim rst_ClDiv As Recordset
Dim sSQL As String
Dim sDivReg As String
Dim sClDiv As String
Dim iEMB As Integer
Dim OutputString 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 appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set db = CurrentDb
sSQL = "SELECT Division_Region FROM RTIClientTracker GROUP BY Division_Region ORDER BY Division_Region"

Set rst_Divs = db.OpenRecordset(sSQL)
rst_Divs.MoveFirst

Do While rst_Divs.EOF <> True
    sDivReg = rst_Divs.Fields("Division_Region")
    
    sSQL = "SELECT Client_Division, EMB_OOB FROM RTIClientTracker WHERE (Division_Region = '" & sDivReg & "')"
    Set rst_ClDiv = db.OpenRecordset(sSQL)
    rst_ClDiv.MoveFirst
    
    OutputString = OutputString & "Client Division " & sDivReg & "<BR/><BR/>"
    Do While rst_ClDiv.EOF <> True
        sClDiv = rst_ClDiv.Fields("Client_Division")
        iEMB = rst_ClDiv.Fields("EMB_OOB")
        If iEMB = -1 Then
            OutputString = OutputString & "<strong><font color=""red"">" & sClDiv & "," & "</font></strong>"
        Else
            OutputString = OutputString & sClDiv & ","
        End If
        rst_ClDiv.MoveNext
    Loop

Debug.Print OutputString
    OutputString = OutputString & "<BR/><BR/>"
    rst_ClDiv.Close
    rst_Divs.MoveNext
Loop
rst_Divs.Close

OutputString = OutputString & "CLOSING COMMENTS GO HERE"

With MailOutLook
    MailOutLook.BodyFormat = olFormatHTML
    MailOutLook.To = "Tester@Tester.com"
    MailOutLook.CC = "Tester2@tester.com"
    MailOutLook.Subject = " RELEASED AND CORRECTED " & Format(Now, "mm/dd/yyyy")
    MailOutLook.HTMLBody = "<HTML><BODY>" & OutputString & "<HTML><BODY>"
    MailOutLook.Display
End With

You can see that you can build the body of the message as you go rather than trying to compile everything into arrays and build it afterward.

[rpeare]

You are checking for three conditions

If iEMB = -1 Then  'OUT OF BALANCE : BLACK
                     OutputString1 = OutputString1 &  "<strong><font color=""black"">" & sClDiv &  "</font></strong>" & ", "
            ElseIf iEMB = -1 And sOOBFix Is Not Null Then  'BALANCED : RED
                     OutputString1 = OutputString1 &  "<strong><font color=""Red"">" & sClDiv &  "</font></strong>" & ", "
            Else
                    'Unmatched situation code here
            End If

Your code is only testing for two possibilities and they're not mutually exclusive, in other words nothing will ever meet your

Elseif iemb = -1 and soobfix is not null because everything with the iemb being -1 will be picked up in the first portion of your IF string

Instead you'd want something like:

If iEMB = -1 AND isnull(soobfix) Then  'OUT OF BALANCE BOLD BLACK
                     OutputString1 = OutputString1 &  "<strong><font color=""black"">" & sClDiv &  "</font></strong>" & ", "
            ElseIf iEMB = -1 And Not Isnull(sOOBFix) Then  'BALANCED BOLD RED
                     OutputString1 = OutputString1 &  "<strong><font color=""Red"">" & sClDiv &  "</font></strong>" & ", "
            Else 'OTHERWISE : Normal Text
                     outputstring1 = outputstring1 & scldiv & ","
            End If

This is saying

if iEMB is True or -1 and sOOBFix is null make your text bold black
if iEMB is True or -1 and sOOBFix is NOT null make your text bold red
Otherwise, just use normal text.

[LindaRuble]

Hello rpeare,

Thank you again for your help. I tried your code and though it still did not work it just needed a little tweeking and rearranging.

Here is what finally worked for me:


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

Again thanks a million!

Linda