# Ron de BruinExcel Automation

## Tips for the SendMail  code examples

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

.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.