Email Boilerplate Text

Posted on by Dick Kusleika

Schoen, a fellow CPA, asks how to include text from a text file in the body of an email. First, let’s make some assumptions. Assume you have a list in Excel with email addresses, like this

an excel range showing names and email addresses

Further assume that you have a file called EmailBoiler.txt with some plain text in it and that you have a public constants named sPATH that points to the location of this file. Now look at this code:

Sub SendBoilerPlate()
   
    Dim olApp As Outlook.Application
    Dim olMail As Outlook.MailItem
    Dim rCell As Range, rRng As Range
   
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)
    Set rRng = Sheet1.Range(“A2”, Sheet1.Range(“A2”).End(xlDown))
   
    With olMail
        For Each rCell In rRng.Cells
            .Recipients.Add rCell.Offset(0, 2).Value
        Next rCell
       
        .Subject = “New tax laws”
       
        .Body = GetBoiler(sPATH & “EmailBoiler.txt”)
               
        .Display
        ‘.Send
    End With
   
End Sub
   
Function GetBoiler(ByVal sFile As String) As String
   
    Dim fso As Object
    Dim ts As Object
   
    Set fso = CreateObject(“Scripting.FileSystemObject”)
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
   
    GetBoiler = ts.readall
   
    ts.Close
   
End Function

You should get this:

an email message with the boiler plate text included

A couple of notes about the code: You’ll need to set a reference to the Outlook Object Library because we’re early binding. The recipients are added in a loop. The Recipients collection object contains email addresses, not Outlook contacts. In other words, the people to whom you’re sending the email don’t have to be in your contacts list.

I use Scripting to read the text file and I put it in a separate function to make the main sub more readable.

If you like to send HTML emails, you can store the HTML in a text file. In fact, the only change you’d make here is to use the HTMLBody property instead of the Body property.

Posted in Uncategorized

6 thoughts on “Email Boilerplate Text”

  1. Dick,

    It might be worthwhile noting the rarely used extras of the mailto type URL.
    You could create a hyperlink in a cell:
    (The following is supposed to be a hyperlink. Hopefully the comment parser doesn’t go silly)
    <a href=”mailto:tom@tom.com; dick@tom.com; harry@tom.com?subject=New tax laws?&body=This is some boilerplate text that I will insert into email.” rel=”nofollow”>Click here to send compose an e-mail</a>

    Not quite as flexible when it comes to multiple lines or HTML, but simple + no vba.

    Cheers,
    Rob

  4. How do I prevent Outlook from presenting me with a dialog box, every time it hits the .Send line, asking me if it is OK to have another application send email from Outlook?

    I can find a setting for this in outlook Express, but not in outlook

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.