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