Excel/Word mail merge to multiple recipients per merge/TO: field

[bungeeee]

Hi all

I have a huge list of mailboxes which all have multiple users who i need to contact. For example the data looks like this:

MAILBOX 1 USEREMAIL1
MAILBOX 1 USEREMAIL2
MAILBOX 1 USEREMAIL3
MAILBOX 2 USEREMAIL4
MAILBOX 2 USEREMAIL5
MAILBOX 2 USEREMAIL6
MAILBOX 2 USEREMAIL7

Currently im auto-filtering the data into a new sheet to get the individual box information, merging the details into a pre-written word document, using 'Send to mail recipients', pasting all of the email addresses into the TO: field, pasting the mailbox name into the SUBJECT: field and sending - so for example an email for MAILBOX 1 will be sent to users 1-3 (all copied in on the same email).

I'm looking for a way to mail merge the whole data sheet but keep this function of emailing 1 email to all relevant users within the specified mailbox (rather than the standard mail merge which sends 1 email to 1 recipient resulting in sending the users of MAILBOX 1 3 individual emails and loosing the visibility of all users) and if possible a way to include the mailbox name field in the SUBJECT: field as well.

I understand this will probably require some complicated VBA so i appreciate any assistance given. Thank you in advance.

 

[bungeeee]

I found a solution to this problem so i thought id post it in case anyone else has this issue:

Firstly you need to transpose the whole list from columns to rows using the formulae in this link:
excel - Transpose multiple rows to multiple columns - Stack Overflow

then automatically create outlook contact lists from the result using the following macro:
Creating Multiple Outlook Distribution Lists from Excel » Planet Mediocrity

you can then mail merge each email to each contact group