View Full Version : if file size larger than X KB, run zip/mail vba .... how?

04-21-2007, 07:35 AM

Alright, i did some searching and found this link... per this information, the vba code will zip the file prior to emailing. I assume that this code will always zip the file before emailing.... I would like to have an option as to when it should zip and when not to.... I also am not sure what to replace in the code that i already have...

I would like to tweak it so that if the file is larger than say 500KB then it zips the file prior to emailing. If file is not then it sends it without zipping. Right now the code that I have sends the file with no zipping no matter the file size.

Here's the link:

Here's the code that I have:
'***** Open a new email with project attached to send to next person
Private Sub btnSendNext_Click()
MsgBox "On the next screen, you will see a mail message created with the approval request attached." & Chr(10) & _
"Please address it to the SQE and click the send button."

ActiveWorkbook.SendMail Recipients:="", _
Subject:="Process Approval Request " & ActiveSheet.Range("B12").Value & " - forwarded for next approval"
Application.DisplayAlerts = False

ActiveWorkbook.Close savechanges:=False, routeworkbook:=False
End Sub
'***** Return to Originator (originator)
Private Sub btnRTO_Click()
Dim originator As Variant
originator = ActiveSheet.Range("B8").Value
If originator = Null Then
MsgBox "No originator found"
Exit Sub
End If
ActiveWorkbook.SendMail Recipients:=originator, Subject:="Process Approval Request " & ActiveSheet.Range("B12").Value & " - returned to originator"
Application.DisplayAlerts = False
ActiveWorkbook.Close savechanges:=False, routeworkbook:=False
End Sub
'***** Send to JAX for approval
Private Sub btnJAXApprove_Click()
Set ol = CreateObject("outlook.application")
Set olns = ol.GetNamespace("MAPI")
Set cuser = olns.CurrentUser
ActiveSheet.Range("D37").Value = CStr(cuser)
ActiveSheet.Range("I37").Value = Date
ActiveSheet.OLEObjects("btnJAXApprove").Visible = False
ActiveWorkbook.SendMail _
Recipients:=Array(originator, "B@U.com (ZB@U.com)") _
, Subject:="Request for Approval " & ActiveSheet.Range("B12").Value & " - Need To Be Reviewed"
ActiveWorkbook.Close savechanges:=False, routeworkbook:=False
End Sub

To recap...my questions:
1. What do I replace in my current code so that it first zips then emails?
2. How do I make it so that: IF file size > 500KB, run zip first then email ELSE email without zipping??

Thanks in advance!!!

05-10-2007, 10:14 AM
Well Good News :-) You can easily check the file size with vba.FileLen (note this operation is not valid for file > 2 gb) and then zip using the functions provided here :http://www.rondebruin.nl/zip.htm