Problem with Tokens in 2007

[Dom606]

Hi everyone,
I am stuck. I am crating a module that allows email to be sent to folks you subscribe to a newsletter. I crated a .txt file that contains the text that will display in each email where the first name (first) and the paid up date (switch) need to change for each created email. The .txt file is in green below.

The problem I am having is when I try to loop through each record in the dataset, the tokens [[first]] and [[switch]] are not updating in the email. As I step through the code, the problem seems to be in the area in red below.

Ideas?

The contents of the body.txt file is simply two lines:

Thanks for agreeing to receive the Falcon News via e-mail.

[[First]]:

Your dues are paid up through [[SWITCH]].


The code is as follows:

Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyNewBodyText As String
Dim MyBodyText As String

Set fso = New FileSystemObject

   ' Enter a subject

Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
"We Need A Subject Line!")


   ' If there's no subject, stop.

If Subjectline$ = "" Then
    MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
        "Quitting...", vbCritical, "E-Mail Merger"
    Exit Function
End If
    
   ' Identify where the body .txt file is located.
    
BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
"We Need A Body!")
 
   'C:\Documents and Settings\Dominic Fino\My Documents\FALCON\Newsletter Stuff\body.txt

   ' If there's no .txt file, stop.

If BodyFile$ = "" Then
    MsgBox "No body, no message." & vbNewLine & vbNewLine & _
         "Quitting...", vbCritical, "I Ain't Got No-Body!"
    Exit Function
End If

  ' Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
    MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
           "Quitting...", vbCritical, "No body .txt found!"
    Exit Function
End If

   ' Since we have a file, we can open it up.
    Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)

   ' and read it into a variable.
    MyBodyText = MyBody.ReadAll

   ' and close the file.
    MyBody.Close

   ' Open Outlook 
    Set MyOutlook = New Outlook.Application

   ' Set up the database and query connections

    Set db = CurrentDb()

    Set MailList = db.OpenRecordset("qryMaster_Label_EMAIL_PD")

  ' Loop through the list of addresses,
  ' adding them to e-mails and sending them.

    Do Until MailList.EOF

        ' Create the e-mail
        
        Set MyMail = MyOutlook.CreateItem(olMailItem)
            
            ' This addresses it the e-mail
            MyMail.To = MailList("email")
            
            'This gives the e-mail a subject
            MyMail.Subject = Subjectline$
[COLOR=Red]
'*****************************************************
            'This provides the e-mail body
            'MyMail.Body = MyNewBodyText
            MyMail.Body = MyBodyText
            
            ' This line will copy the "master" template (body.txt) into a variable
            MyNewBodyText = MyBodyText
'*****************************************************[/color]
            ' Replace tokens without corrupting the "master" template
            MyNewBodyText = Replace(MyNewBodyText, "[[First]]", MailList("First"))
            MyNewBodyText = Replace(MyNewBodyText, "[[SWITCH]]", MailList("SWITCH"))

            'To send an attachment
            'uncomment the following line

            MyMail.Attachments.Add "C:\Documents and Settings\Dominic Fino\My Documents\FALCON\Newsletter Stuff\Newsletter.pdf", olByValue, 1, "Newsletter"
            MyMail.Attachments.Add "C:\Documents and Settings\Dominic Fino\My Documents\FALCON\Newsletter Stuff\Directory.pdf", olByValue, 1, "Directory"
       
            'This sends it!
            'MyMail.Send

            'To see the e-mail
            'instead of automaticially sending it.
            'Uncomment the next line
            'And comment the "MyMail.Send" line above this.

            MyMail.Display


        
    'And on to the next one...
    MailList.MoveNext

Loop

 'Cleanup

Set MyMail = Nothing


'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

 

[PWise]

MyNewBodyText = Replace(MyBodyText, "[[First]]", MailList("First"))
MyNewBodyText = Replace(MyNewBodyText, "[[SWITCH]]", MailList("SWITCH"))
MyMail.Body = MyNewBodyText

 

[Dom606]

pwise
Thank you for your response. That worked like a charm. Man, it must be easy when you know what you are doing.

Thanks again.
Dom