Hi, wondering if anyone can help me. I have an Excel spreadsheet i
which I have a macro which filters the data several times, each tim
copying the data to a new Excel sheet and saving them. However, ther
are 17 of them and I then have to go through each individually and zi
them up before emailing them out. Does anybody know of anyway at all
could get them to automatically zip themselves and email them out so
don't have to do it manually? (I know the code to email them out, it'
just I'm not sure how to automatically zip them before it does this)
Any help would be greatly appreciated

--
krabopl
-----------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...fo&userid=2977
View this thread: http://www.excelforum.com/showthread.php?threadid=49487

Hi Krabople,

See Ron De Bruin at:

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

and

http://www.rondebruin.nl/windowsxpzip.htm


---
Regards,
Norman


"krabople" wrote in
message ...

Hi, wondering if anyone can help me. I have an Excel spreadsheet in
which I have a macro which filters the data several times, each time
copying the data to a new Excel sheet and saving them. However, there
are 17 of them and I then have to go through each individually and zip
them up before emailing them out. Does anybody know of anyway at all I
could get them to automatically zip themselves and email them out so I
don't have to do it manually? (I know the code to email them out, it's
just I'm not sure how to automatically zip them before it does this).
Any help would be greatly appreciated.


--
krabople
------------------------------------------------------------------------
krabople's Profile:
http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=494872

I have information on my site
http://www.rondebruin.nl/windowsxpzip.htm

See also the WinZip page


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


"krabople" wrote in message
...

Hi, wondering if anyone can help me. I have an Excel spreadsheet in
which I have a macro which filters the data several times, each time
copying the data to a new Excel sheet and saving them. However, there
are 17 of them and I then have to go through each individually and zip
them up before emailing them out. Does anybody know of anyway at all I
could get them to automatically zip themselves and email them out so I
don't have to do it manually? (I know the code to email them out, it's
just I'm not sure how to automatically zip them before it does this).
Any help would be greatly appreciated.


--
krabople
------------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=494872

Brilliant, thanks very much for your hel

--
krabopl
-----------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...fo&userid=2977
View this thread: http://www.excelforum.com/showthread.php?threadid=49487

I have tried using the Winzip code shown on the above site but it
doesn't seem to understand the "shellandwait" part of the code. Any
ideas why?


--
krabople
------------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=494872

Hi krabople

Have you read this
http://www.rondebruin.nl/zip.htm#Functions


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


"krabople" wrote in message
...

I have tried using the Winzip code shown on the above site but it
doesn't seem to understand the "shellandwait" part of the code. Any
ideas why?


--
krabople
------------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=494872

Thanks again Ron, I had missed that actually. However I have now copied
it all into another module but it still doesn't seem to be working. It
creates another copy of the spreadsheet and then seems to get stuck. No
error message appears but I cannot do anything else in Excel until I go
into the VBA window and press the stop button. Any idea why? I have
tried waiting a minute or so but nothing happens.


--
krabople
------------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=494872

(Note: you must have a registered copy of WinZip)


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


"krabople" wrote in message
...

Thanks again Ron, I had missed that actually. However I have now copied
it all into another module but it still doesn't seem to be working. It
creates another copy of the spreadsheet and then seems to get stuck. No
error message appears but I cannot do anything else in Excel until I go
into the VBA window and press the stop button. Any idea why? I have
tried waiting a minute or so but nothing happens.


--
krabople
------------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=494872

Ah right, mine's just an evaluation copy. Ah well, I'll have to try an
persuade the company to invest! Thanks again for all your help.

Be

--
krabopl
-----------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...fo&userid=2977
View this thread: http://www.excelforum.com/showthread.php?threadid=49487

If you use Windows XP you can use the other code maybe

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


"krabople" wrote in message
...

Ah right, mine's just an evaluation copy. Ah well, I'll have to try and
persuade the company to invest! Thanks again for all your help.

Ben


--
krabople
------------------------------------------------------------------------
krabople's Profile: http://www.excelforum.com/member.php...o&userid=29774
View this thread: http://www.excelforum.com/showthread...hreadid=494872

I have Windows XP with SP2. I use Winzip but its not registered. Can I
use any of the above code. If yes can you please tell me the url from
where to copy the code.
Thanks in Advance
Vikesh Jain

I use Winzip but its not registered
You can't use the WinZip code then

Use this
http://www.rondebruin.nl/windowsxpzip.htm


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


"Vikesh Jain" wrote in message ups.com...
I have Windows XP with SP2. I use Winzip but its not registered. Can I
use any of the above code. If yes can you please tell me the url from
where to copy the code.
Thanks in Advance
Vikesh Jain

I tried the code. First it is saving the open file to the default
location and then zipping it however when it comes to email it is
giving me an error at the followig line of the code:
Set OutApp = CreateObject("Outlook.Application")
Please suggest.
Regards, Vikesh Jain

Ron,
I have some code from your site that WAS working, but now is NOT attaching
the file to the email. I am creating the zip file, but also another file with
an "E" attached that I want to use as the attachment instead of the zip file.
The email is created and sent, but without the file attached. Could you
review the code and try to determine what the issue is? Thanks much!
PS..Flying to Amsterdam today..do you live close...I might like to take you
out for a drink! Here's my code:

Sub ZipMailWithDeleteOption()
Dim strDate As String, DefPath As String, strbody As String
Dim oApp As Object, OutApp As Object, OutMail As Object
Dim FileNameZip, FileNameXls, FileNameEmail
Dim password As String

'Checks to See If A Directory Exists, If Not, Creates It
MyDirectory = ActiveWorkbook.Path & "\" & "Zipped Reports"
DirTest = Dir$(MyDirectory, vbDirectory)
If DirTest = "" Then
MkDir MyDirectory
DoEvents 'just to make sure it is there
End If
ChDir MyDirectory

DefPath = MyDirectory

If Right(DefPath, 1) < "\" Then
DefPath = DefPath & "\"
End If

strDate = Format(Now, " dd-mmm-yy h-mm-ss")
'Create the temporary xls file and zip file name
FileNameZip = DefPath & Left(ActiveWorkbook.Name,
Len(ActiveWorkbook.Name) - 4) & ".zip"
FileNameXls = DefPath & Left(ActiveWorkbook.Name,
Len(ActiveWorkbook.Name) - 4) & "Z" & ".xls"
FileNameEmail = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)
& "E" & ".xls"

If Dir(FileNameZip) = "" And Dir(FileNameXls) = "" Then

'Make a copy of the activeworkbook
ThisWorkbook.SaveCopyAs FileNameEmail
'ThisWorkbook.Activate
ThisWorkbook.SaveCopyAs FileNameXls

'Create empty Zip File
NewZip (FileNameZip)

'Copy the xls file into the compressed folder
Set oApp = CreateObject("Shell.Application")
oApp.Namespace(FileNameZip).CopyHere FileNameXls

'Keep script waiting until Compressing is done
On Error Resume Next
Do Until oApp.Namespace(FileNameZip).items.Count = 1
Application.Wait (Now + TimeValue("0:00:01"))
Loop
On Error GoTo 0

ChDir MyDirectory

'INSERT EMAIL CODE HERE!
'Create the mail
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Attached is our Big Picture Report" & vbNewLine &
vbNewLine & _
strDate & vbNewLine & _
"" & vbNewLine & _
"Have a Nice Day!" & vbNewLine & _
""

On Error Resume Next
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = FileNameEmail
'.Subject = FileNameXls
.Body = strbody
.Attachments.Add FileNameEmail
.Send 'or use .Display
'.Display
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%S"


End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
Set oApp = Nothing

'Delete the temporary xls file
Kill FileNameXls
Kill FileNameEmail

ThisWorkbook.Activate

MsgBox "Your Zipfile is Stored He " & FileNameZip

Call CapturePlumberData

Msg = "Do You Want to Delete This File and Keep Only the Zip File?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then Call DeleteThisFile

Else
MsgBox "A ZipFile With This File Name Already Exist." & Chr(10) _
& "Delete It and Try Again!"
End If

Application.ScreenUpdating = False

Application.ThisWorkbook.Activate
Worksheets("Global Setup").Select
Range("CA3").Select
password = Range("CA3").Value
Range("L5").Select

Worksheets("Team Scorecard").Activate

Application.ThisWorkbook.Unprotect (password)
ActiveSheet.Unprotect (password)

Application.ScreenUpdating = True

ActiveSheet.Shapes("Button 28").Select
Selection.Characters.Text = "File Zipped" & Chr(10) & "& Mailed"
With Selection.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
Range("A1").Select

ActiveSheet.Protect (password)
Application.ThisWorkbook.Protect (password), structu=True

End Sub

Thanks!

Hi Ron,
I unistalled the Winzip program and then tried and it is sneding the
email. But the problem is that in the code we have hardcoded the
Message and the receipient address. Can we add some Msgbox to it which
ask for the address and the message before sending the email??
Thanks
Vikesh Jain

Hi Vikesh

Change Send to Display in the code



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


"Vikesh Jain" wrote in message oups.com...
Hi Ron,
I unistalled the Winzip program and then tried and it is sneding the
email. But the problem is that in the code we have hardcoded the
Message and the receipient address. Can we add some Msgbox to it which
ask for the address and the message before sending the email??
Thanks
Vikesh Jain