A Complex Mail Merge Question

[Sheikh Saadi]

Hi,
A have a sheet of my Sales Reps and accounts of their Customers. Each
customer’s record is consisting of one row, having sales rep name and email
in it. At the end of every week, I need to send an email to all reps asking
the updates on open sales/opportunities statuses against their respective
clients’ accounts. Now the problem is when I use mail merge function, a
single rep is getting multiple emails for different customers’ accounts. I
want to some up/merge the customers’ names having open status for a
particular rep and send an email to the rep containing all customers’ names
in it.
Is there any possibility where I can put all customers having open status
against a particular rep. and then use that list to send out emails?
A sample from my worksheet is:

Name Account Email Status
Asad Ijaz Acc0005 [email protected] Open
Asad Ijaz Acc0044 [email protected] Open
Ali Khan Acc0056 [email protected] Open
James McBride Acc0078 [email protected] Open
James McBride Acc0034 [email protected] Open
James McBride Acc0023 [email protected] Open
Imran Khald Acc0093 [email protected] Open
John Davis Acc0037 [email protected] Open
Malik Atique Acc0089 [email protected] Open
M. Salman Acc0008 [email protected] Open
Muzafar Sattar Acc0088 [email protected] Open
Saad Naseem Acc0001 [email protected] Open
Saddat Sarfraz Acc0002 [email protected] Open
Saddat Sarfraz Acc0003 [email protected] Open
Zain Zia Acc0086 [email protected] Open

I want out put something like this:
Name Account Email Status
Asad Ijaz Acc0005, Acc0044 [email protected] Open
Ali Khan Acc0056 [email protected] Open
James McBride Acc0078, Acc0034 [email protected] Open
Imran Khalid Acc0093 [email protected]
Open
John Davis Acc0037 [email protected]
Open
Malik Atique Acc0089 [email protected] Open
M. Salman Acc0008 [email protected] Open
Muzafar Sattar Acc0088 [email protected] Open
Saad Naseem Acc0001, Acc0002 [email protected] Open
Zain Zia Acc0086 [email protected] Open


A rep also might have 1, 2, 3 or n number of accounts.

Please help. I really need this sorted out. This will help me a lots of
manual work and saves my time.

Thanks.

 

[Joel]

Try this

Sub CombineAcoounts()

RowCount = 2
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) Then
Accounts = Range("B" & RowCount) & ", " & _
Range("B" & (RowCount + 1))
Range("B" & RowCount) = Accounts
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End Sub

 

[Sheikh Saadi]

Thanks Joel its worked and I’ve changed this to support for multiple fields
and fulfill my complete requirements.

Once again thanks a lot.