Unable to re-use calculated values in VBA (1 Viewer)

[IanO]

The Problem:
Once I use a calculated value in a formula, I appear not to be able to reuse it.
Access 2010 and Win 7
I am using VBA to produce a string of email addresses separated by semi-colons. This string ends up with about 250 email addresses, too long to use in the associated Outlook.

All values are Dim..ed as strings or integer as appropriate.

Outlook requires email addresses extracted directly from a Table but not the output of a query.
To solve this question, I use a "Create Table Query" to produce the temporary Table called "EmailCreateTable"

I next create a string called strEmailAddress of all the email addresses by looping through the records, adding a semi-colon each time until EOF

I break up the string into approximately 25 email addresses by finding the position "Rposition" of the semi-colon with a seed value RPos = 400

RPosition = InStr(RPos, strEmailAddress, "; ") - 1

I extract the first group of emails using

EmailAddress = Left(strEmailAddress, RPosition)

This is all plugged into Outlook using Set appOutLook etc. and the Email is created correctly.

Here the problem occurs. I need to move to the next group of email addresses.
I try and trim off the already used email addresses ready to loop back for the next email using EmailAddress and RPosition once more.

nextEmailAddress = Right(EmailAddress, RPosition)

The problem is that RPosition does not appear to have a value this time.

Debug.Print gives me the correct value of RPosition. If I enter that integer manually, the string calculates correctly.

Is there a way to reuse calculated values further down the VBA code or am I missing something?
For the record, the code up to date is here. Once I solve this problem I will continue using a loop

Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Dim strPath As String
    Dim strFilter As String
    Dim strFile As String
    Dim rst As DAO.Recordset
    Dim EmailAddress As String
    Dim LPosition As Integer
    Dim RPosition As Integer
    Dim RPos As Integer
    Dim nextEmailAddress As String
    
    
'Create a table with selected addresses
'using a Create Table Query called ETransferAddress
    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("ETransferAddress")
    DoCmd.SetWarnings True

'Look in table and concatenate email addresses
Set rst = CurrentDb.OpenRecordset("EmailCreateTable")

Do Until rst.EOF

  strEmailAddress = strEmailAddress & rst("EmailAddress") & "; "
  rst.MoveNext
Loop
   strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
'Cleanup
rst.Close
Set rst = Nothing
'strEmailAddress now contains all addresses in table

'Break into about 22 email addresses at a time
RPos = 400    'Set initial value before trimming string exactly

RPosition = InStr(RPos, strEmailAddress, "; ") - 1 'Trim exactly to end of address

EmailAddress = Left(strEmailAddress, RPosition)

'Replace VB Carriage return with HTML
    MessageNew = Replace(Message, vbCrLf, "<br><br>")

'Define file attachment
    strFile = ToAttach

'Start Outlook
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(olMailItem)

        With MailOutLook
            .BodyFormat = olFormatRichText
            .To = EmailAddress
            '.cc = ""
            '.bcc = ""
            .Subject = Subject
            .HTMLBody = MessageNew
            .Attachments.Add (strFile)

            .Display    'Comment out .Send if using this line
        End With

     nextEmailAddress = Right(EmailAddress, RPosition)
  Debug.Print

 

[pbaldy]

I think the variable is fine. You've already cut the string variable here:

EmailAddress = Left(strEmailAddress, RPosition)

Did you perhaps mean to use strEmailAddress when doing the next variable? I might use the Split() function and a loop to get email addresses.

 

[IanO]

Thank you.
One can stare at the problem and not see what is happening.
I totally missed the point and assumed a wrong diagnosis.
Split() is interesting. I guess I would then need to count off the number of addresses then concatenate them. Maybe another day.

Now back to the next step of looping through until all groups are created.
The main challenge will be to stop at EOF and not keep going for ever.

 

[pbaldy]

Here's another thought. You're already looping to get the addresses. You can move the email code out to a function that takes the addresses as an input variable. Add a counter variable inside that loop and call the email function every 22.