Tips for the SendMail code examples

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

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, _
        Thisworkbook.Sheets("mysheet").Range("B1").Value

Send the mail to more people.

    .SendMail Array("ron@debruin.nl", "jelle@debruin.nl"), _
        "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
With
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

Ron de BruinExcel Automation