Creating a temporary file and zipping the file, password protected.

[anderm13]

Hi,

I'm using a module in excel that sends an email.

The module selects a named range, creates a temporary excel file, pastes in the named range and then sends the temporary file in email.

I need the module to zip the temporary file and add a password before emailing the file.

This is the code i have so far:

Sub Email_Range()
'Working in 2000-2007
On Error GoTo ErrorHandler
Dim Source As Range
Dim Dest As Workbook
Dim wb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long

' Dest.SendMail
Set Source = Nothing
'On Error Resume Next
Set Source = Range("Named_Range").SpecialCells(xlCellTypeVisible)
'On Error GoTo 0
If Source Is Nothing Then
MsgBox "The source is not a range or the sheet is protected, " & _
"please correct and try again.", vbOKOnly
Exit Sub
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb = ActiveWorkbook
Set Dest = Workbooks.Add(xlWBATWorksheet)
Source.Copy
With Dest.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial Paste:=xlPasteValues
.Cells(1).PasteSpecial Paste:=xlPasteFormats
.Cells(1).Select
Application.CutCopyMode = False
End With
TempFilePath = Environ$("temp") & "\"
TempFileName = "Temp_File_Name" & " " _
& Format(Now, "dd-mm-yy hh-mm-ss")
If Val(Application.Version) < 12 Then
'You use Excel 2000-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
End If
With Dest
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
'On Error Resume Next
''The email address is located on Sheet10 Range B4
.SendMail Sheet10.Range("B4").Value, "Emergency Job Request"
'On Error GoTo 0
.Close SaveChanges:=False
End With
'Kill TempFilePath & TempFileName & FileExtStr
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Email Sent"
CleanExit:
Exit Sub
ErrorHandler:
MsgBox "Email_Module_RunProcess did not complete because:" & Chr(13) & Chr(13) & Err.Number & ": " & Err.Description, vbCritical + vbSystemModal, "Error!"
Resume CleanExit
End Sub