ADJUSTING RANGE FROM COLUMN A THRU C...(NOT JUST COLUMN A)

[JuicyMusic]

Hello everyone, I have a code that works very well, but I need to tweak it a little bit and would like to learn how to adjust a particular section.

IMPORTANT!! I've read the Mr. Excel rules regarding cut/paste but my company doesn't allow me to download anything. I am so sorry. I will just type the section of code that I am referring to. I hope that will be acceptable.

Here is what this code does:

1) Generates 2 different type of emails based on two texts in column L: "Expired" and "Expiring Soon"
2) It brings the data (insurance company name) from column A of the spreadsheet onto the body of the email. One email for all the rows that show "Expired", and another email for all the rows that show "Expiring Soon".

I NEED THE DATA THAT SHOWS UP ON THE EMAIL BODY TO BE FROM COLUMN A, B, AND C OF EACH ROW. WHICH WOULD BE THE INSURANCE CO. NAME (A), THE TYPE OF INSURANCE COVERAGE (B), AND THE ACCOUNT NUMBER (D), NOT JUST COLUMN A.

I HOPE THAT I HAVE CAPTURED THE CORRECT LINES OF CODE THAT YOU NEED TO SEE.


THIS LINE?
lr = ws.Range("A" & Rows.Count).End(xlUp).Row


OR THIS SECTION?
If Status = "Expiring Soon" Then
Instrument1 = Instrument1 & ws.Range("A" & i).Value & ", "
counter1 = counter1 + 1
End If
If Status = "Expired" Then
Instrument2 = Instrument2 & ws.Range("A" & i).Value & ", "
counter2 = counter2 + 1
End If


Thank you so much in advance, Juicy

 

[MARK858]

Can you not just concatenate your cells like the example below?

    If Status = "Expired" Then
        Instrument2 = Instrument2 & ws.Range("A" & i).Value & " " & ws.Range("B" & i).Value & " " & ws.Range("C" & i).Value & " ,"
        counter2 = counter2 + 1
    End If

 

[JuicyMusic]

Mark858, Thank you so much for responding to my post. I LOVE IT! I actually thought to concatenate but wasn't sure how to write it In VBA.
I see now that it is the same logic if I was going to write a formula to combine cells.

Here is what it looks like now. I was able to add a Return (vbNewLine) myself and it worked. I inserted an image of the email. The only problem I see is that the word "The" is the first word on the email now, instead of the text saying "The Registration/Coverage/License is expired", it says that but less the word "The. Could you help me put the word "The" where should be?

For i = 2 To lr
  Status = ws.Range("L" & i).Value
    If Status = "Expiring Soon" Then
Instrument1 = Instrument1 & ws.Range("A" & i).Value & ", " & ws.Range("B" & i).Value & ", " & ws.Range("C" & i).Value & "|" & vbNewLine
counter1 = counter1 + 1
End If
If Status = "Expired" Then
If Status = "Expired" Then
Instrument2 = Instrument2 & ws.Range("A" & i).Value & ", " & ws.Range("B" & i).Value & ", " & ws.Range("C" & i).Value & "|" & vbNewLine
counter2 = counter2 + 1
End If
counter2 = counter2 + 1
    End If