Hi,

I have a selection of code that currently makes a copy of the exsisting
workbook and then zips and emails that copy using Outlook, I know this might
sound hopeful but is it at all possible to delete a worksheet out of the copy
before it gets zipped and emailed.

Thanks
Phil

try:

Thisworkbook.savecopyas "NewFileName.xls"
Set NewBook=workbooks("NewFileName.xls").open
with newbook
application.displayalerts=false
.sheets("sheetName").delete
application.displayalerts=true
end with
'then your code for e-mailing etc



"Phil" wrote:

Hi,

I have a selection of code that currently makes a copy of the exsisting
workbook and then zips and emails that copy using Outlook, I know this might
sound hopeful but is it at all possible to delete a worksheet out of the copy
before it gets zipped and emailed.

Thanks
Phil

Just to let you know I have managed to get it to compile further, it was
missing the s of workbooks, now it's just getting a Method or Data error on
..Sheets, slowly getting there, as you can tell this is my first time using VB.

Thanks Phil

"Phil" wrote:

Hi Rob,

Thanks again for the quick reply, I had already set up the variable and
defined the whole path, this is what my code looks like at the moment : -

Sub ActiveWorkbook_Zip_Mail()

Dim PathWinZip As String, FileNameZip As String, FileNameXls As String,
Route As String, Newbook As Workbook
Dim ShellStr As String, strDate As String
Dim OutApp As Object
Dim OutMail As Object

PathWinZip = "C:\program files\ezip wizard\"

If Dir(PathWinZip & "eZip32.exe") = "" Then
MsgBox "Please find your copy of ezip32.exe and try again"
Exit Sub
End If

sendTo = Range("B24").Value

'ActiveWorkbook.SaveCopyAs Filename:="c:\test\Copy.xls"

'Sheets(Hope & "Copy.xls" & "Sheet5").Delete

ThisWorkbook.SaveCopyAs "c:\test\copy.xls"
Set Newbook = Workbook("c:\test\copy.xls").Open
With Newbook
Application.DisplayAlerts = False
.Sheets("sheet5").Delete
Application.DisplayAlerts = True
End With

Route = "c:\test"

FileNameZip = Route & "\" & "Copy.zip"

FileNameXls = Route & "\" & "Copy.xls"

ShellStr = PathWinZip & "ezip32 -min -a" _
& " " & Chr(34) & FileNameZip & Chr(34) _
& " " & Chr(34) & FileNameXls & Chr(34)
ShellAndWait ShellStr, vbHide

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = sendTo
.CC = ""
.BCC = ""
.Subject = "IRVSData Check"
.Body = "Please find attached you latest cleansed file details"
.Attachments.Add FileNameZip
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing

Kill FileNameZip
Kill FileNameXls
End Sub


Thanks again for all the help, Phil

"Rob" wrote:

you need to declare the variable. Also "NewFileName.xls" should be the full
path where you are saving the workbook.

dim NewBook as workbook


Thisworkbook.savecopyas "NewFileName.xls"
Set NewBook=workbooks("NewFileName.xls").open
with newbook
application.displayalerts=false
.sheets("sheetName").delete
application.displayalerts=true
end with
'then your code for e-mailing etc

Hi Rob,

Thanks again for the quick reply, I had already set up the variable and
defined the whole path, this is what my code looks like at the moment : -

Sub ActiveWorkbook_Zip_Mail()

Dim PathWinZip As String, FileNameZip As String, FileNameXls As String,
Route As String, Newbook As Workbook
Dim ShellStr As String, strDate As String
Dim OutApp As Object
Dim OutMail As Object

PathWinZip = "C:\program files\ezip wizard\"

If Dir(PathWinZip & "eZip32.exe") = "" Then
MsgBox "Please find your copy of ezip32.exe and try again"
Exit Sub
End If

sendTo = Range("B24").Value

'ActiveWorkbook.SaveCopyAs Filename:="c:\test\Copy.xls"

'Sheets(Hope & "Copy.xls" & "Sheet5").Delete

ThisWorkbook.SaveCopyAs "c:\test\copy.xls"
Set Newbook = Workbook("c:\test\copy.xls").Open
With Newbook
Application.DisplayAlerts = False
.Sheets("sheet5").Delete
Application.DisplayAlerts = True
End With

Route = "c:\test"

FileNameZip = Route & "\" & "Copy.zip"

FileNameXls = Route & "\" & "Copy.xls"

ShellStr = PathWinZip & "ezip32 -min -a" _
& " " & Chr(34) & FileNameZip & Chr(34) _
& " " & Chr(34) & FileNameXls & Chr(34)
ShellAndWait ShellStr, vbHide

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = sendTo
.CC = ""
.BCC = ""
.Subject = "IRVSData Check"
.Body = "Please find attached you latest cleansed file details"
.Attachments.Add FileNameZip
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing

Kill FileNameZip
Kill FileNameXls
End Sub


Thanks again for all the help, Phil

"Rob" wrote:

you need to declare the variable. Also "NewFileName.xls" should be the full
path where you are saving the workbook.

dim NewBook as workbook


Thisworkbook.savecopyas "NewFileName.xls"
Set NewBook=workbooks("NewFileName.xls").open
with newbook
application.displayalerts=false
.sheets("sheetName").delete
application.displayalerts=true
end with
'then your code for e-mailing etc

Sorry Phil. The line should be
set newbook=workbooks.open("c:\test\copy.xls")

"Phil" wrote:

Just to let you know I have managed to get it to compile further, it was
missing the s of workbooks, now it's just getting a Method or Data error on
.Sheets, slowly getting there, as you can tell this is my first time using VB.

Thanks Phil

"Phil" wrote:

Hi Rob,

Thanks again for the quick reply, I had already set up the variable and
defined the whole path, this is what my code looks like at the moment : -

Sub ActiveWorkbook_Zip_Mail()

Dim PathWinZip As String, FileNameZip As String, FileNameXls As String,
Route As String, Newbook As Workbook
Dim ShellStr As String, strDate As String
Dim OutApp As Object
Dim OutMail As Object

PathWinZip = "C:\program files\ezip wizard\"

If Dir(PathWinZip & "eZip32.exe") = "" Then
MsgBox "Please find your copy of ezip32.exe and try again"
Exit Sub
End If

sendTo = Range("B24").Value

'ActiveWorkbook.SaveCopyAs Filename:="c:\test\Copy.xls"

'Sheets(Hope & "Copy.xls" & "Sheet5").Delete

ThisWorkbook.SaveCopyAs "c:\test\copy.xls"
Set Newbook = Workbook("c:\test\copy.xls").Open
With Newbook
Application.DisplayAlerts = False
.Sheets("sheet5").Delete
Application.DisplayAlerts = True
End With

Route = "c:\test"

FileNameZip = Route & "\" & "Copy.zip"

FileNameXls = Route & "\" & "Copy.xls"

ShellStr = PathWinZip & "ezip32 -min -a" _
& " " & Chr(34) & FileNameZip & Chr(34) _
& " " & Chr(34) & FileNameXls & Chr(34)
ShellAndWait ShellStr, vbHide

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = sendTo
.CC = ""
.BCC = ""
.Subject = "IRVSData Check"
.Body = "Please find attached you latest cleansed file details"
.Attachments.Add FileNameZip
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing

Kill FileNameZip
Kill FileNameXls
End Sub


Thanks again for all the help, Phil

"Rob" wrote:

you need to declare the variable. Also "NewFileName.xls" should be the full
path where you are saving the workbook.

dim NewBook as workbook


Thisworkbook.savecopyas "NewFileName.xls"
Set NewBook=workbooks("NewFileName.xls").open
with newbook
application.displayalerts=false
.sheets("sheetName").delete
application.displayalerts=true
end with
'then your code for e-mailing etc

Hi Rob,

Thanks again for the help with this, I have made the necessary change as
suggested and made a further change with the delete statement i.e. it did
have a full stop at the beginning of the like with the delete on it, I have
removed this as it didn't seem to work.

ThisWorkbook.SaveCopyAs "c:\test\copy.xls"
Set Newbook = Workbooks.Open("c:\test\copy.xls")
With Newbook
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
Application.DisplayAlerts = True
End With

What I get now is if I run the code in the code view and press run, the new
file is created, then it goes back to the code view of the original file and
says Type mismatch on this line :-

Set Newbook = Workbooks.Open("c:\test\copy.xls")

Slowly driving me mad but thanks for the help though :-)


"Phil" wrote:

Just to let you know I have managed to get it to compile further, it was
missing the s of workbooks, now it's just getting a Method or Data error on
.Sheets, slowly getting there, as you can tell this is my first time using VB.

Thanks Phil

"Phil" wrote:

Hi Rob,

Thanks again for the quick reply, I had already set up the variable and
defined the whole path, this is what my code looks like at the moment : -

Sub ActiveWorkbook_Zip_Mail()

Dim PathWinZip As String, FileNameZip As String, FileNameXls As String,
Route As String, Newbook As Workbook
Dim ShellStr As String, strDate As String
Dim OutApp As Object
Dim OutMail As Object

PathWinZip = "C:\program files\ezip wizard\"

If Dir(PathWinZip & "eZip32.exe") = "" Then
MsgBox "Please find your copy of ezip32.exe and try again"
Exit Sub
End If

sendTo = Range("B24").Value

'ActiveWorkbook.SaveCopyAs Filename:="c:\test\Copy.xls"

'Sheets(Hope & "Copy.xls" & "Sheet5").Delete

ThisWorkbook.SaveCopyAs "c:\test\copy.xls"
Set Newbook = Workbook("c:\test\copy.xls").Open
With Newbook
Application.DisplayAlerts = False
.Sheets("sheet5").Delete
Application.DisplayAlerts = True
End With

Route = "c:\test"

FileNameZip = Route & "\" & "Copy.zip"

FileNameXls = Route & "\" & "Copy.xls"

ShellStr = PathWinZip & "ezip32 -min -a" _
& " " & Chr(34) & FileNameZip & Chr(34) _
& " " & Chr(34) & FileNameXls & Chr(34)
ShellAndWait ShellStr, vbHide

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = sendTo
.CC = ""
.BCC = ""
.Subject = "IRVSData Check"
.Body = "Please find attached you latest cleansed file details"
.Attachments.Add FileNameZip
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing

Kill FileNameZip
Kill FileNameXls
End Sub


Thanks again for all the help, Phil

"Rob" wrote:

you need to declare the variable. Also "NewFileName.xls" should be the full
path where you are saving the workbook.

dim NewBook as workbook


Thisworkbook.savecopyas "NewFileName.xls"
Set NewBook=workbooks("NewFileName.xls").open
with newbook
application.displayalerts=false
.sheets("sheetName").delete
application.displayalerts=true
end with
'then your code for e-mailing etc

Hi Rob,

After a lot of searching sites, I have finally got it all working, I'm
arfaid I hacked your original code up a bit and I'm not sure if this is the
best way to write it but it works....


ThisWorkbook.SaveCopyAs "c:\test\copy.xls"
Application.Workbooks.Open ("c:\test\copy.xls")
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True

Thanks for all the help, wouldn't of got this far otherwise.

Thanks Phil


"Phil" wrote:

Hi,

I have a selection of code that currently makes a copy of the exsisting
workbook and then zips and emails that copy using Outlook, I know this might
sound hopeful but is it at all possible to delete a worksheet out of the copy
before it gets zipped and emailed.

Thanks
Phil