Sending Email attaching a Zip file

Terry P · Dec 7, 2002

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

Terry P · Dec 7, 2002

Jack
It appears the usual vba will only send the current workbook or sheet as is. I don,t know much about API's but TsTom is coming up with something that I may be able to use.

Many thanks and warn you that I might come back again.
Terry
Using Excel XP

Jack in the UK · Dec 7, 2002

No stress feel free Jack

XL-Dennis · Dec 7, 2002

Hi Terry,

While TsTom is taking a look on Outlook / Outlook Express via MAPI there exist other alternatives as well.

I recently finished a work where the client preferred to use a total different approach.

<PRE>
Sub Send_Mail_ComMail()

Dim wbBok As Workbook

Dim wsBlad As Worksheet

Dim rnOmrade As Range

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 = Range("F3").Value

'Senders e-mail

stFran = Range("F4").Value

'Recipient e-mail

stTill = Range("F5").Value

'Subject

stArende = Range("F6").Value

'Create attachment

stBilagaNamn = "c:Bilaga" & Date & ".xls"

With Application

.ScreenUpdating = False

.DisplayAlerts = False

End With

wsBlad.Copy

ActiveWorkbook.SaveAs stBilagaNamn

ActiveWindow.Close

'Create bodytext

Range("C11:H25").Copy

Workbooks.Add -4167

Selection.PasteSpecial xlValues

ActiveWorkbook.SaveAs Filename:="c:Bodytext.txt", FileFormat:=xlTextMSDOS

ActiveWindow.Close

'Create BAt-file

Open "XL.bat" For Output As #1

Print #1, stComMailexe & " " & "-host=" & stMailVard & " " & "-from=" & stFran & " " & "-to=" & stTill & " " & "-msg=c:bodytext.txt" & " " & "-subject=" & stArende & " " & "-attach=" & stBilagaNamn

Close #1

'Execute

RetVal = Shell("c:XL.bat", 0)

'Make a delay enablighs Shell to finish up

'and then kill some of files

Application.Wait Now + TimeValue("00:00:10")

Kill "c:Bilaga" & Date & ".xls"

Kill "c:Bodytext.txt"

With Application

.ScreenUpdating = False

.DisplayAlerts = False

End With

End Sub

</PRE>

Instead of creating the attachmentfile as above You replace it with Your zip-file and the path to the zipfile.

In order to have it to work You need to download a small software - Command Mail - which is a freeware and can be downloaded from following site:

http://www.xwebware.com/products/commail/index.html

Kind regards,
Dennis

Ps Propably some of the more regular posters may have objection with this solution - They are welcome :wink: Ds

Terry P · Dec 7, 2002

Thanks Dennis
I'll try it tommorow (It's a good excuse to get out of chores).
Terry
This message was edited by Terry P on 2002-12-07 17:42

Ivan F Moala · Dec 7, 2002

Dennis have given you one option and I'm sure Tom will come up with another.
Here is another option (NB: Not fully tested Xl2000 WinXp)

Notes:
1) Assumes OutLook is your mailer
2) Must be run on a workbook that is Open
- will close it after
3) Change constants to reflect where your files are/going.
4) Uses API to determine when the Winzip process is finished rather then a delayed time.

<PRE>Option Explicit

Private Declare Function OpenProcess Lib "kernel32" ( _

ByVal dwDesiredAccess As Long, _

ByVal bInheritHandle As Long, _

ByVal dwProcessId As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" ( _

ByVal lnghProcess As Long, _

lpExitCode As Long) As Long

'// If your going to be working with Systems that support security

'// settings eg NT, XP the access will be checked against any

'// security descriptor for the target process, so use this Const

'// Sets all possible access flags for the process object.

Private Const PROCESS_ALL_ACCESS = &H1F0FFF

'// Note spaces important

Private Const ZipExePath As String = "C:Program filesWinzip"

Private Const ZipCom As String = "Winzip32 -a "

Private Const strDest As String = "C:WINDOWSTempMyTest.zip"

Public Function ShlProc_IsRunning(ShellReturnValue As Long) As Boolean

Dim lnghProcess As Long

Dim lExitCode As Long

'//Get the process handle

lnghProcess = OpenProcess(PROCESS_ALL_ACCESS, 0&, ShellReturnValue)

If lnghProcess <> 0 Then

 '// The GetExitCodeProcess function retrieves the

 '// termination status of the specified process.

GetExitCodeProcess lnghProcess, lExitCode

If lExitCode <> 0 Then

 '// Process still ALIVE!

ShlProc_IsRunning = True

Else

 '// YES...finished @ last

ShlProc_IsRunning = False

End If

End If

End Function

Sub ShellZipAndEmailIt()

Dim ZipItPID As Long

Dim strSource As String

Dim OLook As Object

Dim Mitem As Object

Dim OlAttachment As Object

strSource = "C:WINDOWSTempMyTest.xls"

If ActiveWorkbook.Name = ThisWorkbook.Name Then

MsgBox "Cannot Zip and email this workbook" & vbCr & _

"select another workbook and run this again."

End

End If

With ActiveWorkbook

.SaveAs strSource

.Close False

End With

'//

'// When you Shell out to an Application the Return Value

'// is the Applications Task ID

'// in order to determine if it has Terminated we need to check

'// if there is an existing process object

'// > OpenProcess function opens an existing process object.

'//


On Error Resume Next

'//

ZipItPID = Shell(ZipExePath & ZipCom & strDest & " " & strSource, vbNormalFocus)

On Error GoTo 0

If ZipItPID = 0 Then MsgBox "NoGo!" & vbCr & "Check your Path": End

'// Ok, lets loop until the App process is terminated!

Do While ShlProc_IsRunning(ZipItPID) = True

DoEvents

Loop

Set OLook = CreateObject("Outlook.Application")

Set Mitem = OLook.CreateItem(0)

Set OlAttachment = Mitem.Attachments

OlAttachment.Add strDest, olByValue, 1, "Updated Excel Workbook"

With Mitem

.To = "ivanmoala@xtra.co.nz"

.Subject = "New Workbook"

.Body = "Here is the latest update"

.Send

End With

ErrorHandler:

Set OLook = Nothing

Set Mitem = Nothing

Set OlAttachment = Nothing

'// Cleanup

Kill strSource

Kill strDest

End Sub

</PRE>

Terry P · Dec 7, 2002

This has been a marvelleous response and I thank you all.
It's nearly 1 a.m here in dull old UK so I'm off to bed. I'll try all your suggestions tomorrow.

By way of explaination, It's my users whose life I'm trying to make as simple as possible as some are in their 70's.

Forgive me for being cheeky but I had recently posted my very first post which I never resolved but with brains like your there may be a solution:
It seems very simple but I can't get my head around it.

I want my users to enter dates in column A as d/mm/yy eg 2/10/02 which automatically converts to 2 Oct 02.
I am using the worksheet_change function so thought I could enter something like if activecolumn = B and column A <> Like #/##/## then messbox("Re-enter date") etc.
I've tried various ways including validation to no avail. Any hints?

My very best wishes
Terry
PS Ivan, hurry up with your wesite. I suppose it's people like me taking your time up!

Ivan F Moala · Dec 7, 2002

Hi Terry

If I understand you correctly then
Try this ??

<pre/>

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Not Target.Text Like "*/*/*" Then
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
MsgBox "Please reenter date as > d/mm/yy eg 2/10/02"
Else
Target.NumberFormat = "d mmm yy"
End If
End If
End Sub

</pre>

Legacy 98055 · Dec 7, 2002

Wow!
No need for me to post! If all else fails, I'll post a code example I use that automatically sends me email from work every day using the default mail client.(Uses Mapi)(Outlook Express) Just out of curiosity? Are your users required to zip up their file manually? Reason I ask is the same procedure I wrote about above uses Winzip's command line to automatically zip up many files and then sends the attachment to my mail account. Very easy to use and free if you have winzip.
Tom

whiteghost · Dec 8, 2002

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

Ivan F Moala · Dec 8, 2002

On 2002-12-07 23:25, TsTom wrote:
Wow!
No need for me to post! If all else fails, I'll post a code example I use that automatically sends me email from work every day using the default mail client.(Uses Mapi)(Outlook Express) Just out of curiosity? Are your users required to zip up their file manually? Reason I ask is the same procedure I wrote about above uses Winzip's command line to automatically zip up many files and then sends the attachment to my mail account. Very easy to use and free if you have winzip.
Tom

Hi Tom
Please post your code for O Express using MAPI as not everyone has outlook

I would be interested in this.
Also note: The code I supplied does zip the file and then emails it...can also automate
the extraction of the zip from upon delivery
But thats another post.
Thanks in advance

Sending Email attaching a Zip file

Terry P

Active Member

Terry P

Active Member

Jack in the UK

Well-known Member

XL-Dennis

Well-known Member

Terry P

Active Member

Ivan F Moala

MrExcel MVP

Terry P

Active Member

Ivan F Moala

MrExcel MVP

Legacy 98055

Guest

whiteghost

Well-known Member

Ivan F Moala

MrExcel MVP

Similar threads

Share this page

Sending Email attaching a Zip file

Active Member

Active Member

Well-known Member

Well-known Member

Active Member

MrExcel MVP

Active Member

MrExcel MVP

Legacy 98055

Guest

Well-known Member

MrExcel MVP

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock