Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81

    Unanswered: Loop collecting emails from all contacts displayed thru filtered form

    I need a button in my CONTACT form that sends an email to all contacts displayed by the form - In filtered mode or none filtered mode. Itīs a marketing database containing a zillion of sortables. Therefore I need the user to make the filtering themselves in a form. For instance it has checkboxes of 40+ products when they want to email customers with a certain product interest. So writing a query for every option is out of the question. Especially since they might want to select all contacts, working at companies w/ turnover over $ 1 million, interested in FORD, having birthday in June and what not...

    I can get it to send to the currently displayed record, no problem. Simple SendObject (EmailField is called [PersonalEmail]) like this:

    DoCmd.SendObject , "", "", [PersonalEmail], "", "", "", "", True, ""

    I therefore think I just need a loop that goes thru record 1 to end of the form, collecting the email-fields of every record in a string.

    Something like this:
    SendString = SendString & !PersonalEmail & ";"

    Problem is - I do not know the loop syntax to move in a form, 1 to END of Form.


    Today I can get it to work in a recordset like this:

    Do Until .EOF

    SendString = SendString & !PersonalEmail & ";"
    .MoveNext
    Loop

    But I need the loop to go thru a form. So I have tried:

    Do Until .EndOfForm

    Didnīt work of course, a mere guess. But that is what I need, I figure!? How to do until end of form and also the syntax for equivalent to ".MoveNext record in Form"

    If I use a loop in a recordset based on a table or query, something I do know how, it will email ALL my contacts, ignoring the filtering made manually in the Form.

    But can I get my recordset to identify the filtering made in the form I opened the function from?

    Or maybe I could use the recordset method just adding some Where statement to the recordset. Something like this:

    rstTblCONTACTS.Open "SELECT * FROM TblCONTACTS WHERE [contactID] = !Forms![CONTACTS]![contactID]"

    But I donīt get that to work.

    The easiest would of course be to use the Query access makes when a user filters a form. Simply set that query as recordset. But how do I find it? What is this Query called?

    Or maybe as suggested below cloning the records the form is showing into a new recordset then using that. But I donīt know how to do that either... Probably also need the same Formloop that I donīt know the syntax for.

    I know the logics but am a complete rookie at the VB syntax. Very frustrating. Knowing what to say but not how.

    -----------------------------------------
    Here is the Function I call to mail ALL contacts(But wish to alter so it just sends to contacts in a Form):

    Option Compare Database
    Public SendString As String

    Function mailall()


    Dim rstTblCONTACTS As New ADODB.Recordset


    'Open the Recordset to Customers
    rstTblCONTACTS.ActiveConnection = Application.CurrentProject.Connection
    rstTblCONTACTS.CursorType = adOpenKeyset
    rstTblCONTACTS.Open "TblCONTACTS"


    SendString = ""

    If MsgBox("Send Email" & Chr(13) & _
    "to all Contacts using Microsoft Outlook?", 4) = 6 Then

    With rstTblCONTACTS


    Do Until .EOF
    ' Create the Invoice report Filter used by the Report_Open
    ' event.

    SendString = SendString & !PersonalEmail & ";"
    .MoveNext
    Loop

    SendString = Left$(SendString, Len(SendString) - 2)


    DoCmd.SendObject , "", "", [SendString], "", "", "", "", True, ""

    End With
    End If

    rstTblCONTACTS.Close

    End Function
    Last edited by Johnny Dove; 04-13-02 at 03:03.

  2. #2
    Join Date
    Mar 2002
    Posts
    192
    Hi Johnny

    After the user finishes filtering try "cloning" the recordset then email based on that list.

  3. #3
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Originally posted by access_dude
    Hi Johnny

    After the user finishes filtering try "cloning" the recordset then email based on that list.
    So I take the records displayed by a filtered form and "clone" them into a new recordset!? What do I export them to? A query? And how can I do that? Do I need a loop for this to?

    Thanks for your help!

    Best Regards

    Johnny Da Dove

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •