Mail the whole workbook With SendMail

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

The code on this page is working with all Microsoft mail programs.

Note: With SendMail it is not possible to
1) Send text in the Body of the mail
2) Use the CC or BCC field
3) Attach other files

If you want to have the options above and more and use Microsoft Outlook as your mail program then use the Outlook object model examples from my site so you have much more control and options.

Copy the code in a Standard module of your workbook, if you just started with VBA see this page.
Where do I paste the code that I find on the internet

Check out this Tip page for changing the code on this page.
Tips for changing the code examples

Example 1

The following subroutine sends the active workbook in an e-mail message.

Change the mail address and subject in the macro before you run it.
Note: if you use Windows Live Mail the address must exist in your contacts.

If you change "ron@debruin.nl" to "" it will display the mail so you can add text in the body for example.

Sub Mail_workbook_1()
'For Tips see: https://jkp-ads.com/rdb/win/winmail/div/tips.htm
'Working in Excel 2000-2016
    Dim wb As Workbook
    Dim I As Long

    Set wb = ActiveWorkbook

    On Error Resume Next
    For I = 1 To 3
        wb.SendMail "ron@debruin.nl", _
                    "This is the Subject line"
        If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
End Sub

To send a workbook other than the active workbook, change the assignment to the wb variable.
For example Set wb = ThisWorkbook
Note: It doesn't have to be the active workbook used at that time.

Example 2

This sub will send a newly created workbook (copy of the ActiveWorkbook).
It is saving the workbook before mailing it with a date/time stamp.
After the file is sent the workbook will be deleted from your hard disk.

Change the mail address and subject in the macro before you run it.
Note: if you use Windows Live Mail the address must exist in your contacts.

Sub Mail_Workbook_2()
'For Tips see: https://jkp-ads.com/rdb/win/winmail/div/tips.htm
'Working in Excel 2000-2016
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim I As Long

    Set wb1 = ActiveWorkbook

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

    '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 = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, _
                                   Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

    With wb2
        On Error Resume Next
        For I = 1 To 3
            .SendMail "ron@debruin.nl", _
                      "This is the Subject line"
            If Err.Number = 0 Then Exit For
        Next I
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Ron de BruinExcel Automation