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