Array in For Each - VBA

Question

I have a VBA code that performs a loop in a specific Outlook Folder, message by message.

I’d like to feed an array with the sender of each message(objItem.SenderEmailAddress), the date(objItem.ReceivedTime) and the subject(objItem.Subject).

I don’t have much experience with arrays, so would you like to get some suggestions.

Dim objItem As Variant

Set colItems = Fldr.Items

For Each objItem In colItems

    Feed the Array here                      

Next

There are static arrays, where you know the size at compile time. There are dynamic arrays where you allocate the size before assigning values and there are collections which dynamically adjust the size. What do you want? — John Alexiou
– John Alexiou, Commented Sep 22, 2015 at 16:01
well, considering I don't know the total of messages, I think the most appropriate would be dynamic. Not sure how to work with them in this case. — Richard
– Richard, Commented Sep 22, 2015 at 16:05
It kind of depends on what you want to do with the arrays afterwards. Also do you want multiple arrays for each information, or one array of MessageItem which contains all the information you need for each email? — John Alexiou
– John Alexiou, Commented Sep 22, 2015 at 16:08
I think one array containing all the info would be the best option. Could you provide some exemples over my code? I appreciate. — Richard
– Richard, Commented Sep 22, 2015 at 16:29

SierraOscar · Accepted Answer · 2015-09-22 16:14:27Z

1

Outside of your procedure (at the top of the module) use:

Private Type EmailInfo
    Sender As String
    DateReceived As Date
    Subject As String
End Type

Then in your procedure use:

Dim emails() As EmailInfo
Dim i As Long: i = 1

Set colItems = Fldr.Items

ReDim email(1 To colItems.Count) As EmailInfo

For Each objItem In colItems
    With objItem
        email(i).Sender = .SenderEmailAddress
        email(i).DateReceived = .ReceivedTime
        email(i).Subject = .Subject
    End With

    i = i + 1
Next

Finally, for testing you can use this afterward:

For i = 1 To UBound(email)
    Debug.Print email(i).Sender
    Debug.Print email(i).DateReceived
    Debug.Print email(i).Subject
Next i

answered Sep 22, 2015 at 16:14

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Array in For Each - VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related