Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
(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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
(1) When the multiple files are created some rows are red. I looked to see
if it had something to do with the filtering when the file was created. For example; if the file was created from lines 2, 7, 8, 9, 12, & 14 then it would cause 2 to be red, 7, 8, & 9 to be white and then 12 to be red again and so forth. This does not seem to be the case and I dont see anything other patterns. (2) I will try this and see what happens. And I will look at the link JP sent me to see if I can find how to add text to the body. All the information you have posted there is very helpful. "Ron de Bruin" wrote: (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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple emails from one Excel file.
(1) When the multiple files are created some rows are red No idea what is going on. If you can't find it then send me the file private with the code you use and I will look at it after work tomorrow For text in the body you can adapt this macro http://www.rondebruin.nl/mail/folder2/mail5.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jordan" wrote in message ... (1) When the multiple files are created some rows are red. I looked to see if it had something to do with the filtering when the file was created. For example; if the file was created from lines 2, 7, 8, 9, 12, & 14 then it would cause 2 to be red, 7, 8, & 9 to be white and then 12 to be red again and so forth. This does not seem to be the case and I dont see anything other patterns. (2) I will try this and see what happens. And I will look at the link JP sent me to see if I can find how to add text to the body. All the information you have posted there is very helpful. "Ron de Bruin" wrote: (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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically add my signature file to emails sent from Excel? | Excel Discussion (Misc queries) | |||
Sending multiple emails at same time in Excel | Excel Programming | |||
Creating spreadsheet of Outlook emails | Excel Programming | |||
Creating spreadsheet of Outlook emails | Excel Discussion (Misc queries) | |||
Sending multiple EMails thru excel? | Excel Discussion (Misc queries) |