sending one sheet by e-mail

[gareth]

Excel '97

This is what I am trying to do:

With my file open - add a new sheet, copy stuff onto it,
move that sheet into a new file and then send that file
in .csv format.

The code below works fine but asks "Do you want to save
changes before switching file status?"

Sub Createandsendfile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
'Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets.Add

' code to add data to the new sheet from several sheets in
the file
' new sheet is then put in a new file

ActiveSheet.Move
Set wb = ActiveWorkbook
With wb
..SaveAs FileName:="my new file", FileFormat:=xlCSV
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
..To = "(e-mail address removed)"
..Subject = "e-mail subject"
..Attachments.Add wb.FullName
..Send
End With
..ChangeFileAccess xlReadOnly
Kill .FullName
..Close False
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
'Application.DisplayAlerts = True
End Sub

I put displayalerts to false but then the SaveAs dialog
box is displayed.

Can anyone please help?

Gareth

 

[gareth]

I have found out what the problem is but cannot fix it
myself.

The problem is switching from .xls to .csv!

Gareth

 

[Ron de Bruin]

Hi Gareth

Use this

This example send the activesheet
You can change it to your situation

Sub Createandsendfile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
..SaveAs Filename:="c:\my new file", FileFormat:=xlCSV
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
..To = "(e-mail address removed)"
..Subject = "e-mail subject"
..Attachments.Add wb.FullName
..display
End With
..Close False
Kill "c:\my new file.csv"
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

 

[Tom Ogilvy]

Sub Createandsendfile()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook, sName as String
'Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets.Add

' code to add data to the new sheet from several sheets in
the file
' new sheet is then put in a new file

ActiveSheet.Move
Set wb = ActiveWorkbook
With wb
..SaveAs FileName:="my new file", FileFormat:=xlCSV
sName = .FullName
..Close SaveChanges:=False
End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
..To = "(e-mail address removed)"
..Subject = "e-mail subject"
..Attachments.Add sName
..Send
End With
Kill sName
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
'Application.DisplayAlerts = True
End Sub