Sub Mail_Click()
Dim Msg As String, Ans As Variant
Dim wb1 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Msg = "Clicking 'Yes' will Directly email Your PO Requisition!"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set wb1 = ActiveWorkbook
'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = Replace(wb1.Name, ".xlsm", "") & " #" & Range("H5") & " " & Format(Now, "dd-mmm-yy")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = "XXXXX@XXXX.com"
.CC = ""
.BCC = ""
.Subject = "PO Requisition #" & " " & Range("H5")
.Body = "Please Find the file attached."
.Attachments.Add TempFilePath & TempFileName & FileExtStr
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0
'Delete the file
Kill TempFilePath & TempFileName & FileExtStr
Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your File was successfully sent!"
Case vbNo
GoTo Quit:
End Select
Quit:
End Sub
