I have an Excel file with 198 rows and several columns. One of the columns
is a list of the different "carriers". Out of the 198 rows there is only
10-12 different carriers. The rest of the columns are misc data.

I need to send this information out to each carrier but can only show each
carrier their own information. In otherwords; I need to sort by the column
listing the carrieres and then divide the information up by each unique value
in that column.

Is there anyway to automate this? Any help will be greatly apprciated.

Cindy.

Sure, check out

http://www.rondebruin.nl/copy5.htm


HTH,
JP

On Jan 28, 1:12*pm, Jordan wrote:
I have an Excel file with 198 rows and several columns. *One of the columns
is a list of the different "carriers". *Out of the 198 rows there is only
10-12 different carriers. *The rest of the columns are misc data.

I need to send this information out to each carrier but can only show each
carrier their own information. *In otherwords; I need to sort by the column
listing the carrieres and then divide the information up by each unique value
in that column.

Is there anyway to automate this? *Any help will be greatly apprciated.

Cindy.

Hi Jordan

Is the mail addresss of each carrier also in the table ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
I have an Excel file with 198 rows and several columns. One of the columns
is a list of the different "carriers". Out of the 198 rows there is only
10-12 different carriers. The rest of the columns are misc data.

I need to send this information out to each carrier but can only show each
carrier their own information. In otherwords; I need to sort by the column
listing the carrieres and then divide the information up by each unique value
in that column.

Is there anyway to automate this? Any help will be greatly apprciated.

Cindy.

No. I was hoping to have each email saved as a draft or left open so the
address could be added. Thanks for your help.

"Ron de Bruin" wrote:

Hi Jordan

Is the mail addresss of each carrier also in the table ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
I have an Excel file with 198 rows and several columns. One of the columns
is a list of the different "carriers". Out of the 198 rows there is only
10-12 different carriers. The rest of the columns are misc data.

I need to send this information out to each carrier but can only show each
carrier their own information. In otherwords; I need to sort by the column
listing the carrieres and then divide the information up by each unique value
in that column.

Is there anyway to automate this? Any help will be greatly apprciated.

Cindy.

Use the code to split the data in different sheets
See reply from JP

Then loop through the sheets and mail them like this

Change the sheet name here to your data sheet with all the data
If sh.Name < "YourDataSheet" Then


Sub Mail_Every_Worksheet()
'Working in 97-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

TempFilePath = Environ$("temp") & "\"

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ThisWorkbook.Worksheets
If sh.Name < "YourDataSheet" Then

sh.Copy
Set wb = ActiveWorkbook

TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

End If
Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub






--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
No. I was hoping to have each email saved as a draft or left open so the
address could be added. Thanks for your help.

"Ron de Bruin" wrote:

Hi Jordan

Is the mail addresss of each carrier also in the table ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
I have an Excel file with 198 rows and several columns. One of the columns
is a list of the different "carriers". Out of the 198 rows there is only
10-12 different carriers. The rest of the columns are misc data.

I need to send this information out to each carrier but can only show each
carrier their own information. In otherwords; I need to sort by the column
listing the carrieres and then divide the information up by each unique value
in that column.

Is there anyway to automate this? Any help will be greatly apprciated.

Cindy.

JP - Thanks for the lead.

Ron de Bruin. Thank you. Thank you. Thank you. I used the macro and it
does exactly what we need it to do. This is going to save many hours in a
day. I have a couple of questions, if you dont mind.
(1) Is there a way to get rid of the red cell highlighing in the created
files.
(2) I am really new to this. How do I add the second set of code to the
first.

Again, this is very helpful so many thanks.

"Ron de Bruin" wrote:

Use the code to split the data in different sheets
See reply from JP

Then loop through the sheets and mail them like this

Change the sheet name here to your data sheet with all the data
If sh.Name < "YourDataSheet" Then


Sub Mail_Every_Worksheet()
'Working in 97-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

TempFilePath = Environ$("temp") & "\"

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ThisWorkbook.Worksheets
If sh.Name < "YourDataSheet" Then

sh.Copy
Set wb = ActiveWorkbook

TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

End If
Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub






--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
No. I was hoping to have each email saved as a draft or left open so the
address could be added. Thanks for your help.

"Ron de Bruin" wrote:

Hi Jordan

Is the mail addresss of each carrier also in the table ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
I have an Excel file with 198 rows and several columns. One of the columns
is a list of the different "carriers". Out of the 198 rows there is only
10-12 different carriers. The rest of the columns are misc data.

I need to send this information out to each carrier but can only show each
carrier their own information. In otherwords; I need to sort by the column
listing the carrieres and then divide the information up by each unique value
in that column.

Is there anyway to automate this? Any help will be greatly apprciated.

Cindy.

(1) Is there a way to get rid of the red cell highlighing in the created
files.

Sorry I not understand what you mean with this

2: You can call the mail macro in the splt macro in the last line like

Call Mail_Every_Worksheet

If you use Outlook you can also use another macro that you can use to add text in the body



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
JP - Thanks for the lead.

Ron de Bruin. Thank you. Thank you. Thank you. I used the macro and it
does exactly what we need it to do. This is going to save many hours in a
day. I have a couple of questions, if you dont mind.
(1) Is there a way to get rid of the red cell highlighing in the created
files.
(2) I am really new to this. How do I add the second set of code to the
first.

Again, this is very helpful so many thanks.

"Ron de Bruin" wrote:

Use the code to split the data in different sheets
See reply from JP

Then loop through the sheets and mail them like this

Change the sheet name here to your data sheet with all the data
If sh.Name < "YourDataSheet" Then


Sub Mail_Every_Worksheet()
'Working in 97-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String

TempFilePath = Environ$("temp") & "\"

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

For Each sh In ThisWorkbook.Worksheets
If sh.Name < "YourDataSheet" Then

sh.Copy
Set wb = ActiveWorkbook

TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
.SendMail "", _
"This is the Subject line"
On Error GoTo 0
.Close SaveChanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

End If
Next sh

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub






--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
No. I was hoping to have each email saved as a draft or left open so the
address could be added. Thanks for your help.

"Ron de Bruin" wrote:

Hi Jordan

Is the mail addresss of each carrier also in the table ?

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jordan" wrote in message ...
I have an Excel file with 198 rows and several columns. One of the columns
is a list of the different "carriers". Out of the 198 rows there is only
10-12 different carriers. The rest of the columns are misc data.

I need to send this information out to each carrier but can only show each
carrier their own information. In otherwords; I need to sort by the column
listing the carrieres and then divide the information up by each unique value
in that column.

Is there anyway to automate this? Any help will be greatly apprciated.

Cindy.