Want to ZIP and Email the current workbook

[a006dean]

Anyone ever work out a technique to ZIP the currently open workbook and attach it to an email? The WinZip software has this functionality if you do a right-click in Explorer but I can't find any notes about command options to trigger the mail of a zip file.

[Tom Schreiner]

As far as I know, you cannot zip an open file...
Tom

[Jack in the UK]

Hi --

Im sure via VBA you can Zip Xls doce via the WinZip shell command beyond me, and email yep thats not so bad.

I would search this site and im sure both are documented verywell, i have used email codes many times, i developing my own, and the winzip bit i never really solved or got the codes working... dont know why....

good luck... sure can be done...

Check under my name and thank my pal Ivan F Moala for his work.. might not be exact but will get you started...

HTH

[NateO]

I'll get this started with a choppy response. I apologize in advance.

You'll need to define your file with code like:

Dim fname As String 
fname = ActiveWorkbook.FullName

Close fname with:

ThisWorkbook.Close False 'close without saving (otherwise, save)

Now read Ivan's post on zipping files (good notes incidentally):

http://www.mrexcel.com/board/viewtop...ic=776&forum=2

Change:

Source = "C:Final.xls" '"C:myfile.xls"

to

Source = fname

Now use the e-mail code (depending on your e-mail system).

Then open the file with:

Workbooks.Open FileName:=fname

You may want to use:

application.screenupdating = false

So that you don't have to watch all of this activity. The file needs to be saved before this will work, you may want to test this by testing the path, e.g.,:

activeworkbook.path

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-04-04 17:14 ]

[a006dean]

Well, I have made some headway on this thanks to the contributions of lots of folks. I picked up ZIP.EXE at:

http://www.info-zip.org/pub/infozip/

I installed the necessary ZIP.EXE in C:
and did:

Sub ZipAndMail(ByVal Subject As String)

Dim fname As String
Dim storeName As String

Application.EnableEvents = False
'
' Get rid of any existing zip file
'
storeName = "C:PPSTemp.zip"
fname = Dir(storeName)
If fname <> "" Then Kill (storeName)
'
' We will store this spreadsheet in 'c:PPSTemp.xls' so ensure that it does
' not exist before we try to save this worksheet.
'
storeName = "C:PPSTemp.xls"
fname = Dir(storeName)
If fname <> "" Then Kill (storeName)

ActiveWorkbook.SaveCopyAs (storeName)
Shell ("c:zip c:PPSTemp.Zip " + storeName)


Then I triggered the email activity with:


Public Sub SendOutlookMail()

Dim oLapp As Outlook.Application
Dim oItem As MailItem
Dim myAttachments As Attachments

On Error GoTo errorHandler

Set oLapp = CreateObject("Outlook.application")
Set oItem = oLapp.CreateItem(0)
'
' Setup a message
'
oItem.Subject = "Proposal Workbook for " + shStart.Range(cCustomerName)

'oItem.To = ""
'oItem.body = Message

Set myAttachments = oItem.Attachments
myAttachments.Add "c:PPSTemp.ZIP", olByValue, 1, "Excel Workbook"

oItem.Display
'
errorHandler:
Set myAttachments = Nothing
Set oItem = Nothing
Set oLapp = Nothing

End Sub


This seems to work fine EXCEPT that every once in awhile, it hangs and I think that the hang is related to OUTLOOK being left in execution from a prior run. I'm not sure, still investigating.

The other thing that bothers me is that this seems to depend upon OUTLOOK being on the system and I would have preferred to use the built-in dialog for sending mail:

Application.Dialogs(xlDialogSendMail).Show arg1:="", arg2:=Subject & " for " & shStart.Range(cCustomerName), arg3:=True

[NateO]

Wow, the solution! A nice touch that is usually left to be desired!

So, you want to create a custom [soley] XL based e-mail product. Please post this when you get it!

The following is pure Outlook code:

xlDialogSendMail

The only useful thing I can add at this point would be to try and launch Outlook Via Appactivate and use an errohandler to kill your vba if the result is failure, then proceed to verbally flog the end-user for not having Outlook. You could also test for Lotus Notes. I don't quite have the test/login code for Hotmail as of yet...

Sounds like one helluva project, good luck!

_________________
Cheers, NateO

[ This Message was edited by: nateo on 2002-04-04 21:13 ]

[Ivan F Moala]

On 2002-04-04 19:32, a006dean wrote:
Well, I have made some headway on this thanks to the contributions of lots of folks. I picked up ZIP.EXE at:

http://www.info-zip.org/pub/infozip/

I installed the necessary ZIP.EXE in C:
and did:

Sub ZipAndMail(ByVal Subject As String)

Dim fname As String
Dim storeName As String

Application.EnableEvents = False
'
' Get rid of any existing zip file
'
storeName = "C:PPSTemp.zip"
fname = Dir(storeName)
If fname <> "" Then Kill (storeName)
'
' We will store this spreadsheet in 'c:PPSTemp.xls' so ensure that it does
' not exist before we try to save this worksheet.
'
storeName = "C:PPSTemp.xls"
fname = Dir(storeName)
If fname <> "" Then Kill (storeName)

ActiveWorkbook.SaveCopyAs (storeName)
Shell ("c:zip c:PPSTemp.Zip " + storeName)


Then I triggered the email activity with:


Public Sub SendOutlookMail()

Dim oLapp As Outlook.Application
Dim oItem As MailItem
Dim myAttachments As Attachments

On Error GoTo errorHandler

Set oLapp = CreateObject("Outlook.application")
Set oItem = oLapp.CreateItem(0)
'
' Setup a message
'
oItem.Subject = "Proposal Workbook for " + shStart.Range(cCustomerName)

'oItem.To = ""
'oItem.body = Message

Set myAttachments = oItem.Attachments
myAttachments.Add "c:PPSTemp.ZIP", olByValue, 1, "Excel Workbook"

oItem.Display
'
errorHandler:
Set myAttachments = Nothing
Set oItem = Nothing
Set oLapp = Nothing

End Sub


This seems to work fine EXCEPT that every once in awhile, it hangs and I think that the hang is related to OUTLOOK being left in execution from a prior run. I'm not sure, still investigating.

The other thing that bothers me is that this seems to depend upon OUTLOOK being on the system and I would have preferred to use the built-in dialog for sending mail:

Application.Dialogs(xlDialogSendMail).Show arg1:="", arg2:=Subject & " for " & shStart.Range(cCustomerName), arg3:=True

I would stick to the initial approach you have taken. How have you called your routines
If you have called both routines from another macro then you will have to watch out
for the fact that The zip operation may not finish before the outlook operation....could
be the cause of the crash.....

What you need to do is to Shell out to the application.....get a handle to it, check if the pprocess has finsihed...close it then do the outlook operation.

[NateO]

Howdy, I [overly] focused on:

The other thing that bothers me is that this seems to depend upon OUTLOOK being on the system and I would have preferred to use the built-in dialog for sending mail:

Ivan, agreed, may need to throw a delay in the mix:

Application.Wait Now + TimeValue("00:00:10")

Or shell out as you mentioned. I assume you're referring to testing whether Windows handle for Winzip = 0. That code would be interesting. Ontime perhaps (could get it to the second)....

Winzip doesn't necessarily act instantaneously (based on standard RAM allowances). And it's going to be variable based on users systems...

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-04 20:42 ]

[a006dean]

Well, I did to a SHELL to get the zip functionality.

And by the way, I used ZIP not WINZIP so I can run it from DOS and there are no GUI screens to pop-up if using the Eval version of WinZip.

Also looks as if another problem is that the message doesn't actually send from OUTLOOK until I launch it - it just sits in the out box. Now I don't use OUTLOOK as the default mail handler so this may be one reason.

I think I gotta figure out how to use the Dialog approach as I use that elsewhere and it works just fine. Trick is getting the file attachment.

[Ivan F Moala]

On 2002-04-04 22:24, a006dean wrote:
Well, I did to a SHELL to get the zip functionality.

And by the way, I used ZIP not WINZIP so I can run it from DOS and there are no GUI screens to pop-up if using the Eval version of WinZip.

Also looks as if another problem is that the message doesn't actually send from OUTLOOK until I launch it - it just sits in the out box. Now I don't use OUTLOOK as the default mail handler so this may be one reason.

I think I gotta figure out how to use the Dialog approach as I use that elsewhere and it works just fine. Trick is getting the file attachment.

if you decide to go with outlook then you need

oItem.Send

to send it............