Sending Email attaching a Zip file

[Terry P]

I am able to zip the active Workbook but I need to email the zipped file rather than the Workbook.
Any ideas please?
This message was edited by Terry P on 2002-12-07 10:22

 

[Legacy 98055]

Hey Terry.
If you are unable to find a solution, let me know. I am going to compile the above into a COM add-in. The Excel developer would then only need to provide a few simple arguments. The last two being optional.

• The full path of the file being zipped.
• The full path of the zip file being created.
• The email address you will be sending to.
• Subject
• Body

Tom

PS
If anyone can recommend any other arguments, please say on.
_________________
Using Office 2000 on Windows 2000
This message was edited by TsTom on 2002-12-09 18:10

 

[Terry P]

Tom
I'm almost ahead of you.
When I get it right which hopefully I'll be in touch.
Terry

 

[XL-Dennis]

Hi Tom,

If anyone can recommend any other arguments, please say on.

First of all, turning it to a COM-add in is a good idea

Although people who´s running XL 97 cannot use it at all. Therefore it should be developed both as a COM and as a tradtional XLA.

Following argument should be optional:
- CC
- BCC

...and the three fields, TO/CC/BCC, should be able to handle several recepients, i e as an array.

Please let me know if I can assist You in anyway.

Kind regards,
Dennis

 

[Terry P]

OK Tom
Here's what I've got.
It still need tidying up and isn't very high brow:
Sub EmailArchive()
On Error Resume Next

v = MsgBox("I am about to archive the file to the floppy disk drive(A. It will be saved as Archive.zip. Please ensure an EMPTY disk is available in the drive. Label the disk as appropriate.", 49, "Archive?.")
If v <> 1 Then Exit Sub

datenow1 = Range("Index!n2")
datenow2 = Range("Index!n3")
dn1 = Right$(datenow1, 2)
dn2 = Right$(datenow2, 2)

savefile = "C:windowstempAr" + dn1 + "to" + dn2 + ".XLS"

ActiveWorkbook.SaveCopyAs savefile

savefil = "C:acctspkpkzip -ex-& a:Archive.zip C:windowstempAr" + dn1 + "to" + dn2 + ".XLS"

RetVal = Shell(savefil, 1)

Send_Mail_ComMail
End Sub

Sub Send_Mail_ComMail()
Dim wbBok As Workbook
Dim wsBlad As Worksheet
Dim RetVal As Double
Dim stTill As String, stFran As String, stComMailexe As String
Dim stBodyText As String, stHost As String, stBilagaNamn As String
Dim stMailVard As String, stArende As String

Set wbBok = ThisWorkbook
Set wsBlad = wbBok.ActiveSheet

'Mailsoftware
stComMailexe = "c:commail.exe"

'Mailhost
stMailVard = UserForm17.TextBox1.Text

'Senders e-mail
stFran = UserForm17.TextBox2.Text

'Recipient e-mail
stTill = "online-impact@ntlworld.com"

'Subject
stArende = "Emailed File"

'Create attachment
stBilagaNamn = "A:Archive.zip"

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

'Create BAt-file
Open "XL.bat" For Output As #1
Print #1, stComMailexe & " " & "-host=" & stMailVard & " " & "-from=" & stFran & " " & "-to=" & stTill & " " & "-subject=" & stArende & " " & "-attach=" & stBilagaNamn
Close #1

'Execute
RetVal = Shell("C:AcctsXL.bat", 0)

With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With

End Sub

I may have a problem getting the A: drive to update to the fact it now holds the Archive as I occasionally get the message "A:Archive.zip not found" but it's still beta.
It's most likely to be my age!
Terry

 

[Legacy 98055]

Good ole PK. Forgot all about it.
Dennis. Thanks for the offer. One question and I think I can handle the rest.

• Seeing that this utility will need WZZIP.EXE which should be located in the Crogram Files-Winzip folder. I do not know how to find out via API or registry functions how to determine if the user did a custom install and placed Winzip in a folder other than the default listed above. If you don't know off the top of your head, then don't worry about it. I'm sure I can find out via a search. Just taking advantage of your offer.

Thanks,
Tom

 

[Terry P]

Ok
All is fine using commail BUT...
When the first macro runs and now archives to C:. It then calls the second macro.
The second marco, to email it, can't initially find the C:Archive.zip.
It does eventually find it but it might take one or two goes.
Any ideas please.
Terry

 

[XL-Dennis]

Seeing that this utility will need WZZIP.EXE which should be located in the Crogram Files-Winzip folder. I do not know how to find out via API or registry functions how to determine if the user did a custom install and placed Winzip in a folder other than the default listed above. If you don't know off the top of your head, then don't worry about it. I'm sure I can find out via a search. Just taking advantage of your offer.

This is the best source which I use on a regular basis in both VB / VBA:

"Use the Registry API to Save and Retrieve" Setting
http://support.microsoft.com/default.aspx?scid=kb;en-us;145679

No need to use the two free calls to MS :wink:

Kind regards,
Dennis

 

[XL-Dennis]

Terry,

The second marco, to email it, can't initially find the C:Archive.zip.
Any ideas please.

I believe You need to obtain when the zip-process is done Ivan showed a nice solution to catch when the process is finished - see item 4.

Kind regards,
Dennis

 

[Terry P]

Ignore the last
I think it's ok 'cos the Function is Public.Thanks Dennis.
This message was edited by Terry P on 2002-12-10 06:21

 

[Terry P]

My thanks to you all.
With a combination of commail and thanks to Dennis for reminding me of Ivan's solution to check one process is finished before starting the next.IT WORKS GREAT!!!
I look forward to see what solutions you are working on for the greater good are. I.e. Addins etc
My thanks also for your patience shown from pro's to a pure amateur.
Happy Christmas
Terry