Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending files by e-mail
I have an application, which when the user has finished with it, 3 files
have to be created and sent to other departments. Previously the process of creating the files (extracting certain data from the file) was done by macro and the new file was then sent manually. I believe there is a way to automatically send these files. One of the files is just one sheet and MUST be saved in.csv format, I have tried to do this with code from ron de bruin's site but with no luck. This sheet is not copied, it is a new sheet which data is copied to - it is then moved to a new file. The other two files have 2 and 5 sheets to be copied form the original file, both saved as .xls Any help gratefully received. Gareth (confused) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending files by e-mail
Try this
Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs Filename:="Part of " & ThisWorkbook.Name _ & " " & strdate, FileFormat:=xlCSV .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub You can use this also Sheets("Sheet5").Copy See the example for send ing a sheets array on my site also -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... I have an application, which when the user has finished with it, 3 files have to be created and sent to other departments. Previously the process of creating the files (extracting certain data from the file) was done by macro and the new file was then sent manually. I believe there is a way to automatically send these files. One of the files is just one sheet and MUST be saved in.csv format, I have tried to do this with code from ron de bruin's site but with no luck. This sheet is not copied, it is a new sheet which data is copied to - it is then moved to a new file. The other two files have 2 and 5 sheets to be copied form the original file, both saved as .xls Any help gratefully received. Gareth (confused) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending files by e-mail
I need to get 2 cell values into the file name, the file name should be in
the format "2003+range("c1").value+range("b1").value.csv" . Also, I need to do some formatting before copying the sheet. Where should this code go? Gareth "Ron de Bruin" wrote in message ... Try this Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs Filename:="Part of " & ThisWorkbook.Name _ & " " & strdate, FileFormat:=xlCSV .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub You can use this also Sheets("Sheet5").Copy See the example for send ing a sheets array on my site also -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... I have an application, which when the user has finished with it, 3 files have to be created and sent to other departments. Previously the process of creating the files (extracting certain data from the file) was done by macro and the new file was then sent manually. I believe there is a way to automatically send these files. One of the files is just one sheet and MUST be saved in.csv format, I have tried to do this with code from ron de bruin's site but with no luck. This sheet is not copied, it is a new sheet which data is copied to - it is then moved to a new file. The other two files have 2 and 5 sheets to be copied form the original file, both saved as .xls Any help gratefully received. Gareth (confused) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending files by e-mail
Hi Gareth
.SaveAs Filename:="2003 " & Range("c1").Value _ & " " & Range("b1").Value, FileFormat:=xlCSV This will use the cells of the activesheet! Also, I need to do some formatting before copying the sheet If you don't want that formatting in your original workbook you can add the code after the copy line and before the save code -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... I need to get 2 cell values into the file name, the file name should be in the format "2003+range("c1").value+range("b1").value.csv" . Also, I need to do some formatting before copying the sheet. Where should this code go? Gareth "Ron de Bruin" wrote in message ... Try this Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs Filename:="Part of " & ThisWorkbook.Name _ & " " & strdate, FileFormat:=xlCSV .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub You can use this also Sheets("Sheet5").Copy See the example for send ing a sheets array on my site also -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... I have an application, which when the user has finished with it, 3 files have to be created and sent to other departments. Previously the process of creating the files (extracting certain data from the file) was done by macro and the new file was then sent manually. I believe there is a way to automatically send these files. One of the files is just one sheet and MUST be saved in.csv format, I have tried to do this with code from ron de bruin's site but with no luck. This sheet is not copied, it is a new sheet which data is copied to - it is then moved to a new file. The other two files have 2 and 5 sheets to be copied form the original file, both saved as .xls Any help gratefully received. Gareth (confused) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending files by e-mail
Thanks for this, so far so good....
I will not be 'Activesheet.Copy' but 'Activesheet.Move', will this make any difference? One thing, the SaveAs box is displayed. How can I prevent this? Gareth -----Original Message----- Hi Gareth .SaveAs Filename:="2003 " & Range("c1").Value _ & " " & Range("b1").Value, FileFormat:=xlCSV This will use the cells of the activesheet! Also, I need to do some formatting before copying the sheet If you don't want that formatting in your original workbook you can add the code after the copy line and before the save code -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... I need to get 2 cell values into the file name, the file name should be in the format "2003+range("c1").value+range ("b1").value.csv". Also, I need to do some formatting before copying the sheet. Where should this code go? Gareth "Ron de Bruin" wrote in message ... Try this Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs Filename:="Part of " & ThisWorkbook.Name _ & " " & strdate, FileFormat:=xlCSV .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub You can use this also Sheets("Sheet5").Copy See the example for send ing a sheets array on my site also -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... I have an application, which when the user has finished with it, 3 files have to be created and sent to other departments. Previously the process of creating the files (extracting certain data from the file) was done by macro and the new file was then sent manually. I believe there is a way to automatically send these files. One of the files is just one sheet and MUST be saved in.csv format, I have tried to do this with code from ron de bruin's site but with no luck. This sheet is not copied, it is a new sheet which data is copied to - it is then moved to a new file. The other two files have 2 and 5 sheets to be copied form the original file, both saved as .xls Any help gratefully received. Gareth (confused) . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending files by e-mail
Move will place the sheet on a other location in the workbook
Activesheet.Copy will create a new workbook with the activesheet in it One thing, the SaveAs box is displayed. How can I prevent this? If you replace .SaveAs Filename:="Part of " & ThisWorkbook.Name _ & " " & strdate, FileFormat:=xlCSV with this .SaveAs Filename:="2003 " & Range("c1").Value _ & " " & Range("b1").Value, FileFormat:=xlCSV You don't see the saveas box -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... Thanks for this, so far so good.... I will not be 'Activesheet.Copy' but 'Activesheet.Move', will this make any difference? One thing, the SaveAs box is displayed. How can I prevent this? Gareth -----Original Message----- Hi Gareth .SaveAs Filename:="2003 " & Range("c1").Value _ & " " & Range("b1").Value, FileFormat:=xlCSV This will use the cells of the activesheet! Also, I need to do some formatting before copying the sheet If you don't want that formatting in your original workbook you can add the code after the copy line and before the save code -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... I need to get 2 cell values into the file name, the file name should be in the format "2003+range("c1").value+range ("b1").value.csv". Also, I need to do some formatting before copying the sheet. Where should this code go? Gareth "Ron de Bruin" wrote in message ... Try this Sub Mail_ActiveSheet() Dim wb As Workbook Dim strdate As String strdate = Format(Now, "dd-mm-yy h-mm-ss") Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs Filename:="Part of " & ThisWorkbook.Name _ & " " & strdate, FileFormat:=xlCSV .SendMail ", _ "This is the Subject line" .ChangeFileAccess xlReadOnly Kill .FullName .Close False End With Application.ScreenUpdating = True End Sub You can use this also Sheets("Sheet5").Copy See the example for send ing a sheets array on my site also -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... I have an application, which when the user has finished with it, 3 files have to be created and sent to other departments. Previously the process of creating the files (extracting certain data from the file) was done by macro and the new file was then sent manually. I believe there is a way to automatically send these files. One of the files is just one sheet and MUST be saved in.csv format, I have tried to do this with code from ron de bruin's site but with no luck. This sheet is not copied, it is a new sheet which data is copied to - it is then moved to a new file. The other two files have 2 and 5 sheets to be copied form the original file, both saved as .xls Any help gratefully received. Gareth (confused) . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sending one sheet by mail | Excel Discussion (Misc queries) | |||
Sending E-Mail by code | Excel Discussion (Misc queries) | |||
General mail failure when sending e-mail from Excel | Excel Discussion (Misc queries) | |||
Sending E-mail | Excel Worksheet Functions | |||
sending outlook mail | Excel Programming |