CREATING zip FILE, RUN TIME ERROR 91

sjkuehn

New member
Local time
Today, 08:01
Joined
Mar 22, 2012
Messages
1
'Zip and mail the ActiveWorkbook
'This will only work if you use Outlook as your mail program
'
'This sub will send a newly created workbook (copy of the Activeworkbook).
'It save and zip the workbook before mailing it with a date/time stamp.
'After the zip file is sent the zip file and the workbook will be deleted from your hard disk.

Sub Zip_Mail_ActiveWorkbook()
Dim strDate As String, DefPath As String, strbody As String
Dim objShell As Object
Dim OutApp As Object, OutMail As Object
Dim FileNameZip, FileNamexls As String
Dim objSource As Object
Dim objTarget As Object
Dim FileExtStr As String
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If
'Create date/time string and the temporary xl* and zip file name
If Val(Application.Version) < 12 Then
FileExtStr = ".xls"
Else
Select Case ActiveWorkbook.FileFormat
Case 51: FileExtStr = ".xlsx"
Case 52: FileExtStr = ".xlsm"
Case 56: FileExtStr = ".xls"
Case 50: FileExtStr = ".xlsb"
Case Else: FileExtStr = "notknown"
End Select
If FileExtStr = "notknown" Then
MsgBox "Sorry unknown file format"
Exit Sub
End If
End If
strDate = Format(Now, " yyyy-mm-dd h-mm-ss")
FileNameZip = DefPath & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & ".zip"
FileNamexls = DefPath & Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - Len(FileExtStr)) & strDate & FileExtStr

If Dir(FileNameZip) = "" And Dir(FileNamexls) = "" Then
'Make copy of the activeworkbook
ActiveWorkbook.SaveCopyAs FileNamexls
'Create empty Zip File
NewZip = (FileNameZip)
'Copy the file in the compressed folde: FileNameZip = name of zip file:filenamexls nonzipped source file
Set objShell = CreateObject("Shell.Application")
objShell.Namespace(CVar(FileNameZip)).CopyHere FileNamexls <<<< >>>>>>>>>BARFS HERE - "RUN TIME ERROR 91, OBJECT VARIABLE OR WITH BLOCK NOT SET"
' intOptions = 256
 

Users who are viewing this thread

Back
Top Bottom