CODEWITHSUNDEEP
vbaoutlook
Basil
Basil

Reputation: 1004

Loop through two arrays containing emails

I have two arrays containing email addresses and then another array called emailgroup that contains the name of these two arrays.

I want to create a loop that loops around the emailgroup array to call the other 2 arrays in turn.

I thought for example on the first loop, the "emails" array would be called but the error message says type mismatch.

Can anyone help modify the code below?

 Sub Email_Click()
 Dim myOutlook As Outlook.Application
 Dim objMailMessage As Outlook.MailItem

Dim emails As Variant
Dim moreemails As Variant
Dim emailgroup As Variant


Set myOutlook = Outlook.Application
Set objMailMessage = myOutlook.CreateItem(0)

emails = Array("[email protected]", "[email protected]")
moreemails = Array("[email protected]", "[email protected]")

emailgroup = Array("emails", "moreemails")

For Each i In emailgroup

currentemail = i

With objMailMessage
    .Display
    .To = Join(currentemail, ";")
    .Subject = ""
    .HTMLBody = ""

    .Save
    .Close olPromptForSave
End With
 Next 
End Sub

Upvotes: 0

Views: 219

Answers (2)

Mark Moretto
Mark Moretto

Reputation: 2348

Here's another take:

Private Function CombineArray(arr1() As Variant, arr2() As Variant) As Variant()

Dim tmp() As Variant ' Array to hold values until output
Dim n As Long ' Numeric value to count first array
Dim m As Long ' Numeric value to count second array (`n` could be reused, but this helps keep things separate, IMHO)
Dim idx As Long ' Variable to keep tabs on current index when switching between arrays

' ReDim out temporary array
ReDim tmp(UBound(arr1) + UBound(arr2) + 1)

' Set an indexer to account for switching arrays
idx = 0
For n = 0 To UBound(arr1, 1)
    tmp(idx) = arr1(n)
    idx = idx + 1
Next n

' Iterate over second array.
For m = 0 To UBound(arr2, 1)
    tmp(idx) = arr2(m)
    idx = idx + 1
Next m

' Set results to function output
CombineArray = tmp

End Function

Then a quick test function to check CombineArray() results:

Public Function TestFunc()

Dim output() As Variant
Dim emails() As Variant
Dim moreemails() As Variant

' Set out test email groups
emails() = Array("[email protected]", "[email protected]")
moreemails() = Array("[email protected]", "[email protected]")

' ReDim output array.  Probably not necessary, but helps keep things in check.
ReDim output(UBound(emails) + UBound(moreemails))

' Populate output array with combined array
output = CombineArray(emails, moreemails)

' Test output
Debug.Print Chr(13) & "List of values:"
Dim i As Integer
For i = 0 To UBound(output)
    Debug.Print "-- " & output(i)
Next i

' Join function output
Debug.Print Chr(13) & "The joined values are: " & Join(output, ";")

End Function

Using the immediate window, we get:

Debug.Print TestFunc()

List of values:
-- [email protected]
-- [email protected]
-- [email protected]
-- [email protected]

The joined values are: [email protected];[email protected];[email protected];[email protected]

Upvotes: 2

BigBen
BigBen

Reputation: 50006

emailgroup = Array("emails", "moreemails")

is not the same as an array of arrays:

emailgroup = Array(emails, moreemails)

Then, an array should be iterated over with LBound and UBound:

Dim i as Long
For i = LBound(emailgroup) to UBound(emailgroup)
    ...
    .To = Join(emailgroup(i), ";")
Next

Upvotes: 1

Related Questions