Examples

The following are three VBA code samples originally derived from Ron de Bruin’s automation scripts for exporting Excel data into Outlook emails. These examples have been re-engineered using VBA Mailer, enabling direct email dispatch via SMTP without relying on Outlook or any external client dependencies

Mail one worksheet in the body of the mail

The following subroutine sends the whole ActiveSheet in the body of the mail without pictures. Don't forget to copy the function RangetoHTML in the same module. You only have to change the mail address in the macro before you can run the macro.

Option Explicit
Private Enum OlItemType
    olMailItem = 0
End Enum

Sub Mail_Sheet_Outlook_Body()
'For Tips see: https://jkp-ads.com/rdb/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2016
    Dim rng As Range
'    Dim OutApp As Object
    Dim OutApp As VBAMailer.Application
'    Dim OutMail As Object
    Dim OutMail As VBAMailer.MailItem
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Set rng = Nothing
    Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    'Set rng = Sheets("YourSheet").UsedRange
    
'    Set OutApp = CreateObject("Outlook.Application")
    Set OutApp = New VBAMailer.Application
'    Set OutMail = OutApp.CreateItem(0)
    Set OutMail = OutApp.CreateItem(OlItemType.olMailItem)
    
    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
'        .HTMLBody = RangetoHTML(rng)
        .Body = RangetoHTML(rng)
        ' .Send   'or use .Display
        .Display
    End With
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close savechanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Mail a row to each person in a range

Important :

1. The code is not working if your data is a List(Excel 2003) or Table(Excel 2007-2016)
2. The first row in the range must have Headers
3. Turn off AutoFilter before you use the code
4. Be sure that the sheet with the data is the active worksheet

• In this example I use the range A1:J100
• In column A : Names of the students
• In column B : E-mail addresses
• In column C : yes or no ( if the value is yes it will create a mail)
• In column D:J : Grades or other info for the student

Option Explicit
Private Enum OlItemType
    olMailItem = 0
End Enum

Sub Send_Row()
'For Tips see: https://jkp-ads.com/rdb/win/winmail/Outlook/tips.htm
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2016
'    Dim OutApp As Object
    Dim OutApp As VBAMailer.Application
'    Dim OutMail As Object
    Dim OutMail As VBAMailer.MailItem
    Dim cell As Range
    Dim rng As Range
    Dim Ash As Worksheet

    Set Ash = ActiveSheet
    On Error GoTo cleanup
'    Set OutApp = CreateObject("Outlook.Application")
    Set OutApp = New VBAMailer.Application

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

    For Each cell In Ash.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" _
           And LCase(cell.Offset(0, 1).Value) = "yes" Then

            'Change the filter range and filter Field if needed
            'It will filter on Column B now (mail addresses)
            Ash.Range("A1:J100").AutoFilter Field:=2, Criteria1:=cell.Value

            With Ash.AutoFilter.Range
                On Error Resume Next
                Set rng = .SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
            End With

'            Set OutMail = OutApp.CreateItem(0)
            Set OutMail = OutApp.CreateItem(OlItemType.olMailItem)

            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "Grades Aug"
'                .HTMLBody = RangetoHTML(rng)
                .Body = RangetoHTML(rng)
                .Display  'Or use .Send
            End With
            On Error GoTo 0

            Set OutMail = Nothing
            Ash.AutoFilterMode = False
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
 
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
 
    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
 
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
 
    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
 
    'Close TempWB
    TempWB.Close savechanges:=False
 
    'Delete the htm file we used in this function
    Kill TempFile
 
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Send mail with link to workbook

If you want to let your Colleagues/Co workers now that you have created a workbook that you want to share with them you can use the following subroutine to create a mail with a link to the ActiveWorkbook.

Important :

1. The ActiveWorkbook must be saved on a drive that everyone is connected to
2. The reciever of the mail must also use Outlook

Option Explicit
Private Enum OlItemType
    olMailItem = 0
End Enum

Sub Make_Outlook_Mail_With_File_Link()
'For Tips see: https://jkp-ads.com/rdb/win/winmail/Outlook/tips.htm
'Working in Excel 2000-2016
'    Dim OutApp As Object
    Dim OutApp As VBAMailer.Application
'    Dim OutMail As Object
    Dim OutMail As VBAMailer.MailItem
    Dim strbody As String

    If ActiveWorkbook.Path <> "" Then
'        Set OutApp = CreateObject("Outlook.Application")
        Set OutApp = New VBAMailer.Application
'        Set OutMail = OutApp.CreateItem(0)
        Set OutMail = OutApp.CreateItem(OlItemType.olMailItem)

        strbody = "" & _
                  "Colleagues,

" & _
                  "I want to inform you that the next sales Order :
" & _
                  ActiveWorkbook.Name & " is created.
" & _
                  "Click on this link to open the file : " & _
                  "Link to the file" & _
                  "

Regards," & _
                  "

Account Management"

        On Error Resume Next
        With OutMail
            .To = "[email protected]"
            .CC = ""
            .BCC = ""
'            .Subject = ActiveWorkbook.Name

'            .HTMLBody = strbody
            .Body = strbody
            .Display   'or use .Send
        End With
        On Error GoTo 0

        Set OutMail = Nothing
        Set OutApp = Nothing
    Else
        MsgBox "The ActiveWorkbook does not have a path, Save the file first."
    End If
End Sub