Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Tips for the SendMail  code examples


Change the Recipients line

Choose a address in the address book of your mail program. When you use "" (not fill in a mail address)you can choose a mail address in your address book.

    .SendMail "", "This is the Subject line"

Use a cell containing an email-address.

    .SendMail ThisWorkbook.Sheets("mysheet").Range("A1").Value, _
        "This is the Subject line"

Use also a cell for the subject.

    .SendMail ThisWorkbook.Sheets("mysheet").Range("A1").Value, _

Send the mail to more people.

    .SendMail Array("", ""), _
        "This is the Subject line"

Send to all E-mail addresses in a range.

    Dim MyArr As Variant
    MyArr = ThisWorkbook.Sheets("Sheet1").Range("A1:A20")
        .SendMail MyArr, "This is the Subject line"


Send to all E-mail addresses in a range and check if there is a correct mail address in each cell.
Add the code below to the macro and change the SendMail line to this

   .SendMail Arr, "This is the Subject line"

    Dim rng As Range
    Dim Arr() As String
    Dim N As Integer
    Dim cell As Range

    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A20")
    ReDim Preserve Arr(1 To rng.Cells.Count)
    N = 0
    For Each cell In rng
        If cell.Value Like "?*@?*.?*" Then
            N = N + 1
            Arr(N) = cell.Value
        End If
    Next cell
    ReDim Preserve Arr(1 To N)

If you only want to use mail addresses with the word "yes" in the column next to it you can replace

If cell.Value Like "?*@?*.?*" Then
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then


Change the Save line

You can change the TempFileName string in the code to change the save line.
It is also possible to use a cell reference.

TempFileName = "YourFileName"

TempFileName = "YourFileName " & Format(Now, "dd-mmm-yy h-mm-ss")

TempFileName = ThisWorkbook.Sheets("Sheet1").Range("C1").Value

Important: Use error checking to verify that a file with that name doesn't already exist or isn't already open. In the examples in this article the file name includes the date and time so that the chance the file name already exists is very small.


Copy the cells as values

If you want to paste as values the sheet must be unprotected!!!!!
Or Unprotect and Protect the sheet in the Sub also.

In most examples there is commented code that you can use to do this.


Copy sheet security dialog in Excel 2007-2013

Read how you can change the mail code examples that send one or more sheets to avoid problems when you use the code examples in your personal file or in a add-in in 2007-2013.

Read the info in this article