How to convert mailing list in Excel column into a string using Collections?

Let us assume you have say 20 email Ids in an Excel sheet to which a common mail communication is to be sent. One way of combining all the emails into a string is using CONCATENATE function. However it is difficult if the no. of email IDs to be combined are more. Another way to accomplish this is by using Collections in VBA. Collections are nothing but group of related objects.

First store all Email Ids into a Collection and then combine each email Ids in Collection into a String variable and this variable can be used as Mailing List to send mails. See the below example.

Below code will create a Collection and add mail Ids from Excel sheet to it. 

Dim MailList as Collection

Set MailList = New Collection

For i  = 1 to 20

MailList.Add Cells(i,1).Value

Next i

Now we have created collection, next step is to combine all mail Ids in collection into a single string.

Dim S as String

S = ""

For Each Item in MailList

S= S  & Item  & ";"

Next 

Now we have created single string 'S' of all 20 mail Ids. This variable can be used in outlook mail  application to send mails as shown below.

MailObj.To = S or

MailObj.CC = S