I am using both Outlook 2002 and Access 2002. I'm pretty sure my 3rd
argument is a valid string, but I don't have any formal training so I
could be wrong. I tried to follow your instructions to the letter. I
actually don't mind displaying the email first, and it has the
advantage of not getting the annoying warning message. I decided to go
to .send because with that, I could set up an error trap allowing me
to determine whether or not the user has actually sent the email.
Right now I have it set as .display followed by .send. This works fine
as long as the user doesn't cancel from the warning box, but then
change his mind and send it manually. In a professional environment
that would be a major problem I'm sure, but I have set up this database
on my own (with a lot of help from somebody on the TechSupportGuy
website) for a community theater group (I handle the box office.) We
are non-profit (and not paid) and I am the only user. I'm just trying
to set if up so that I can pass it on to someone else someday who
doesn't know all the ins and outs of the program. Trying to make it as
idiot-proof as possible. That, and when something doesn't work that
I'm sure should work somehow, I get very curious and keep pounding away
at it.
If I can't get this to work, my next idea is to see if there's away to
determine if the email exists in my outbox, but that strikes me as very
complicated.
Anyway, my database is tremendously useful to our group, and it
improved by leaps and bounds once I got hold of your book--both
programatically and visually. I also appreciate how easy you made it
for people like me, with little training (and none of mine has been
formal) to use it. And thank you, also, for taking the time to
respond.
This is the code I use. After the "If Not Isnull(SendOutlookMsg . . .)
line, I have added the following:
If Me.RsvReminderEmailSent = "NotSent" and so on.
In your SendOutlookMsg module I set an error trap so that if I cancel
the email, I set the unbound field RsvReminderEmailSent to "NotSent".
However, when I got rid of the error trap, I still had the blank email.
Private Sub cmdAnnounceEmail_Click()
Dim db As DAO.Database, rstAnnounce As DAO.Recordset, rstPerfSched As _
DAO.Recordset
Dim rstTemplate As DAO.Recordset, rstData As DAO.Recordset
Dim strTo As String, strTitle As String, strHTML As String
Dim strWork As String, strBody As String, strPerfSched As String
Dim strFootTemp As String, strClosing As String
Dim intProductionIDNo As Integer, strProductionIDNo As String
Dim intAnswer As Integer
intAnswer = MsgBox("Do you want to send the reservation reminder email
now?", _
vbQuestion + vbYesNo, "")
Select Case intAnswer
Case vbYes
GoTo Continue:
Case vbNo
GoTo AnnounceEmail_Exit:
End Select
Continue:
Set db = DBEngine(0)(0)
Set rstAnnounce = db.OpenRecordset("qryPerfSchedforNextProdEmail")
' See if any records
If rstAnnounce.RecordCount = 0 Then
MsgBox "The performance schedule is missing."
GoTo AnnounceEmail_Exit:
ElseIf rstAnnounce!RsvReminderEmailSent = "True" Then
intAnswer = _
MsgBox("The reminder email for the upcoming production has
already been sent. Would you like to re-send it?", _
vbYesNo + vbQuestion, "")
Select Case intAnswer
Case vbYes
GoTo SendEmailReminder:
Case vbNo
GoTo AnnounceEmail_Exit:
End Select
' Close out
rstAnnounce.Close
Set rstAnnounce = Nothing
Set db = Nothing
End If
' Get the of season ticket holders
SendEmailReminder:
Set rstData = _
db.OpenRecordset("qryEmailsofSeasonTcktHoldersWithoutReservations")
' Make sure we have some
If rstData.RecordCount = 0 Then
MsgBox "All season ticket holders have reserved. No email
necessary."
' Close out
rstData.Close
Set rstData = Nothing
rstAnnounce.Close
Set rstAnnounce = Nothing
Set db = Nothing
GoTo AnnounceEmail_Exit:
End If
' Build the "To" list
Do Until rstData.EOF
' Add an email name
strTo = strTo & "<" & rstData!to & ">" & ";"
' Get the next record
rstData.MoveNext
Loop
' Close the recordset
rstData.Close
Set rstData = Nothing
'Build the Performance Schedule
Set rstPerfSched = db.OpenRecordset("qryPerfSchedforNextProdEmail")
intProductionIDNo = rstPerfSched!ProductionIDNo
Do Until rstPerfSched.EOF
strPerfSched = strPerfSched & rstPerfSched!DayName & ", " & _
Format(rstPerfSched!PerformanceDate, "mmm dd") & ", " & _
rstPerfSched!ShowTime & "<br>"
rstPerfSched.MoveNext
Loop
' Close the recordset
rstPerfSched.Close
Set rstPerfSched = Nothing
' Open the HTML template for email
Set rstTemplate = db.OpenRecordset("SELECT * FROM
ztblMessageTemplates " & _
"WHERE Template = 'ResvReminder' " & "ORDER By
TemplateSequence")
' The first record has the header - copy it
strWork = rstTemplate!TemplateMsg
' Insert the production name
strWork = Replace(strWork, "[ProductionName]",
rstAnnounce!ProductionName)
strHTML = strWork
' Load the rest of the template text
rstTemplate.MoveNext
strWork = rstTemplate!TemplateMsg
strWork = Replace(strWork, "[PerformanceDate]", strPerfSched)
strHTML = strHTML + strWork
' Load the rest of the template text
rstTemplate.MoveNext
strWork = rstTemplate!TemplateMsg
strHTML = strHTML + strWork
' Close the template recordset
rstTemplate.Close
Set rstTemplate = Nothing
' Got the pieces built, now assemble them
strHTML = strHTML & strClosing
' Send the email
If Not IsNull(SendOutlookMsg("Providence Players - Reservations
Reminder", strTo, strHTML, True)) Then
'if email sent, update table to indicate this
If Me.RsvReminderEmailSent = "NotSent" Then 'do not update
GoTo AnnounceEmail_Exit:
Else: DoCmd. _
RunSQL _
"UPDATE tblYearlyProductions SET [RsvReminderEmailSent]=True
WHERE tblYearlyProductions![ProductionIDNo]=" & intProductionIDNo & ";"
End If
End If
AnnounceEmail_Exit:
End Sub
John said:
Ann-
It works for me using Access 2003 and Outlook 2003. (But I get a pesky
security warning message.) Note that the function expects the third
argument to be a valid HTML string, but it also seems to work if I pass
simple text. Please post your call to SendOutlookMsg.
John Viescas, author
"Building Microsoft Access Applications"
"Microsoft Office Access 2003 Inside Out"
http://www.viescas.com/
(Paris, France)
:
I have found this module to be tremendously useful, and it works just
fine as long as I use the objMail.Display option. If I change to
objMail.Send, the email has no body. The To: and the Subject: are
there, just no email. Using objMail.Display and then objMail.Send
works, but not objMail.Send by itself. I would really like to use the
..send alone as it would allow me to implement some other programming
that checks to see that the email was sent. Any ideas what I have done
wrong?
