Save As & email via Outlook with VBA???
Posted by Tom Urtis on June 12, 2001 7:16 PM
Hello fellow Excel-ites:
In Excel 2000 I maintain a template file with a macro that manipulates the day's business transactions, to ultimately display a snappy summary report.
This template file must then be Saved As in the file name syntax "mm-dd-yyyy Report" (today's report is called "06/12/2001 Report"), and emailed via Outlook to the bosses, which I have been doing manually.
Three questions regarding possibly adding VBA code at the end of my data manipulation macro:
(1) What VBA code (if any) would Save As the template file in this syntax, considering that the current date would be part of the Saved As file name.
(2) Could such code specify the drive & folder where the Saved As file should be placed? I place these saved as files in J:\Transactions\Reports.
(3) That Saved As file is then emailed internally via Outlook to John Doe, copy to Dewey Cheatem and Mae Onayz. Could VBA handle that task?
Any direction would be greatly appreciated. TIA.
Tom Urtis
Posted by Ivan F Moala on June 13, 2001 3:28 AM
Hi Tom
This may help you out...change as required
Note no error checking....+ note the file format
for the date !! and NOT mm/dd/yy.
Sub SaveAndEmail_Rpt()
Dim FNm As String
Dim myDir As String
Dim RptNm As String
Dim recipients
Dim subject As String
Dim objOutLook As Object
Dim objOutlookMsg As Object
Dim objoutlookA As Object
RptNm = " Finacial Report"
recipients = Array("phoenix@voyager.co.nz", "ivan.moala@gwf.com.au")
subject = Format(Now, "mm-dd-yy") & RptNm
FNm = "J:\Transactions\Reports\" & subject & ".xls"
ActiveWorkbook.SaveAs Filename:=FNm
Set objOutLook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutLook.CreateItem(olMailItem)
Set objoutlookA = objOutlookMsg.Attachments.Add(FNm)
With objOutlookMsg
.recipients.Add recipients(0)
.recipients.Add recipients(1)
.subject = subject
End With
With objOutlookMsg
.Send
End With
Set objOutLook = Nothing
Set objOutlookMsg = Nothing
Set objoutlookA = Nothing
End Sub
