email counting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that the users want to use to send out a list of emails. What
they are asking for is a textbox that will provide them with the total number
of emails that are marked for sending and as well as a a counting process
that tells them how many of them are sent until all are completed. I have
done the record counting with a textbox on a form in the form's OnCurrent
event, but this is puzzling to me. The emails to be sent all come from the
same table and a check box that is checked. Once the email is sent, the
checkbox is unchecked. I am seeking assistance on the counter part of this
posting.
If anyone has any info or a site that has it, thanks in advance.
*** john
 
John,
I have done something just like that before, so I'll try and explain the
concept of what I did it. This will either email or print, depending if the
person has an email address or not. (not would then be printed and mailed).
It worked out pretty slick.

On the first form (various print selections (Monthly / YTD) and other
options), then "GO" ...... This starts a process of data compiling and will
also create a table (tblEmail) with the names and email addresses (and other
specific data) of the people to be printed/emailed ..... then an Email form
is displayed. This has the email subject line and body for the user to edit
(it comes prefilled). In the lower section it has 5 boxes 1) Printing ... 2)
# of Emails to Print 3) # to Printed 4) Printed Email 5) Printed. This
is to show who is being printed/emailed, how many will be emailed/printed and
how many have been emailed/printed. It updates the count after each.

Here's my code -- it works for me, but I'm sure there are other ways to get
the job done. If you need me to explain in additional detail or a screen
shot, please let me know

Hope this concept fits your need.
SteveD

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click

Dim MyDB As Database
Dim Myrst As Recordset
Dim MySubject As String
Dim MyMessage As String

Me.PrintCountEmail = 0
Me.PrintCountPrinter = 0
MyMessage = Forms!frmPrintEmail!EMailMessage

Set MyDB = CurrentDb()
Set Myrst = MyDB.OpenRecordset("tblEMail", dbOpenDynaset)

Myrst.MoveFirst
While Not Myrst.EOF
Me!LastName = Myrst!Last & ", " & Myrst!First
Me!UniqueID = Myrst!Last & Myrst!MemberID
MySubject = Forms!frmPrintEmail!EmailSubject & " - " & Me!LastName

If Myrst!Print = "EMAIL" Then 'EMail the report

If Forms!mainMenu!ChkMonthlyStmt = True Then
Me.PrintCountEmail = Me.PrintCountEmail + 1
DoCmd.SendObject acReport, "Monthly Statement_Email",
"RichTextFormat(*.rtf)", Myrst![E-Mail], "", "", MySubject, MyMessage, False
Else
Me.PrintCountEmail = Me.PrintCountEmail + 1
DoCmd.SendObject acReport, "Monthly Statement_EmailYTD",
"RichTextFormat(*.rtf)", Myrst![E-Mail], "", "", MySubject, MyMessage, False
End If

Else 'Print the Report

'Need IF Stmt to determine which report (monthly or YTD)
If Forms!mainMenu!ChkMonthlyStmt = True Then
Me.PrintCountPrinter = Me.PrintCountPrinter + 1
DoCmd.OpenReport "Monthly Statement_EMail", acViewNormal
Else
Me.PrintCountPrinter = Me.PrintCountPrinter + 1
DoCmd.OpenReport "Monthly Statement_EMailYTD", acViewNormal
End If

'DoCmd.OpenReport "Monthly Statement_EMailYTD", acViewNormal
'DoCmd.OpenReport "Monthly Statement_EMail", acViewNormal
End If
Myrst.MoveNext
Wend

Myrst.Close
MyDB.Close


DoCmd.Close acForm, "frmPrintEMail"

Exit_Command5_Click:
Exit Sub

Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click

End Sub
 
Back
Top