How to zip a file before sending it out by macro?

[Jackie]

Hi,

Below is the code to send directly am email with the excel workbook
attached. But does anyone know how can I (by using macro) zip the file before
sending it out?

Private Sub CmdSubmit_Click()
WhereTo = Range("K2")
SubjectText = "Example for " & Range("C3") & " - " & Range("C4")
ActiveWorkbook.SendMail Recipients:=WhereTo, Subject:=SubjectText
End Sub

Thanks and regards,
Jackie

 

[Ron de Bruin]

Hi Jackie

If you use winzip and use Outlook I have code and a Add-in that can do this on my site
http://www.rondebruin.nl/sendmail.htm

 

[Jackie]

Hi Ron,

Thanks a lot and I find that your site is really useful

Regarding to the code in your website, I've encountered a problem when I
tried to run the macro under "Zip and mail the ActiveWorkbook". I got the
below error message:

Compile error: Sub or Function not defined

and the line below is highlighted:

ShellAndWait ShellStr, vbHide

Would you please advise if there is anything wrong with "ShellAndWait" or do
I need to make some changes in the code before running it?

Thanks,
Jackie

 

[Ron de Bruin]

See the functions on that page

http://www.rondebruin.nl/zip.htm#Functions