Generating email with template

[MTSPEER]

Hello,



I need to generate emails from a table I created(tblCustomers) to be send to the customers. I generated a template in Microsoft Outlook with variable fields in it for example, "Hello, my address is <address>". I want everyone in my table to have their separate email to be sent out with a custom letter with the variable fields generated. Is it best to use a template in word? I'm not even sure if I can, or can I automate these emails by just creating them in the body of the email?
I hope you guys can give me some advice. Thanks

[pbaldy]

I was hoping somebody would reply with experience with templates. I haven't used one. I'd use Outlook automation to create the body on the fly. You can even use SendObject if you don't need HTML, and build the body argument in code.

[MTSPEER]

How would I build the body argument in code using variables? With a record set? Can you give me some example code please?

[orange]

I found this code many years ago. There may be some parts relevant to your current request.
Note: I haven't used Outlook for many years.

'---------------------------------------------------------------------------------------
' Procedure : SendEMail
' Author    : Jack (based on various posts found)
' Created   : 11/19/2009
' Purpose   : To show sending email from Access where there is a pdf attachment
' and the email body is brought in from a file, and there is a list of email
' recipients. Each to get the  common "form" email.
' ---- came from researching posts on sending emails ------
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: uses filescripting object
'------------------------------------------------------------------------------
'
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 MyBodyText As String

10    Set fso = New FileSystemObject

      ' First, we need to know the subject.

      ' We can’t very well be sending around blank messages...

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

      ' If there’s no subject, call it a day.

30    If Subjectline$ = "" Then
40    MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
      "Quitting...", vbCritical, "E-Mail Merger"
50    Exit Function
60    End If

      ' Now we need to put something in our letter...

70    BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
      "We Need A Body!")

      ' If there’s nothing to say, call it a day.

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

      ' Check to make sure the file exists...
120   If fso.FileExists(BodyFile$) = False Then
130   MsgBox "The body file isn’t where you say it is. " & vbNewLine & vbNewLine & _
      "Quitting...", vbCritical, "I Ain’t Got No-Body!"
140   Exit Function
150   End If

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

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

      ' and close the file.
180   MyBody.Close

      ' Now, we open Outlook for our own device..
190   Set MyOutlook = New Outlook.Application

      ' Set up the database and query connections

200   Set db = CurrentDb()

210   Set MailList = db.OpenRecordset("Select email from Doctor where email is not Null")
      'NOTE:  "Doctor" is my test set of email addresses

      ' now, this is the meat and potatoes.
      ' this is where we loop through our list of addresses,
      ' adding them to e-mails and sending them.

220   Do Until MailList.EOF

      ' This creates the e-mail

230   Set MyMail = MyOutlook.CreateItem(olMailItem)

      ' This addresses it

240   MyMail.To = MailList("email")
      'NOTE: "email" is the field name in table Doctor

      'This gives it a subject
250   MyMail.Subject = Subjectline$

      'This gives it the body
260   MyMail.Body = MyBodyText

      'If you want to send an attachment
      'uncomment the following line

      'MyMail.Attachments.Add "c:\myfile.txt", olByValue, 1, "My Displayname"

      ' sample file to attach -----------
270  ' MyMail.Attachments.Add "C:\program files\safari\safari.resources\ToolbarDownloadsTemplate.pdf", olByValue, 1, "My Displayname"
MyMail.Attachments.Add "C:\users\mellon\documents\toad data modeler\reports\pdf\report.pdf", olByValue, 1, "My Displayname"
      ' To briefly describe:
      ' "c:\myfile.txt" = the file you want to attach
      '
      ' olByValue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
      ' the shortcut only works if the file is available locally (via mapped or local drive)
      '
      ' 1 = the position in the outlook message where the attachment goes. This is ignored by most
      ' other mailers, so you might want to ignore it too. Using 1 puts the attachment
      ' first in line.
      '
      ' "My Displayname" = If you don’t want the attachment’s icon string to be "c:myfile.txt" you
      ' can use this property to change it to something useful, i.e. "4th Qtr Report"

      'This sends it!

      'MyMail.send

      'Some people have asked how to see the e-mail
      'instead of automaticially sending it.
      'Uncomment the next line
      'And comment the "MyMail.Send" line above this.

280   MyMail.Display

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

300   Loop

      'Cleanup after ourselves

310   Set MyMail = Nothing

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

      'MyOutlook.Quit
320   Set MyOutlook = Nothing

330   MailList.Close
340   Set MailList = Nothing
350   db.Close
360   Set db = Nothing

End Function

If MyBodytext contained markers (your <text> to be substituted) you could have function or process to do same before finalizing the email(untested, but seems reasonable). You could set up tests to confirm/adjust as necessary.

Update: 11:10 am,
I just updated some coded values and the routine works to display the email (line 280).
If you provide some sample substitution codes, I'll look at the process.

[pbaldy]

Using a recordset you can set the value of a variable:

strBody = "Dear " & rs!FirstName & ", " & vbCrLf & vbCrLf & "Some more text here " & rs!OtherDataFromRecordset & " More text here"

vbCrLf inserts a carriage return/line feed. If you used HTML you'd use <BR> instead.

[MTSPEER]

THank you pbaldy I got this working to generate the emails from the data in my table. But I have multiple records in my table that need send out. I need to create a loop but I am struggling putting it together.

[pbaldy]

What have you got so far? This has some extraneous stuff, but should get you started:

http://www.granite.ab.ca/access/email/recordsetloop.htm

[MTSPEER]

Thanks guys I got it figured out!