Public Function zzPJM_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
Dim MyNewBodyText As String
Set fso = New FileSystemObject
' First, we need to know the subject.
' We can't very well be sending around blank messages...
Subjectline$ = "HP Support Incident customer Feedback"
' If there's no subject, call it a day.
If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If
' Now we need to put something in our letter...
'BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
' "We Need A Body!")
'Use a pre formatted text file.....
BodyFile$ = "C:\Documents and Settings\masonpet\My Documents\FeedbackTest.html"
' If there's nothing to say, call it a day.
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, "I Ain't Got No-Body!"
Exit Function
End If
' Since we got 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
' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application
' Set up the database and query connections
Set db = CurrentDb()
Set MailList = db.OpenRecordset("qry_zzPJM_MyEmailAddresses")
' 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.
Dim MyRecip As Outlook.Recipient
Do Until MailList.EOF
' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)
' This addresses it
'MyMail.To = MailList("email")
Set MyRecip = MyMail.Recipients.Add("xxxxx@xxxxx.com")
MyRecip.Type = olBCC
Set MyRecip = MyMail.Recipients.Add(MailList("email"))
'This gives it a subject
MyMail.Subject = Subjectline$
'This gives it the body
' This line will copy the "master" template into
' a variable we can mess around with
MyNewBodyText = MyBodyText
' Now we can replace tokens to our heart's content
' without worrying about corrupting the "master" template
'Customer first name for intro
MyNewBodyText = Replace(MyNewBodyText, "[[FirstName]]", MailList("FirstName"))
'Summary of incident detail
MyNewBodyText = Replace(MyNewBodyText, "[[Summary]]", MailList("Summary"))
'Incident number for feedback
MyNewBodyText = Replace(MyNewBodyText, "[[RNnumber]]", MailList("RNnumber"))
'Date the call was raised
MyNewBodyText = Replace(MyNewBodyText, "[[OpenDate]]", MailList("OpenDate"))
MyMail.HTMLBody = MyNewBodyText
'If you want to send an attachment
'uncomment the following line and add the location of the file
MyMail.Attachments.Add "C:\Users\masonpet\Documents\Customer Feedback for HP Support Incidents.xls", olByValue, 1, "Feedback"
'This sends it!
'MyMail.Send
'Uncomment the next line And comment the "MyMail.Send" line above this.
MyMail.Display
'And on to the next one...
MailList.MoveNext
