Excel 2003 - Print variable # of lines in email.

[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

 

[SkipVought]

Please post VBA code questions in Forum707.

Skip,

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