attaching a zip file to an e-mail using excel

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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:

Code:
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:

Code:
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:

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

Thanks for the help and have a great day!!!! (y)

Dave M.
 

Davers

Well-known Member
Joined
Sep 17, 2002
Messages
1,165
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... :p 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... :LOL: Here is an example of an e-mail and how it chooses the correct directory based on previous days date:
TestMail.xls
ABCDEF
1GoodMorning,
2
3HerearetheDailyPerformanceReportsandIntervalReportsfortheentirecenter.
4
5Haveagoodday,
6
7
8DavidM.Morrison
9WorkforcePlanningandAdministration
10313-621-0173
115S290
12
13
14
15
16Reportdate:1/7/2004
17
18W:\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.... (y) I think it saves me maybe 3 minutes in the morning... :LOL:

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

Everyone have a good day,

Dave M.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,163
Messages
5,768,555
Members
425,481
Latest member
ihumanl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top