Excel 2003 - Print variable # of lines in email. 2

[Divercem]

I have an excel spreadsheet with 4 columns: GroupName, MemberName, EmailAddress, & FirstName. I have copied code from another working spreadsheet that sends an email, but that code has a specific # of lines. This spreadsheet can have from 1 to 100+ lines, depending on the members of the group. Eventually variable Para_3 (in the code) is gonna exceed the max size of a variable. How do I write all the lines without overflowing the variable? I know the overall code still needs some tweaking.

Here is a snippet of the code:

    Para_1 = "Hey, look over the members of this group and tell me " & _
    "if these people belong in this group."
    DP_Row = 2
    strGroup = "A" & DP_Row
    strMember = "B" & DP_Row
    While Range(strGroup) > "" And Range(strMember) > ""
        strGroup = "A" & DP_Row
        GroupName = Range(strGroup)
        strMember = "B" & DP_Row
        StrRecip = "C" & DP_Row
        strName = "D" & DP_Row
        Greeting = Range(strName) & "," & vbCrLf & vbCrLf
        Para_2 = GroupName
        Para_3 = vbTab & Range(strMember) & vbCrLf
        strNextRow = "A" & DP_Row + 1
        While Range(strNextRow) = ""
            strMember = "B" & DP_Row
            strNextMem = "B" & DP_Row + 1
            If Range(strNextMem) = "" Then
                Wend      ' Not sure if this will compile, I just wanna end the while statement
            End If
            Para_3 = Para_3 & vbTab & Range(strMember) & vbCrLf
            DP_Row = DP_Row + 1
        Wend

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

        With Destwb
            ' .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            ' On Error Resume Next
            With OutMail
                .Display 'or use .send
                .To = StrRecip
                .CC = ""
                .BCC = ""
                .Subject = "Group - " & GroupName
                .Body = Greeting & Para_1 & Closing & Signature ' & Para_2 & Para_3
                ' .Send
            End With
            On Error GoTo 0
        End With
SkipThisRow:
        DP_Row = DP_Row + 1
        strGroup = "A" & DP_Row
    Wend

Here is an example of the spreadsheet:

[tt]
GroupName MemberName Email FirstName
Group1 Tom Ghall@a.com George
Bill
Tommy
Group2 Angie Adell@a.com Angie
Bill
Dan
Fred
LastGuy
[/tt]

Thanks

 

[ClulessChris]

Why is "Para_3 gonna exceed the max size of a variable"?
What is this dimentioned as?
Try 'Diming' as Long

Don't count the days, Make the days count.

 

[ClulessChris]

Sorry i didn't read the question correctly.
Can you please sum up what issue your having?
Don't need to see the code that's working just the bit you're having trouble with.

Don't count the days, Make the days count.

 

[SkipVought]

" If Range(strNextMem) = "" Then
Wend ' Not sure if this will compile, I just wanna end the while statement
"
I'd use Do While...Loop

Do While(Range(strNextRow) = "")
...
            If Range(strNextMem) = "" Then [b]Exit Do[/b]
...
Loop

Where is the row count exceeding your percieved limit?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[ClulessChris]

Or For ...To...Next

For X = 3 To Range("A65536").End(xlUp).Row

next

you then have a ready made counter to index the worksheet rows.

Don't count the days, Make the days count.

 

[SkipVought]

Personally, I'd use MS Query. Almost no VBA.

faq68-5829

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Divercem]

Hmmm...my perceived problem was Para_3 exceeding 1024 characters way before I got to the end of the members list.

The past problems I've had, I have beat my head against the wall for at least a day before posting. I thought I'd be *smart* and save myself some of that bruising so I posted what I thought was going to be a problem.

Lesson learned...if it aint broke, don't ask someone to help fix it.

Although I did take Skip's advice on the Do While statement. Sorry about the premature postulation on the limitations.

Chris & Skip thanks for your replies...