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