Tips for the Outlook object model 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 To, CC and BCC line

You can use this to send to more then one person.

    .To = "ron@debruin.nl;jelle@debruin.nl"

Or you can use a mail address in a cell like this, you can do the same for the CC, BCC or the subject.

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

Or Send to Outlook Distribution Group. Instead of .To "ron@debruin.nl" use

   .To = "GroupName"

Send to all E-mail addresses in a range and check if the mail address is correct.
Add the code below to the macro and change the To line to this: .To = strto

     Dim cell As Range
    Dim strto As String
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
        If cell.Value Like "?*@?*.?*" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

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 this line
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 file name.
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 macro also.

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

Change the Body line

If you want to add more text to the body then instead of .Body = "Hi there" use this.

    Dim strbody As String
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"

Or use this if you want to use cell values from a range

     Dim cell As Range
    Dim strbody As String
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("C1:C60")
        strbody = strbody & cell.Value & vbNewLine
    Next

Or this

    Dim strbody As String
    With ThisWorkbook.Sheets("Sheet1")
        strbody = "Hi there" & vbNewLine & vbNewLine & _
                  .Range("A1") & vbNewLine & _
                  .Range("A2") & vbNewLine & _
                  .Range("A3") & vbNewLine & _
                  .Range("A4")
    End With

Change the Body line to .Body = strbody to use the string in the body.

Use HTMLBody instead of Body

Formatted text (HTML)

If you want to create emails that are formatted you can use HTMLBody (Office 2000 and up) instead of Body.You can find a lot of WebPages on the internet with HTML tags examples.

 strbody = "<H3><B>Dear Customer</B></H3>" & _
              "Please visit this website to download the new version.<br>" & _
              "Let me know if you have problems.<br>" & _
              "<A HREF=""/rdb/"">Ron's Excel Page</A>" & _
              "<br><br><B>Thank you</B>"

Use .HTMLBody = strbody then instead of .Body = strbody

If you want to send a range or worksheet as HTML see this two examples pages.

Mail Range/Selection in the body of the mail

Mail one worksheet in the body of the mail

Other options

There are a lot of other options that you can add to the mail code. You can find a few of them below. To find out all the options change the code example that you use to Early binding. On the bottom of every Outlook example page you can see how. The Intellisense help show you the properties and methods that you can use when you type the code. You can also use the VBA help after you change the code to Early binding.

Change Account (only Excel/Outlook 2007 and up)

Visit : Use the mail account you want in your mail macro

Set Importance

    ' 0 = Low, 2 = High, 1 = Normal
    .Importance = 2

ReadReceiptRequest

    .ReadReceiptRequested = True

DeferredDeliveryTime

    'Stay in the outbox untill this date and time
   .DeferredDeliveryTime = "1/6/2007 10:40:00 AM"

    'Wait four hours
    .DeferredDeliveryTime = DateAdd("h", 4, Now)

Add Signature to mail

Visit : Insert Outlook Signature in mail

Check if Outlook is open and open Outlook with VBA

Test if Outlook is open and open Outlook with VBA

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