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

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

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

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jackie" wrote in message ...
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

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

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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jackie" wrote in message ...
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

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

See the functions on that page

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



--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jackie" wrote in message ...
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" wrote:

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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Jackie" wrote in message ...
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