Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Send Object Command (Access 2000)

    I'm using a database to track training dates and want to notify trainees about expiration of their training. I know how to use the SendObject function when the audience is fixed. Does anyone know how to use the results of a query to fill in the TO: line item of the send function? The result I'm looking for is running a query/report based on date criteria and sending an email to each trainee whose record shows up in the query.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Send Object Command (Access 2000)

    You would open a recordset based on your query and then loop through the records, sending an email to each trainee who met the criteria and came up in the query ... or did I misunderstand your question?
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Object Command (Access 2000)

    I think you do understand the question. I am not however VB conversant and at a loss how to proceed.

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Object Command (Access 2000)

    Charlotte,
    Thanks for the reply, and yes you did understand the question. I have some code shown below but the "Set rs" line is giving me an 'Invalid Argument' when I run it. TEST-ALERT is a query name and USERID is a field name. It is suppose to build a string (strEmails) from the USERIDs in each record of TEST-ALERT and then send them a report. Any comments on the line and what may be causing the error? Thanks

    Dim rs As Recordset
    Dim strEmails As String
    Set rs = CurrentDb.OpenRecordset("Select UserID FROM [test-alert]GROUP BY UserID;", dbOpenSnapshot)

    Do While Not rs.EOF
    strEmails = strEmails & rs!UserID & ";"
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    DoCmd.SendObject acSendReport, "test-alert-report", strEmails

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Object Command (Access 2000)

    Chris, I am also not that experienced with VBA, but if I look at some code from examples I have seen, the Set rs that you have written isn't the same. For example, here is the way I have seen it done.

    sSQL = "Select distinctrow * From tbl_User_Setup Where UserName = '" & _
    Me!txtUserName & "' and Password = '" & Me!txtPassword & "';"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)

    Although I don't know why it wouldn't work, you have an SQL query querying your query [test-alert]. Now the only problem you may have is that you placed brackets around test-alert. Try running it without the brackets. If that doesn't work, try the above example.

    Also, in the Do Loop, I don't think you need the rs.MoveNext, but I am not sure. Have fun.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Send Object Command (Access 2000)

    Since you're using Access 2000, the problem may be in your references. If you have the default ADO reference set, then you'll get an error on the Set rs = line because that's DAO syntax. If you have both ADO and DAO references set, you'll probably still get an error because the ADO reference is still the default and Access isn't sure which flavor of recordset you're trying to open.

    Go into the VB Editor (Alt+F11 is fastest), Select references from the Tools menu, and see if you have a DAO 3.6 reference set. If not, find it in the list and click on it to set the reference. Then go to your code and change it like this:

    Dim rs As DAO.Recordset

    The compile the project and see if everything compiles properly. If it does, try running your code. If you still have a problem, post back with the specifics.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Object Command (Access 2000)

    Lonnie:

    The MoveNext method is needed, otherwise the Loop code will just hang out on the first record of the recordset. That is, if your name is the first name in the record set, without the MoveNext method, strEmail would end up as: Lonnie; Lonnie; Lonnie; Lonnie....and on and on and you never get to the end of the recordset (EOF = End Of File) 'cause you never leave the first record.

    You'd think after having done this once you'd remember, but I've just spent nearly a full day wondering why my ASP page kept timing out only to find that I'd (mistakenly) deleted that all important MoveNext method. Oye! <img src=/S/weep.gif border=0 alt=weep width=21 height=16>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Object Command (Access 2000)

    Charlotte,
    THAT fixed the SELECT line. The DAO 3.6 reference was not turned on. The subroutine continues and now halts on the last line (DoCmd.SendObject) with a runtine error 2282 "The formats that enable you to output data as a MS Excel, rich-text, MS-text, or HTML file are missing from the Windows Registry"
    This one I don't understand because I can manually output excel and HTML content though the DB without issue.
    The rs strEmails loop is working correctly. When I mouse over the strEmails in the last line, the pop up window shows the content I'm looking for.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Object Command (Access 2000)

    You forgot the output format argument:

    <pre>DoCmd.SendObject acSendReport, "test-alert-report",acFormatDAP, strEmails
    </pre>


    See in the help-file for SendObject Method's other formats
    Francois

  10. #10
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Object Command (Access 2000)

    Thanks for the reply Francois. Adding the "acFormatDAP" argument did not have any corrective effect or change the
    the Runtime error.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Send Object Command (Access 2000)

    Do you try other formats ?
    acFormatHTML
    acFormatRTF
    acFormatTXT
    acFormatXLS
    I just test it and acFormatDAP give me the same error but the other formats works fine.
    Francois

Posting Permissions

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