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

 

[Ivan F Moala]

On 2002-12-08 00:35, whiteghost wrote:
another compression agent is powerarchiver which is free and can be downloaded at http://www.powerarchiver.com I regularly used this for e-mailing workbooks

Thanks for that...I download it

 

[XL-Dennis]

Tom,

Hi Tom
Please post your code for O Express using MAPI as not everyone has outlook
I would be interested in this.

I agree with Ivan and looking forward to take part of such solution

Kind regards,
Dennis

 

[Legacy 98055]

Ok
Will send when I get to work tonight. Mail API. MAPI

 

[Terry P]

Dear Ivan
Your code for the date problem works perfectly in a new sheet (of course) but there is something that prevents it working on the sheet I want it to.
I've cleared all the formating in the date column from dd mmm yy to General and it still won't have it. I'd be grateful for any pointers but please don't spend too much time on it.

 

[XL-Dennis]

Terry,

The problem is propably due to formatting.

Select the range and format it to General.
If this does not solve the problem then You may need to delete the range.

Kind regards,
Dennis

 

[Terry P]

Dear Dennis
Thanks for replying.
I did as suggested and got some way with it.
The problem is that if the cell is blank all is well but when going back to alter a correct date it produces the error message because the cell is set to d mmm yy again.
Any further thoughts please?
kind regards
Terry

 

[XL-Dennis]

Hi Terry,

Please try the below procedure:

<PRE>
<FONT color=blue>Private <FONT color=blue>Sub </FONT></FONT>Worksheet_Change(<FONT color=blue>ByVal</FONT> Target<FONT color=blue> As</FONT> Range)

<FONT color=blue>If </FONT>Target.Column = 1 Then

<FONT color=blue>If </FONT>Not Target.Text Like "*/*/*" Then

<FONT color=blue>With </FONT>Application

.EnableEvents =<FONT color=blue> False</FONT>

.Undo

.EnableEvents =<FONT color=blue> True</FONT>

<FONT color=blue>End With</FONT>

MsgBox "Please reenter date as > d/mm/yy eg 2/10/02"

<FONT color=blue>Else</FONT>

<FONT color=blue>If </FONT>Not IsEmpty(Target)<FONT color=blue> Then </FONT>Target.NumberFormat = "d mmm yy"

<FONT color=blue>End If</FONT>

<FONT color=blue>End If</FONT>

<FONT color=blue>End Sub</FONT>
</PRE>

Kind regards,
Dennis

 

[Terry P]

sorry Dennis but I tried it in an new workbook and it didn't work

 

[Terry P]

I have found a workaround for my date problem thanks to your input. It made me think more laterally.
The date is used to placed the month number across the row in the M column so in the Worksheet_change I entered
If Left(x, 2) = "$B" Then ActiveCell.Offset(0, 11).FormulaR1C1 = "=MONTH(RC[-12])"
If IsError(ActiveCell.Offset(0, 11).Value) Then ActiveCell.Offset(0, 11).ClearContents: ActiveCell.Offset(0, -1).Select: v = MsgBox("Please re-enter in the 2/4/02 format.", 0, "Incorrect date entry").
My thanks to all for your ideas on this and sorry for taking up your time but I hope this may help others.
I am still yet to get to grips with the emailing but as soon as I crack it I send my thanks.
Terry

 

[Legacy 98055]

Try this out. This code was edited on the fly from a VB project but you should be able to adapt it. Let me know if you have any problems. I'll post a slimmed down version for zipping only one file in a few moments.
Create a user form(useform1) draw out the two mapi controls. There is no code on the form and the form will not actually be shown, only loaded.<pre>Option Explicit

Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)

Sub SendFiles()
Dim fs As New FileSystemObject, f As Folder, ff As Files, f1 As file, n As Long
Dim SendAddress As String, ZipFile As String, ZipList As String, ShellString As String

'there are a bunch of files in this folder which are added to a list file.
'is a simple text file which will be used by Winip's commandline
If fs.FolderExists("L:Whse_DroplotDroplot ArchiveLRDTMessages") Then
Set f = fs.GetFolder("L:Whse_DroplotDroplot ArchiveLRDTMessages")
Set ff = f.Files
For Each f1 In ff
CreateZipFile f1.Path
Next
End If

'defining the zip files full path to use as an argument
ZipFile = "C:JBDCSCollectorLRDT_DATA_" & Format(Now - 1, "MMDDYYYY") & ".zip"

'defining the list text files path to use as an argument
ZipList = "C:JBDCSCollectorLRDT_DATA_" & Format(Now - 1, "MMDDYYYY") & ".LST"

'defining the full argument to pass to the following shell function
ShellString = "C:Program FilesWinZipwzzip.exe " & ZipFile & " @" & ZipList

'run the shell on winzip's command line utility. Zips up all of the files in the
'list file into one zip file
Shell ShellString

'this procedure is called in a loop. You may or may not need this
'I just wanted to slow down the mailing a bit to avoid runtime problems
Sleep 10000

'define the address you will be mailing to
SendAddress = "tstom@hotmail.com"

If Not send_mail(SendAddress, "Subject Here", "Body Here", ZipFile) Then MsgBox "Failed"

End Sub

Public Function send_mail(sendto As String, subject As String, _
text As String, AttachPath As String) As Boolean
'Add The MAPI Components and
'add a MAPI Session and MAPI mail control to your form

On Error GoTo ErrHandler
Unload UserForm1
With UserForm1.MAPISession1
.DownLoadMail = False
.LogonUI = True
.SignOn
UserForm1.MAPIMessages1.SessionID = .SessionID
End With

With userform1.MAPIMessages1
.Compose
.RecipAddress = sendto
.AddressResolveUI = True
.ResolveName
.AttachmentPathName = AttachPath
.MsgSubject = subject
.MsgNoteText = text
Sleep 2000
.Send False
DoEvents
End With
Unload UserForm1
DoEvents
send_mail = True
Exit Function
ErrHandler:
End Function</pre>
Tom

Here's the link for the command line utility from Winzip:
http://www.winzip.com/getsite.cgi?wzcline.exe
_________________
Using Office 2000 on Windows 2000
This message was edited by TsTom on 2002-12-08 19:06