Getting values into an array 1

[Gavona]

I have developed a routine that splits a workbook and emails each of the newly created workbooks to a recipient.
I have a range within ThisWorkbook ("emails&quot that contains an identifier and in the adjacent column, the email addresses. The email addresses would look like this for single recipients: jsmith and like this for multiple recipients: jsmith;jjones;another

My VBA code includes

sendto = r.Offset(0, 1).Value
EmailTitle = "Budget Monitoring Return " + r.Value
        
'      need to trap invalid email addresses
On Error GoTo ErrorHandler
ActiveWorkbook.sendmail Recipients:=sendto, _
Subject:=EmailTitle, RETURNRECEIPT:=True
On Error GoTo 0
'      reset workbook ready for next loop
ActiveWorkbook.Close
Next r

This works fine except where I want to email to multiple recipients.I think I need something like:
SendTo = Array(r.Offset(0, 1).Value)
but this doesn't work I suspect because I am not properly populating the array.
Please help.

 

[Zathras]

1. Recipients needs to be a Variant.
2. A Variant can contain either a single string or an array.
3. You need to convert the delimited list into an array.

The following code illustrates one way to do that (You don't need the For loop, it is just there to do a MsgBox on each address to demo that sendto is in fact an array):

Option Explicit
Sub test()
Dim r As Range
Dim i As Integer
Dim sendto As Variant
  For Each r In Selection
    sendto = MakeArrayOf(r.Offset(0, 1).Value, ";")
    For i = 0 To UBound(sendto)
      MsgBox sendto(i)
    Next i
  Next r
End Sub

Function MakeArrayOf(StringList As String, _
                 Delimiter As String) As Variant
Dim sWork As String
Dim A() As String
Dim nIndex As Integer
Dim nPos As Integer
  sWork = StringList
  ReDim A(1)
  nIndex = 0
  nPos = InStr(sWork, Delimiter)
  While nPos > 0
    ReDim Preserve A(nIndex)
    A(nIndex) = Left(sWork, nPos - 1)
    sWork = Mid(sWork, nPos + 1, 999)
    nIndex = nIndex + 1
    nPos = InStr(sWork, Delimiter)
  Wend
  If Len(sWork) > 1 Then
    ReDim Preserve A(nIndex)
    A(nIndex) = sWork
  End If
  MakeArrayOf = A
End Function

Hope this helps

 

[Gavona]

Thanks Zathras,

Works perfectly - a star. I'll study it further later and am sure this example will help me to understand both arrays and VBA programming better.

Brilliant.

Gavona