Mail a row or rows to each person in a range 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

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 your worksheet you must have :

In column A : Names of the students or ?
In column B:H : Information about the student or ?

We filter the range A1:H? for every unique name in the name column (column A in this example). For every unique name we create a new file with only the data of that person and send it to the mail address it find with the VLookup function in the worksheet "Mailinfo".

Important: You must create this worksheet manual and add the names and mail addresses one time. Add a worksheet to your workbook with the name "Mailinfo" with in column A the names and in column B the mail addresses of every possible person in your Name column.
Note: if you use Windows Live Mail the address must exist in your contacts.

How do I Change filter range and filter column? :
In this example I use the filter range A1:H? (we use all the rows on the sheet)
You can change the filter range and filter column in this two code lines in the macro.

Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)
FieldNum = 1 'Filter column = A because the filter range start in A

Copy the macro below in a Standard module of your workbook.

Sub Send_Row_Or_Rows_Attachment_1()
'For Tips see: https://jkp-ads.com/rdb/win/winmail/div/tips.htm
'Working in Excel 2000-2016
    Dim rng As Range
    Dim Ash As Worksheet
    Dim Cws As Worksheet
    Dim Rcount As Long
    Dim Rnum As Long
    Dim FilterRange As Range
    Dim FieldNum As Integer
    Dim mailAddress As String
    Dim NewWB As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim I As Long

    On Error GoTo cleanup

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

    'Set filter sheet, you can also use Sheets("MySheet")
    Set Ash = ActiveSheet

    'Set filter range and filter column (column with names)
    Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)
    FieldNum = 1    'Filter column = A because the filter range start in column A

    'Add a worksheet for the unique list and copy the unique list in A1
    Set Cws = Worksheets.Add
    FilterRange.Columns(FieldNum).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=Cws.Range("A1"), _
            CriteriaRange:="", Unique:=True

    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))

    'If there are unique values start the loop
    If Rcount >= 2 Then
        For Rnum = 2 To Rcount

            'Look for the mail address in the MailInfo worksheet
            mailAddress = ""
            On Error Resume Next
            mailAddress = Application.WorksheetFunction. _
                          VLookup(Cws.Cells(Rnum, 1).Value, _
                                  Worksheets("Mailinfo").Range("A1:B" & _
                                      Worksheets("Mailinfo").Rows.Count), 2, False)
            On Error GoTo 0

            If mailAddress <> "" Then

                'Filter the FilterRange on the FieldNum column
                FilterRange.AutoFilter Field:=FieldNum, _
                                       Criteria1:=Cws.Cells(Rnum, 1).Value

                'Copy the visible data in a new workbook
                With Ash.AutoFilter.Range
                    On Error Resume Next
                    Set rng = .SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                End With

                Set NewWB = Workbooks.Add(xlWBATWorksheet)

                rng.Copy
                With NewWB.Sheets(1)
                    .Cells(1).PasteSpecial Paste:=8
                    .Cells(1).PasteSpecial Paste:=xlPasteValues
                    .Cells(1).PasteSpecial Paste:=xlPasteFormats
                    .Cells(1).Select
                    Application.CutCopyMode = False
                End With

                'Create a file name
                TempFilePath = Environ$("temp") & "\"
                TempFileName = "Your data of " & Ash.Parent.Name _
                             & " " & Format(Now, "dd-mmm-yy h-mm-ss")

                If Val(Application.Version) < 12 Then
                    'You use Excel 97-2003
                    FileExtStr = ".xls": FileFormatNum = -4143
                Else
                    'You use Excel 2007-2016
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If

                'Save, Mail, Close and Delete the file
                With NewWB
                    .SaveAs TempFilePath & TempFileName _
                          & FileExtStr, FileFormat:=FileFormatNum
                    On Error Resume Next
                    For I = 1 To 3
                        .SendMail mailAddress, _
                                  "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
            End If

            'Close AutoFilter
            Ash.AutoFilterMode = False

        Next Rnum
    End If

cleanup:
    Application.DisplayAlerts = False
    Cws.Delete
    Application.DisplayAlerts = True

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

Example 2

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 your worksheet you must have :

In column A : Names of the students or ?
In column B : E-mail addresses
In column C:H : Information about the student or ?

Note: Every row must have a mail address in column B
Note: if you use Windows Live Mail the address must exist in your contacts.

We filter the range A1:H? for every unique mail address in column B.
For every unique mail address we create a new file with only the records with that
mail address and send it to that mail address.

How do I Change filter range and filter column? :
In this example I use the filter range A1:H? (we use all the rows on the sheet)
You can change the filter range and filter column in this two code lines in the macro.

Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)
FieldNum = 2 'Filter column = B because the filter range start in A

Copy the macro below in a Standard module of your workbook.

Sub Send_Row_Or_Rows_Attachment_2()
'For Tips see: https://jkp-ads.com/rdb/win/winmail/div/tips.htm
'Working in Excel 2000-2016
    Dim rng As Range
    Dim Ash As Worksheet
    Dim Cws As Worksheet
    Dim Rcount As Long
    Dim Rnum As Long
    Dim FilterRange As Range
    Dim FieldNum As Integer
    Dim NewWB As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim I As Long

    On Error GoTo cleanup

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

    'Set filter sheet, you can also use Sheets("MySheet")
    Set Ash = ActiveSheet

    'Set filter range and filter column (column with e-mail addresses)
    Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)
    FieldNum = 2    'Filter column = B because the filter range start in column A

    'Add a worksheet for the unique list and copy the unique list in A1
    Set Cws = Worksheets.Add
    FilterRange.Columns(FieldNum).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=Cws.Range("A1"), _
            CriteriaRange:="", Unique:=True

    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))

    'If there are unique values start the loop
    If Rcount >= 2 Then
        For Rnum = 2 To Rcount

            'If the unique value is a mail addres create a mail
            If Cws.Cells(Rnum, 1).Value Like "?*@?*.?*" Then

                'Filter the FilterRange on the FieldNum column
                FilterRange.AutoFilter Field:=FieldNum, _
                                       Criteria1:=Cws.Cells(Rnum, 1).Value

                'Copy the visible data in a new workbook
                With Ash.AutoFilter.Range
                    On Error Resume Next
                    Set rng = .SpecialCells(xlCellTypeVisible)
                    On Error GoTo 0
                End With

                Set NewWB = Workbooks.Add(xlWBATWorksheet)

                rng.Copy
                With NewWB.Sheets(1)
                    .Cells(1).PasteSpecial Paste:=8
                    .Cells(1).PasteSpecial Paste:=xlPasteValues
                    .Cells(1).PasteSpecial Paste:=xlPasteFormats
                    .Cells(1).Select
                    Application.CutCopyMode = False
                End With

                'Create a file name
                TempFilePath = Environ$("temp") & "\"
                TempFileName = "Your data of " & Ash.Parent.Name _
                             & " " & Format(Now, "dd-mmm-yy h-mm-ss")

                If Val(Application.Version) < 12 Then
                    'You use Excel 97-2003
                    FileExtStr = ".xls": FileFormatNum = -4143
                Else
                    'You use Excel 2007-2016
                    FileExtStr = ".xlsx": FileFormatNum = 51
                End If

                'Save, Mail, Close and Delete the file
                With NewWB
                    .SaveAs TempFilePath & TempFileName _
                          & FileExtStr, FileFormat:=FileFormatNum
                    On Error Resume Next
                    For I = 1 To 3
                        .SendMail Cws.Cells(Rnum, 1).Value, _
                                                          "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
            End If

            'Close AutoFilter
            Ash.AutoFilterMode = False

        Next Rnum
    End If

cleanup:
    Application.DisplayAlerts = False
    Cws.Delete
    Application.DisplayAlerts = True

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

Ron de BruinExcel Automation