How can I send an Excel file via email?

Question

I have to create and send an Excel file every month via email to my boss. I want to use a VBA code to send the file as attachment, but my VBA code doesn't work and asks for debug after confirmation.

My code:

Sub EMail() 
ActiveWorkbook.SendMail Recipients:="user@gmail.com" 
End Sub

Answer 1

Here is an example on how to send Active Workbook as attachment

Option Explicit
Sub EmailFile()
    Dim olApp As Object
    Dim olMail As Object
    Dim olSubject As String

'   // Turn off screen updating
    Application.ScreenUpdating = False

    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(olMailItem)

    olSubject = "This Subject Line"

    With olMail
        .Display
    End With

    With olMail
        .To = "0m3r@EMail.com"
        .CC = ""
        .BCC = ""
        .Subject = olSubject
        .HTMLBody = "This Body Text " & .HTMLBody
        .Attachments.Add ActiveWorkbook.FullName
        '.Attachments.Add ("C:\test.txt") ' add other file
'        .Send   'or use .Display
        .Display
    End With

'   // Restore screen updating
    Application.ScreenUpdating = True

    Set olMail = Nothing
    Set olApp = Nothing

End Sub