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)

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)

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)

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)

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)








.

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)








.