Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Want to ZIP and Email the current workbook

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    Code:
    Dim fname As String 
    fname = ActiveWorkbook.FullName
    Close fname with:
    Code:
    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:
    Code:
    Source = "C:Final.xls" '"C:myfile.xls"
    to
    Code:
    Source = fname
    Now use the e-mail code (depending on your e-mail system).

    Then open the file with:
    Code:
    Workbooks.Open FileName:=fname
    You may want to use:
    Code:
    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.,:

    Code:
    activeworkbook.path
    Hope this helps.

    _________________
    Cheers, NateO

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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    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 ]

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.



    Kind Regards,
    Ivan F Moala From the City of Sails

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Code:
    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 ]

  9. #9
    New Member
    Join Date
    Apr 2002
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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............
    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •