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))
For Each rCell In rRng.Cells
.Recipients.Add rCell.Offset(0, 2).Value
.Subject = “New tax laws”
.Body = GetBoiler(sPATH & “EmailBoiler.txt”)
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
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.