attaching a zip file to an e-mail using excel

[Davers]

Does that sound possible. I'm creating a series of e-mails using Excel, and I'd like to be able to automatically attach a zip file to the e-mail. Does anyone know if this is possible?

Thanks,

Dave M.

 

[Iridium]

Had a quick search of the board but haven't looked thro all the results as the threads were quite long:

http://www.mrexcel.com/board2/viewtopic.php?t=4093&highlight=zip+attach+email
http://www.mrexcel.com/board2/viewtopic.php?t=32086&highlight=zip+attach+email

Hope your answer is there - post back if not

 

[Davers]

Thanks for the links Iridium...not quite what I had in mind though...they are actually trying to zip the file and e-mail it through code...looking kind of complicated. I just needed to attach a file....it's been zipped already...I couldn't make heads nor tails of their code but I did manage to find the attaching part it is below:

Dim myAttachments As Attachments

Set myAttachments = olMail.Attachments
myAttachments.Add "C:\Documents and Settings\dmorri18\Desktop\ImportantPhoneNumbers.xls", olByValue, 1, "Excel Workbook"

Just the pertinent parts of course. Here's my whole code in case anyone else is interested:

Sub mySndMail()

Dim olApp As Outlook.Application, olMail As Outlook.MailItem
Dim FSObj As Scripting.FileSystemObject, TStream As Scripting.TextStream
Dim rngeSend As Range, strHTMLBody As String
Dim strTempFilePath As String
Dim myAttachments As Attachments

On Error Resume Next
Set rngeSend = Application.InputBox("Please select range you wish to send.", , , , , , , 8)
If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
On Error GoTo 0

Set oFSObj = CreateObject("Scripting.FilesystemObject")
strTempFilePath = oFSObj.GetSpecialFolder(2)
strTempFilePath = strTempFilePath & "\XLRange.htm"

ActiveWorkbook.PublishObjects.Add(xlSourceRange, strTempFilePath, rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish True

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
Set myAttachments = olMail.Attachments
myAttachments.Add "C:\Documents and Settings\dmorri18\Desktop\ImportantPhoneNumbers.xls", olByValue, 1, "Excel Workbook"

Set FSObj = New Scripting.FileSystemObject
Set TStream = FSObj.OpenTextFile(strTempFilePath, ForReading)

strHTMLBody = TStream.ReadAll

TStream.Close
Kill strTempFilePath

olMail.HTMLBody = strHTMLBody
olMail.Display

End Sub

This code will ask you to select a range to e-mail, and then attach the file I've specified in the code. It currently names the file: "ExcelWorkbook", you just need to change the name to what you'd like in this line:

myAttachments.Add "C:\Documents and Settings\dmorri18\Desktop\ImportantPhoneNumbers.xls", olByValue, 1, "Excel Workbook"

Thanks for the help and have a great day!!!!

Dave M.

 

[Davers]

And just in case anyone was curious as to what the heck I am doing...I have quite a few reports that get zipped and e-mailed all over the place...I do have Outlook templates created, but I still have to attach the file before I send the e-mail...after roughly 10 e-mails or so...I've wasted several minutes of my morning where I could be drinking a cappucino and eating a donut... so I've created a spreadsheet that has rows of buttons corresponding to the e-mails that need to go out to the different people. When you press the button, it creates the e-mail and attaches the correct file or files from the correct dates directory...I'm surprised I got it to work... Here is an example of an e-mail and how it chooses the correct directory based on previous days date:

TestMail.xls
	A	B	C	D	E	F
1	GoodMorning,
2
3	HerearetheDailyPerformanceReportsandIntervalReportsfortheentirecenter.
4
5	Haveagoodday,
6
7
8	DavidM.Morrison
9	WorkforcePlanningandAdministration
10	313-621-0173
11	5S290
12
13
14
15
16	Reportdate:	1/7/2004
17
18	W:\rcbwpa\Reports\HalfHourlyReports\Year2004\010704\CenterInterval010704.zip
19
Sheet1

And here is the code that creates the mail and attaches, (in this case) 2 different files...<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SendMail()

Worksheets("Sheet1").Select<SPAN style="color:#00007F">Dim</SPAN> olApp<SPAN style="color:#00007F">As</SPAN> Outlook.Application, olMail<SPAN style="color:#00007F">As</SPAN> Outlook.MailItem<SPAN style="color:#00007F">Dim</SPAN> FSObj<SPAN style="color:#00007F">As</SPAN> Scripting.FileSystemObject, TStream<SPAN style="color:#00007F">As</SPAN> Scripting.TextStream<SPAN style="color:#00007F">Dim</SPAN> rngeSend<SPAN style="color:#00007F">As</SPAN> Range, strHTMLBody<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">Dim</SPAN> strTempFilePath<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">Dim</SPAN> nowTime<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN><SPAN style="color:#00007F">Dim</SPAN> myAttachments<SPAN style="color:#00007F">As</SPAN> Attachments<SPAN style="color:#00007F">Dim</SPAN> myAttachments2<SPAN style="color:#00007F">As</SPAN> Attachments<SPAN style="color:#00007F">Dim</SPAN> myDir<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>

nowTime = Format(Range("B16").Value, "mm-dd-yy")
myDir = Range("A18").Value<SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">Resume</SPAN><SPAN style="color:#00007F">Next</SPAN><SPAN style="color:#00007F">Set</SPAN> rngeSend = Application.Range("A1:J11")<SPAN style="color:#00007F">Set</SPAN> oFSObj = CreateObject("Scripting.FilesystemObject")

strTempFilePath = oFSObj.GetSpecialFolder(2)
strTempFilePath = strTempFilePath & "\XLRange.htm"

ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
    strTempFilePath, rngeSend.Parent.Name, rngeSend.Address, xlHtmlStatic).Publish<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">Set</SPAN> olApp = CreateObject("Outlook.Application")<SPAN style="color:#00007F">Set</SPAN> olMail = olApp.CreateItem(olMailItem)<SPAN style="color:#00007F">Set</SPAN> FSObj =<SPAN style="color:#00007F">New</SPAN> Scripting.FileSystemObject<SPAN style="color:#00007F">Set</SPAN> TStream = FSObj.OpenTextFile(strTempFilePath, ForReading)<SPAN style="color:#00007F">Set</SPAN> myAttachments = olMail.Attachments
myAttachments.Add "W:\rcbwpa\Reports\DailyPerformance\DailyPerformanceReport-01-2004.zip", _
    olByValue, 1, "DailyPerformanceReport-01-2004"
    <SPAN style="color:#00007F">Set</SPAN> myAttachments2 = olMail.Attachments
myAttachments2.Add myDir, olByValue, 1, "IntervalReport-" & nowTime

strHTMLBody = TStream.ReadAll
TStream.Close
Kill strTempFilePath

olMail.HTMLBody = strHTMLBody
olMail.Subject = ("Daily Performance Report and Center Interval Reports for " & nowTime)
olMail.To = ("me@here.com")
olMail.CC = ("you@there.com")
olMail.Display<SPAN style="color:#00007F">Set</SPAN> FSObj =<SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Set</SPAN> olApp =<SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Set</SPAN> olMail =<SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Set</SPAN> TStream =<SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

I just think this is kind of neat even though it's promoting my laziness.... I think it saves me maybe 3 minutes in the morning...

Anyway, it was fun creating.....thanks for all the help by the way....

Everyone have a good day,

Dave M.