Compiling Text String by Row in Excel

[Rzrbkpk]

I'm trying to create a basic report from a worksheet that has more detail than needed for the recipient of the report. The report is then sent as the body of the e-mail(not an attachment). Here is a generic view of my data (first row is headers):

Job Reference Task1 Task2 Task3 Task4
1234 AA Done Done Done Done
5678 BB 50% Done Done Done
9101 CC Done Done Done Done

Here is my code:

Sub Button1_Click()

Dim myRange As Range, r As Range
Dim aWs As Worksheet
Dim OutApp As Object
Dim OutMail As Object
Dim StrBody As String


Set myRange = Sheets("Data").Range("A2:A200")


For Each r In myRange
If Not IsEmpty(r) Then
StrBody = StrBody & _
"=====================================" & "<br>" & _
"Job:" & " " & r.Value & "<br>" & _
"=====================================" & "<br><br><br>"


End If
Next r

With Application
.EnableEvents = False
.ScreenUpdating = False
End With


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "user@domain.com"
.CC = ""
.BCC = ""
.Subject = "Insert Subject Here"
.HTMLBody = StrBody
.Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing



End Sub


The code is working as it is above, but what I need to do is conditionaly compile the rest of the text string with data from the other columns. So, Task1/Task2 are one category on my basic report and Task 3/Task 4 are another category. The condition would look like(not literal syntax):

If Task1 or Task2 <> "Done" Then
string = "Incomplete"
Else String = "Complete"

What I'm aiming for is a final report in an e-mail that looks like this:

=====================
Job: 1234
Ref: AA
Cat1: Complete
Cat2: Complete
=====================

=====================
Job: 5678
Ref: BB
Cat1: Incomplete
Cat2: Complete
=====================

 

[SkipVought]

hi,

   If Task1 = "Done" AND Task2 = "Done" Then
      string = "Cat1: Complete" 
   Else 
      String = "Cat1: Incomplete"
   end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rzrbkpk]

Skip,
Thanks for the assistance. I was looking more for how it would fit into string within the range loop. I've tried a few approaches and none seem to work.

 

[SkipVought]

Pleae post the code that you have tried.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rzrbkpk]

Issue resolved. I was trying to put the conditional statement in the string so that it processed with each loop. This didn't work. So I came up with the following and it's working like a charm.

For Each r In myRange
    If Not IsEmpty(r) Then
    
    'Set Category1 String Value
        If r.Columns(3).Value = Empty Or r.Columns(3).Value = "Searches Running" Then Str2 = "Incomplete" Else
        If r.Columns(4).Value = Empty Or r.Columns(4).Value = "Searches Running" Then Str2 = "Incomplete" Else
     'Set Category2 String Value
                If r.Columns(3).Value = Empty Or r.Columns(3).Value = "Searches Running" Then Str3 = "Incomplete" Else
        If r.Columns(4).Value = Empty Or r.Columns(4).Value = "Searches Running" Then Str3 = "Incomplete" Else
        
    StrBody = StrBody & _
              "=====================================" & "<br>" & _
              "Job:" & " " & r.Columns(1) & "<br>" & _
              "Category1:" & " " & Str2 & "<br>" & _
              "Category2:" & " " & Str3 & "<br>" & _
                            "=====================================" & "<br><br><br>"
                
                         
    End If
    Next r