Concatenating unique cells (vertically), leaving "; " between results (for emails)

Hi all
I'm creating a macro to compile some emails but taking the necessary addresses information from the spreadsheet.

I have some email addresses in lets say column H (row 8 downwards). Lets say as per example below.

----------------- ------------col H
Row 8--------------- empty cell-----------
Row 9--------------- empty cell-----------
Row 10------- John.smith1@hotmail.com
Row 11------- John.smith1@hotmail.com
Row 12------- John.smith1@hotmail.com
Row 13--------------- empty cell-----------
Row 14------- John.smith14@hotmail.com
Row 15------- John.smith18@hotmail.com
Row 16------- John.smith24@hotmail.com
Row 17--------------- empty cell-----------
Row 18------- John.smith30@hotmail.com

Now, I'm after a macro or formula that would look the information from H8 (inclusive) downwards as far as per last cell used in column A. Now it should take all the unique email addresses and concatenate them together and use "; " (read semicolon + 1 space) and populate this into cell H3.

In our example the correct result in H3 would be like this:
John.smith1@hotmail.com; John.smith14@hotmail.com; John.smith18@hotmail.com; John.smith24@hotmail.com; John.smith30@hotmail.com;

But not like this:
John.smith1@hotmail.com; John.smith1@hotmail.com; John.smith1@hotmail.com; John.smith14@hotmail.com; John.smith18@hotmail.com; John.smith24@hotmail.com; John.smith30@hotmail.com;


I have attached and example spreadsheet. It has sheet "Before", "After" and ""But not like this"

My intention is use some email compiling macros, that would take the email addresses from H3.

Does anyone know how to achieve this sort of concatenation?
Any help is greatly appreciated.
Cheers

Hello rain4u,

The macro below setups up both the recipients and CC email columns "H3" and "I3" with the unique addresses separated by a semi-colon and a space. It has been added to the attached workbook.

Please Login or Register  to view this content.

Hello Rain4u,

Glad you like it. That will be 25 cents to the egress.

Hello rain4u,

Looks like I owe a refund. WTF is going on? I will look this over to find the cause. This should not be happening.

Hello Rain4u,

What a beginner's mistake! I did not clear the Recipients string before the next call.

Here is the corrected code...

Please Login or Register  to view this content.

Hello Rain4u,

To have the text wrap in the cell requires adding a vertical tab character ANSI character 10. In VBA you can concatenate a Chr(10) into the string.