Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Email list (2000/XP)

    I have a form that asks the user if they want to be contacted by email with further information. I have a field for an email address and a tick box to confirm they wish to be added to the mailing list.

    What I would like to do is have a command button on my Report menu that when you click it generates a list of these users and then places all the email addresses into the BCC field of an email. Can someone help me with the code that loops through a query and finds those email addresses please?
    Jerry
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email list (2000/XP)

    You could create a function like this, with the appropriate names substituted:

    Function GetBCC()
    Dim strBCC As String
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rst.Open "tblNames", cnn, adOpenForwardOnly
    Do While Not rst.EOF
    If rst!SendMail = True Then
    strBCC = strBCC & "," & rst!EMail
    End If
    rst.MoveNext
    Loop

    ' Get rid of first comma
    If Not (strBCC = "") Then
    strBCC = Mid(strBCC, 2)
    End If

    GetBCC = strBCC

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

    You can use GetBCC as BCC argument in DoCmd.SendObject. Post back if you need more assistance.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Email list (2000/XP)

    Hi Hans

    Thanks for getting back so quickly. Only one problem, I have an error message which says:

    Invalid SQL Statement;expected 'DELETE','INSERT','PROCEDURE','SELECT',or'UPDATE'

    Jerry
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Email list (2000/XP)

    Temporily disable the error handling part by inserting an apostrophe ' before On Error GoTo ErrHandler.
    If you run the code, you'll get an error message and the option to Stop, Debug, or get Help. Select Debug. What is highlighted?

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Email list (2000/XP)

    Hi Hans

    Sorry, I have realised the error existed between the keyboard and chair!
    I had inadvertently mispelled the table name in the code.

    Thanks, it works a treat

    Jerry
    Jerry

Posting Permissions

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