Hello, board!
I am working on refining my macros... which you all have helped me develop and perfect. Now, I would like to add one last function to the end of them that will automatically send the resulting report to the people who need to get it after it finishes saving. However, I have made the mistake of running the wrong Macro on a particular dataset, so I don't want my mistake blasted out to everyone in the universe. What I'd like to do is insert a command that will ask for me to confirm that I am peachy-keen before sending the file out, but I am not sure how to do that. Can someone tell me how to edit this to include a mandatory confirmation selection before going onto the outlook part of this macro?
I think all of the code is correct, but I haven't actually tried it yet. Also, the code to create the report was condensed as I don't think it would be terribly relevant to anyone else. I've only included the very last part for saving - which I know works - and the new part for the outlook mail, which I haven't tested yet.
The file saving part is put here because I need a particular date range in the file to appear in the file name along with the current date.
I am working on refining my macros... which you all have helped me develop and perfect. Now, I would like to add one last function to the end of them that will automatically send the resulting report to the people who need to get it after it finishes saving. However, I have made the mistake of running the wrong Macro on a particular dataset, so I don't want my mistake blasted out to everyone in the universe. What I'd like to do is insert a command that will ask for me to confirm that I am peachy-keen before sending the file out, but I am not sure how to do that. Can someone tell me how to edit this to include a mandatory confirmation selection before going onto the outlook part of this macro?
I think all of the code is correct, but I haven't actually tried it yet. Also, the code to create the report was condensed as I don't think it would be terribly relevant to anyone else. I've only included the very last part for saving - which I know works - and the new part for the outlook mail, which I haven't tested yet.
The file saving part is put here because I need a particular date range in the file to appear in the file name along with the current date.
Code:
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = "C:\Documents and Settings\user\Me\Reports\Sample Report"
FileName1 = "(" & Range("C3") & ")"
FileName2 = Format(Now(), "mmddyy")
ActiveWorkbook.SaveAs Filename:=Path & "_" & FileName1 & "_" & FileName2 & ".xls", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Rows("1:5").Select
Selection.EntireRow.Hidden = True
[[I'd like to have the popup message go here]]
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.TO = "email@email.com;emailxyz@email.com"
.CC = "lorax@snoozing.com"
.BCC =
.Subject = "Sample Report"
.Body = "Hi%20All!%0A%0AAttached%20is%20the%20report%20for%20today.%20Let%20me%20know%20if%20you%20need%20anything%20else!%0A%0ALove,%0AVorlag"
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
On Error Goto 0
Set OutApp = Nothing
Set OutMail = Nothing