store loop results as increment variable?

[JCBuckeye]

Hello -

I'm using a Do Until loop that is basically writing the body text of an email for me. It's working great, but I need to add one more thing.

It is storing the Body text that it is creating as a BodyText string within the loop. Works good. However, I really need the Body text variable to increment each time the loop is ran, or the loop becomes pointless.

Example:
User enters 5 form entries.
User hits 'Send Email'
VBA runs code to build email for each entry submitted
VBA sends the full body text of all 5 form entries to Lotus Notes (-_-)

Here is the code I'm using, slightly modified for better viewing:

    'Perform a Do Until loop to build the email with multiple policies
 
    Dim r As DAO.Recordset
    Dim rcount As Integer
    Set r = CurrentDb.OpenRecordset("SELECT * FROM " & Me.User.Value & "")
    r.MoveLast
    rcount = r.RecordCount
 
        If rcount > 0 = True Then
        r.MoveFirst
        Do Until r.EOF = True
 
            'Save policy number and Body into a variable
            Dim sPolicy As String
            sPolicy = r!Policy
 
            'Build body text strings for each policy
            'Me.RLCEmail.Value = DLookup("RLCEmail", "tblRLCrelationships", "RLC='" & Me.RLC.Value & "'")
 
 
            Dim sNEW As String
            Dim sAOR As String
            Dim sSDR As String
            Dim sPREM As String
            Dim sALR As String
            Dim sRM As String
            Dim sRMDESC As String
            Dim sAPS As String
            Dim APSDESC As String
            Dim sM1035 As String
            Dim sN1035 As String
            Dim sMNAIC As String
            Dim sNNAIC As String
            Dim sRDESC As String
            Dim sNAN As String
            Dim sNANDESC As String
            Dim sMISC1 As String
            Dim sMISC2 As String
            Dim sMISC3 As String
            Dim sBWP As String
            Dim sCOD As String
 
[BUILDING OF BODY TEXT REMOVED FOR BREVITY]
 
           Dim BodyText As String
 
            BodyText = sNEW & sAOR & sSDR & sPREM & sALR & sRM & sRMDESC & sAPS & APSDESC & sM1035 & _
            sN1035 & sMNAIC & sNNAIC & sRDESC & sNAN & sNANDESC & sMISC1 & sMISC2 & sMISC3 & _
            sBWP & sCOD
 
            'Move to the next record. Don't ever forget to do this.
            r.MoveNext
Loop
    Else
 
    End If
 
    r.Close
    Set r = Nothing

After this, there is code that generates email. The probelm I'm having is that the BodyText variable is only remembering one loop. I need a way (array?) to name BodyText within in the loop so that each form entry text that is built has a different name and can be combined, i.e. Body1, Body2, Body3.

EDIT: I use the email code to call/instert 'BodyText'. So I would want to define BodyText = Body1 & Body2 & Body3 & Body4, etc. so that each record that went through the loop appears in the email.

Thanks!

 

[pbaldy]

It would be more common to have your code to send the email at the end of the loop. Alternatively, in a function that accepts the body as an argument. Then you'd call that function at the end of the loop. Either would be more dynamic, allowing 1 email or 100.

 

[JCBuckeye]

It would be more common to have your code to send the email at the end of the loop. Alternatively, in a function that accepts the body as an argument. Then you'd call that function at the end of the loop. Either would be more dynamic, allowing 1 email or 100.

I think a function may help me here, but sending the email in the loop will not accomplish what I want. I'm not trying to send multiple emails. I'm trying to send multiple bodies of text into 1 email. And since I'm working with Lotus Notes, this is the best solution I have come to.

The point is for 10 business follow ups to go in 1 email... instead of 10 follow ups in 10 separate emails, but also formatted so they are easily separated (hence the large amount of strings I'm saving to bodytext). I have a feeling this would be easily accomplished in Outlook, but my company uses Lotus Notes.

Thanks!

 

[JCBuckeye]

Actually, a function won't help me much because I still don't know how to increment a variable so that I can store the results of each bodytext that the loop builds.

 

[pbaldy]

Presumably you would just concatenate these multiple body variables into the email? I still wouldn't use them, I'd just keep adding on to this one.

BodyText = BodyText & vbCrLf & vbCrLf & sNew &...

Wouldn't that accomplish the same goal?

 

[JCBuckeye]

Hahahahahahaha.

:banghead: -_- . The sad part is that is EXACTLY how I tested my theory to work before I re-wrote the email build for multiple policies. I previously had it set to 1 form entry = 1 policy. But of course, the requestor's got needy and asked for multiple entries.

My VBA talk buddy is out this week. Sometimes once you get an idea in your head, you forget the simple way of doing things. And thats when you just need someone to go... 'Hey Stupid....'

Thanks so much pbaldy!

 

[pbaldy]

I know what you mean. My brain is always locking in on things. Glad we got it sorted out.