Email Boilerplate Text
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
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:
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:
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.
Rob van Gelder:
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,
14 August 2005, 10:15 pmRob
Rob van Gelder:
Well... the comment parser went silly. Hopefully you get the idea.
The syntax is a quick google away: mailto syntax
mailto syntax
Cheers,
14 August 2005, 10:19 pmRob
Ron de Bruin:
Hi all
I add this example on one of my mail pages
28 August 2005, 3:03 amhttp://www.rondebruin.nl/mail/folder3/smallmessage.htm
David Cross:
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
1 January 2006, 11:17 amDick Kusleika:
David: Try Ron's site http://www.rondebruin.nl/cdo.htm
2 January 2006, 9:19 pmJonathon:
Is there any way to get this to work with other email clients like Thunderbird?
18 February 2006, 5:49 pm