Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / March 2007

Tip: Looking for answers? Try searching our database.

email counting

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
JohnE - 28 Mar 2007 00:18 GMT
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
SteveD - 29 Mar 2007 01:20 GMT
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


 

> 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
[quoted text clipped - 7 lines]
> If anyone has any info or a site that has it, thanks in advance.
> *** john
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.