A Complex Mail Merge Question

S

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 (e-mail address removed) Open
Asad Ijaz Acc0044 (e-mail address removed) Open
Ali Khan Acc0056 (e-mail address removed) Open
James McBride Acc0078 (e-mail address removed) Open
James McBride Acc0034 (e-mail address removed) Open
James McBride Acc0023 (e-mail address removed) Open
Imran Khald Acc0093 (e-mail address removed) Open
John Davis Acc0037 (e-mail address removed) Open
Malik Atique Acc0089 (e-mail address removed) Open
M. Salman Acc0008 (e-mail address removed) Open
Muzafar Sattar Acc0088 (e-mail address removed) Open
Saad Naseem Acc0001 (e-mail address removed) Open
Saddat Sarfraz Acc0002 (e-mail address removed) Open
Saddat Sarfraz Acc0003 (e-mail address removed) Open
Zain Zia Acc0086 (e-mail address removed) Open

I want out put something like this:
Name Account Email Status
Asad Ijaz Acc0005, Acc0044 (e-mail address removed) Open
Ali Khan Acc0056 (e-mail address removed) Open
James McBride Acc0078, Acc0034 (e-mail address removed) Open
Imran Khalid Acc0093 (e-mail address removed)
Open
John Davis Acc0037 (e-mail address removed)
Open
Malik Atique Acc0089 (e-mail address removed) Open
M. Salman Acc0008 (e-mail address removed) Open
Muzafar Sattar Acc0088 (e-mail address removed) Open
Saad Naseem Acc0001, Acc0002 (e-mail address removed) Open
Zain Zia Acc0086 (e-mail address removed) 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.
 
J

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
 
S

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.
 
Top