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
Bookmarks