Improved Code to email a saved .xlsx file

[jamestomi]

Please help, I want below code after saving the excel file to email it to specific address. I appreciate all your comments and help. Thanks.

Sub NextReq()
Range("J8").Value = Range("J8").Value + 1
Range("A4:D8,A11:D14,F4:H4,F6:H6,F8:H8,A16:I37,I40").ClearContents
End Sub

Sub SaveRequisitionWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "C:\StoresReq" & Range("J8").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
Mail_workbook_Outlook_2
ActiveWorkbook.Close
NextReq
End Sub

Sub Mail_workbook_Outlook_2()
Dim NewFN As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

TempFilePath = Environ$("temp") & "\"
TempFileName = "C:\StoresReq" & Range("J8").Value & ".xlsx" & " " & Format(Now, "dd-mmm-yyyy h:mm:ss")




Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = "james.tomi@lixm.co.mz"
.CC = ""
.BCC = ""
.Subject = "URGENT - Branch Stores Requisition"
.Body = "Please note requirements urgently needed on attached requisition"
.Attachments.Add TempFilePath & TempFileName & FileExtStr
.Send 'or use .Display
End With
On Error GoTo 0
End Sub

 

[jim may]

An excellent source for code regarding e-mailing (using outlook)..

Mail from Excel and make/mail PDF files (Windows)

 

[jamestomi]

An excellent source for code regarding e-mailing (using outlook)..Mail from Excel and make/mail PDF files (Windows)

Thanks Jim I appreciate greatly