Excel Macros : Send Email with VBA

The Code I am sharing with you today is capable of sending active sheet of your workbook through outlook mail to email ID specified by you. You will not get any warning messages and the email will be sent using outlook profile available on your machine.

This code saves active sheet to temp file and then email it to the recipient. Once the mail has been sent the temp file is deleted by the code.

You will need to copy this code to regular VBA module of your workbook

Sub EmailActiveSheetWithOutlook() Dim oApp, oMail As Object, _ tWB, cWB As Workbook, _ FileName, FilePath As String Application.ScreenUpdating = False 'Set email id here, it may be a range in case you have email id on your worksheet Mailid = "yogesh@yogeshguptaonline.com" 'Write your email message body here , add more lines using & vbLf _ at the end of each line Body = "Please find enclosed " & vbLf _ & vbLf _ & "Thanks & Regards" 'Copy Active Sheet and save it to a temporary file Set cWB = ActiveWorkbook ActiveSheet.Copy Set tWB = ActiveWorkbook FileName = "Temp.xls" 'You can define the name FilePath = Environ("TEMP") On Error Resume Next Kill FilePath & "\" & FileName On Error GoTo 0 Application.DisplayAlerts = False tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=56 Application.DisplayAlerts = True 'Sending email through outlook Set oApp = CreateObject("Outlook.Application") Set oMail = oApp.CreateItem(0) With oMail .To = Mailid .Subject = "Update Message Subject here" .Body = Body .Attachments.Add tWB.FullName .send End With 'Delete the temporary file and restore screen updating tWB.ChangeFileAccess Mode:=xlReadOnly Kill tWB.FullName tWB.Close SaveChanges:=False cWB.Activate Application.ScreenUpdating = True Set oMail = Nothing Set oApp = Nothing End Sub

Download file with VBA code to send active sheet through outlook