Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Unanswered: Emailing multiple recipients

    I'm attepting to extract email addresses from a qry as a result of a dual variable filter: type of conference(string) AND Times Attended(integer)


    I have the code. but keep revieving a "type mismatch error" I'm assuming that times attended needs to be defined as an integer(in my query this field is defined as "count"), but I do not know the syntax.


    Any suggestions?????

  2. #2
    Join Date
    Mar 2004
    Posts
    52

    By the way

    Here's the code that I have so far:


    Private Sub Command14_Click()
    On Error GoTo Err_Command14_Click
    Dim SendString As String
    Dim strSQL As String
    Dim rstAttendance As New ADODB.Recordset



    strSQL = "SELECT * FROM " & "qryAttendance " _
    & "WHERE type_of_conference = " & Forms!frmAttendanceSearchView.[Type of Conference] And "WHERE times attended = " & Forms!frmAttendanceSearchView.[Times Attended]

    'Open the Recordset to Customers
    rstAttendance.ActiveConnection = Application.CurrentProject.Connection
    rstAttendance.CursorType = adOpenKeyset
    rstAttendance.Open strSQL


    SendString = ""

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

    With rstAttendance


    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

    rstAttendance.Close

    Exit_Command14_Click:
    Exit Sub

    Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click

    End Sub

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: By the way

    Originally posted by ax2ron
    Here's the code that I have so far:


    Private Sub Command14_Click()
    On Error GoTo Err_Command14_Click
    Dim SendString As String
    Dim strSQL As String
    Dim rstAttendance As New ADODB.Recordset



    strSQL = "SELECT * FROM " & "qryAttendance " _
    & "WHERE type_of_conference = " & Forms!frmAttendanceSearchView.[Type of Conference] And "WHERE times attended = " & Forms!frmAttendanceSearchView.[Times Attended]

    'Open the Recordset to Customers
    rstAttendance.ActiveConnection = Application.CurrentProject.Connection
    rstAttendance.CursorType = adOpenKeyset
    rstAttendance.Open strSQL


    SendString = ""

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

    With rstAttendance


    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

    rstAttendance.Close

    Exit_Command14_Click:
    Exit Sub

    Err_Command14_Click:
    MsgBox Err.Description
    Resume Exit_Command14_Click

    End Sub
    Your WHERE clause is invalid. Try:

    "WHERE (type_of_conference=" & Forms!frmAttendanceSearchView.[Type of Conference] & " And [times attended]= " & Forms!frmAttendanceSearchView.[Times Attended] & ");"

    You will need to verify the types ... If there are strings then add single quotes. If there is a date add #'s ...

  4. #4
    Join Date
    Mar 2004
    Posts
    52

    email

    Thanks for that snippet of code, that takes care of the type mismatch. I'm new to VBA so when you say "verify the types" what type of syntax are you referring to?


    I entered in the following code and revieved this message: "no given value for one or more required parameters"

    Is that because times attended is a "integer"?

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: email

    Originally posted by ax2ron
    Thanks for that snippet of code, that takes care of the type mismatch. I'm new to VBA so when you say "verify the types" what type of syntax are you referring to?


    I entered in the following code and revieved this message: "no given value for one or more required parameters"

    Is that because times attended is a "integer"?
    You need to verify what types the columns you're querying against ... Are they strings, dates, integers, longs, memos, etc ... And then make your comparison assignments match to the type.

  6. #6
    Join Date
    Mar 2004
    Posts
    52

    Re: email

    Originally posted by M Owen
    You need to verify what types the columns you're querying against ... Are they strings, dates, integers, longs, memos, etc ... And then make your comparison assignments match to the type.
    Owen, thanks for the tip. I did as sugguested with the following code:

    strSQL = "SELECT * FROM " & "qryAttendance " _
    & "WHERE (type_of_conference='" & Forms!frmAttendanceSearchView.[Type of Conference] & "' and times_attended => " & Forms!frmAttendanceSearchView.[Times Attended] & ");"

    Type of attendance being charaters, i enclosed the state with single quotes. I did not encase times attended because it is an integer. Right?

    In my qryAttendance I set the critera to greater than (>=) need I translate this into my VBA code?


    ................................> I ran the code and recieved the following error:

    syntax error(missing operator)

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: email

    Originally posted by ax2ron
    Owen, thanks for the tip. I did as sugguested with the following code:

    strSQL = "SELECT * FROM " & "qryAttendance " _
    & "WHERE (type_of_conference='" & Forms!frmAttendanceSearchView.[Type of Conference] & "' and times_attended => " & Forms!frmAttendanceSearchView.[Times Attended] & ");"

    Type of attendance being charaters, i enclosed the state with single quotes. I did not encase times attended because it is an integer. Right?

    In my qryAttendance I set the critera to greater than (>=) need I translate this into my VBA code?


    ................................> I ran the code and recieved the following error:

    syntax error(missing operator)
    Here's your error: times_attended => What is this supposed to be?

    the greater than or equal operator is >= ....

  8. #8
    Join Date
    Mar 2004
    Posts
    52

    Re: email

    Originally posted by M Owen
    Here's your error: times_attended => What is this supposed to be?

    the greater than or equal operator is >= ....
    Thats what I thought,i originally set it to ">=".


    Now i'm recieving the previous error of "No value given for one or more required parameters"

Posting Permissions

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