Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Unanswered: sending mass emails from a query via outlook....arggg!!!!

    I'm attempting to send out mass emails from a filtered query. Type of conference and "Times_attended".

    This is what i've come up with so far. The problem is that i'm consistantly recieveing the missong operator error. This database was thrown on my lap and I was told that this code works. What gives?


    Private Sub Command14_Click()


    Dim MyDb As Database
    Dim MyRS As Recordset
    Dim strSQL As String
    Dim Address As String

    I BELIEVE THIS IS WHERE THE PROBLEM IS ARISING
    **************************************************


    strSQL = "SELECT * FROM " & "qryAttendance " _
    & "WHERE (type of conference='" & Forms!frmAttendanceSearchView![Type of Conference] & "' and times attended >= " & CLng(Forms!frmAttendanceSearchView![Times Attended]) & ");"

    ************************************************** ***

    Set MyDb = CurrentDb
    Set MyRS = MyDb.OpenRecordset(strSQL)
    MyRS.MoveFirst

    Set objOutlook = CreateObject("outlook.application")

    Do Until MyRS.EOF
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Address = MyRS![Email]

    With objOutlookMsg
    Set objOutlookRecip = .Recipients.add(TheAddress)
    objOutlookRecip.Type = olTo

    .Subject = "test"
    .Body = "test body"
    .Importance = olImportanceHigh

    For Each obbjOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With
    MyRS.MoveNext
    Loop
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    So, where's it erroring ?

    On this line

    Set MyRS = MyDb.OpenRecordset(strSQL)

    ?

    If so, why don't you debug.print the strSQL and look at it in the debug window? If you see a problem, great. If not, copy it into the query builder and run it as a query. If it doesn't run, start testing some of your criteria.

    BTW, your references to "type of conference" and "times attended" should each be surrounded in brackets. I personally NEVER use variables with imbeded spaces. And if one ever does, he/she better wrap the entire variable name in brackets.

    Is that CLng( . . . ) comparison against a date ? Suspicious.

  3. #3
    Join Date
    Mar 2004
    Posts
    52
    THanks!!!!

    Its erroring on that line.

    debug.print? What exactly is that? You have to excuse my lack of knowledge. This is the first time, I've worked with VBA........

    I'd agree with you on the spacing issue, just learned that spaces should not be set in variables, thanks for the tip.

    I encased the spaced variables with brackets, and removed the clng() function. I orignially inserted this to prevent a data mismatch error.

    Anyhow.....I ran the code, the good news it that the previous error is gone, but now its displaying a new error message: two few parameters: expected (2)

    I think that its not picking up the two variables in the code...what do you think?


    Originally posted by PracticalProgram
    So, where's it erroring ?

    On this line

    Set MyRS = MyDb.OpenRecordset(strSQL)

    ?

    If so, why don't you debug.print the strSQL and look at it in the debug window? If you see a problem, great. If not, copy it into the query builder and run it as a query. If it doesn't run, start testing some of your criteria.

    BTW, your references to "type of conference" and "times attended" should each be surrounded in brackets. I personally NEVER use variables with imbeded spaces. And if one ever does, he/she better wrap the entire variable name in brackets.

    Is that CLng( . . . ) comparison against a date ? Suspicious.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    post the current SQL

    and what datatype is [Type of Conference]

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Mar 2004
    Posts
    52
    Ok...here it is:

    type of conference is text

    times attended is an integer

    Private Sub Command14_Click()


    Dim MyDb As Database
    Dim MyRS As Recordset
    Dim strSQL As String
    Dim Address As String


    strSQL = "SELECT * FROM " & "qryAttendance " _
    & "WHERE (type of conference='" & Forms!frmAttendanceSearchView![Type of Conference] & "' and times attended >= " & Forms!frmAttendanceSearchView![Times Attended] & ");"

    Set MyDb = CurrentDb
    Set MyRS = MyDb.OpenRecordset(strSQL)
    MyRS.MoveFirst

    Set objOutlook = CreateObject("outlook.application")

    Do Until MyRS.EOF
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Address = MyRS![Email]

    With objOutlookMsg
    Set objOutlookRecip = .Recipients.add(TheAddress)
    objOutlookRecip.Type = olTo

    .Subject = "test"
    .Body = "test body"
    .Importance = olImportanceHigh

    For Each obbjOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With
    MyRS.MoveNext
    Loop
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub






    Originally posted by izyrider
    post the current SQL

    and what datatype is [Type of Conference]

    izy

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Right after

    strSQL = "SELECT * FROM " & "qryAttendance " _
    & "WHERE (type of conference='" & Forms!frmAttendanceSearchView![Type of Conference] & "' and times attended >= " & CLng(Forms!frmAttendanceSearchView![Times Attended]) & ");"

    insert a line

    debug.print strSQL

    this will print the contents of the strSQL variable to the debug window. What's the debug window? Hold down the control key and hit the letter 'G' and the debug window will come up.

    Instead of debug.print, you can pop up a messagebox with the same info

    msgbox strSQL

    With debug.print, you can copy the output to the clipboard. With a messagebox you can't.

    Now, does the value of the strSQL look exactly as you expected ?

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try this for me (humour me)
    Code:
    strSQL = "SELECT * FROM qryAttendance WHERE ("
    strSQL = strSQL & "([type of conference]='" & Forms!frmAttendanceSearchView![Type of Conference] & "') "
    strSQL = strSQL & "AND ([times attended] >= " & Forms!frmAttendanceSearchView![Times Attended] & "));"
    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2004
    Posts
    52
    Thanks a bunch, that was great!!!! The sql out put is what i'm looking for.

    SELECT * FROM qryAttendance WHERE (type of conference='XENON' and times attended >= 1);


    looks right to me right??? I'm under the impression that text need to be encased in single quottes, numbers are not right>

    what gives


    Originally posted by PracticalProgram
    Right after

    strSQL = "SELECT * FROM " & "qryAttendance " _
    & "WHERE (type of conference='" & Forms!frmAttendanceSearchView![Type of Conference] & "' and times attended >= " & CLng(Forms!frmAttendanceSearchView![Times Attended]) & ");"

    insert a line

    debug.print strSQL

    this will print the contents of the strSQL variable to the debug window. What's the debug window? Hold down the control key and hit the letter 'G' and the debug window will come up.

    Instead of debug.print, you can pop up a messagebox with the same info

    msgbox strSQL

    With debug.print, you can copy the output to the clipboard. With a messagebox you can't.

    Now, does the value of the strSQL look exactly as you expected ?

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    where are the [] ?

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Mar 2004
    Posts
    52
    izy,

    thanks....i inserted the code:

    I mean the output looks good(scratching my head)

    SELECT * FROM qryAttendance WHERE (([type of conference]='XENON') AND ([times attended] >= 1));


    Originally posted by izyrider
    try this for me (humour me)
    Code:
    strSQL = "SELECT * FROM qryAttendance WHERE ("
    strSQL = strSQL & "([type of conference]='" & Forms!frmAttendanceSearchView![Type of Conference] & "') "
    strSQL = strSQL & "AND ([times attended] >= " & Forms!frmAttendanceSearchView![Times Attended] & "));"
    izy

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    mine's got [], yours hasn't

    god only knows how A evaluates:
    Type Of (conference = "XENON")

    ...but that is what you are asking it to do.

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Mar 2004
    Posts
    52
    I inserted the [], to clarify. I'm using the filter: type of conference and times attended to pull up a filtered table, then take those email addresses to send an email.

    For this query(fake names have been used due to data sensitivity) type of conference = xenon and times attended 1

    I've inserted the brackets in the right places(i think) :

    Private Sub Command14_Click()


    Dim MyDb As Database
    Dim MyRS As Recordset
    Dim strSQL As String
    Dim Address As String


    strSQL = "SELECT * FROM qryAttendance WHERE ("
    strSQL = strSQL & "([type of conference]='" & Forms!frmAttendanceSearchView![Type of Conference] & "') "
    strSQL = strSQL & "AND ([times attended] >= " & Forms!frmAttendanceSearchView![Times Attended] & "));"


    Debug.Print strSQL

    Set MyDb = CurrentDb
    Set MyRS = MyDb.OpenRecordset(strSQL)
    MyRS.MoveFirst

    Set objOutlook = CreateObject("outlook.application")

    Do Until MyRS.EOF
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Address = MyRS![Email]

    With objOutlookMsg
    Set objOutlookRecip = .Recipients.add(TheAddress)
    objOutlookRecip.Type = olTo

    .Subject = "test"
    .Body = "test body"
    .Importance = olImportanceHigh

    For Each obbjOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With
    MyRS.MoveNext
    Loop
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub





    Originally posted by izyrider
    mine's got [], yours hasn't

    god only knows how A evaluates:
    Type Of (conference = "XENON")

    ...but that is what you are asking it to do.

    izy

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    .Recipients.add(TheAddress)
    should be
    .Recipients.add(Address).


    does it work?


    if no: now you've got debug working, paste the generated SQL into a query in SQL view and see if it goes (keeping your form open, of course)


    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Mar 2004
    Posts
    52
    it still gets stuck on the sql statement. Thanks for the suggestions, i'm going to tinker with it in SQL view. Will get back to you



    Originally posted by izyrider
    .Recipients.add(TheAddress)
    should be
    .Recipients.add(Address).


    does it work?


    if no: now you've got debug working, paste the generated SQL into a query in SQL view and see if it goes (keeping your form open, of course)


    izy

  15. #15
    Join Date
    Mar 2004
    Posts
    52
    hmmmmmm..... i'm currently running this statement in access in sql view


    SELECT * FROM qryAttendance WHERE (([type of conference]='XENON') AND ([times attended] >= 1));



    its not picking up the variables.........it keeps on asking me to input the values. Hmmmmm.....

Posting Permissions

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