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
I extract the first group of emails using
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.
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
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
Code:
RPosition = InStr(RPos, strEmailAddress, "; ") - 1
Code:
EmailAddress = Left(strEmailAddress, RPosition)
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.
Code:
nextEmailAddress = Right(EmailAddress, RPosition)
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
Code:
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