Hi,
A have a sheet of my Sales Reps and accounts of their Customers. Each
customers 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 Open
Asad Ijaz Acc0044 Open
Ali Khan Acc0056 Open
James McBride Acc0078 Open
James McBride Acc0034 Open
James McBride Acc0023 Open
Imran Khald Acc0093 Open
John Davis Acc0037 Open
Malik Atique Acc0089 Open
M. Salman Acc0008 Open
Muzafar Sattar Acc0088 Open
Saad Naseem Acc0001 Open
Saddat Sarfraz Acc0002 Open
Saddat Sarfraz Acc0003 Open
Zain Zia Acc0086 Open

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

--
Sheikh Saadi

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" wrote:

Hi,
A have a sheet of my Sales Reps and accounts of their Customers. Each
customers 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 Open
Asad Ijaz Acc0044 Open
Ali Khan Acc0056 Open
James McBride Acc0078 Open
James McBride Acc0034 Open
James McBride Acc0023 Open
Imran Khald Acc0093 Open
John Davis Acc0037 Open
Malik Atique Acc0089 Open
M. Salman Acc0008 Open
Muzafar Sattar Acc0088 Open
Saad Naseem Acc0001 Open
Saddat Sarfraz Acc0002 Open
Saddat Sarfraz Acc0003 Open
Zain Zia Acc0086 Open

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

--
Sheikh Saadi

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

Once again thanks a lot.

--
Sheikh Saadi


"Joel" wrote:

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" wrote:

Hi,
A have a sheet of my Sales Reps and accounts of their Customers. Each
customers 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 Open
Asad Ijaz Acc0044 Open
Ali Khan Acc0056 Open
James McBride Acc0078 Open
James McBride Acc0034 Open
James McBride Acc0023 Open
Imran Khald Acc0093 Open
John Davis Acc0037 Open
Malik Atique Acc0089 Open
M. Salman Acc0008 Open
Muzafar Sattar Acc0088 Open
Saad Naseem Acc0001 Open
Saddat Sarfraz Acc0002 Open
Saddat Sarfraz Acc0003 Open
Zain Zia Acc0086 Open

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

--
Sheikh Saadi