Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    New Hampshire Coast
    Posts
    18

    Unanswered: create a loop for emailing

    hello all,

    I'm trying to write some code to email a report or a query. The receipient would be a sales rep. I've written a query which returns resulted based on the date. It will have a number of rows with different sales reps.

    I want the subject of the email to be the customer's name. An email shouldn't be sent if the report is blank.

    I thought I could use a loop of some sort, but what would I use as a counter? Since the results of the query doesn't have a number associated with each entry.

    Anyone done something like this?

    I would consider writing a report that filters each Sales Rep and use the DoCmd.SendObject, but I don't want to send blank reports.

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you loop through a recordset

    DAO code:

    dim dabs as dao.database
    dim recs as dao.recordset
    set dabs = currentdb
    set recs = dabs.openrecordset("theNameOfYourQueryInQuotes")
    with recs
    do while not .eof

    'here is your loop until all records have been visited

    .movenext
    loop
    end with
    set recs = nothing
    set dabs = nothing

    in your loop you use !fieldName to get data from the query record
    i.e.

    VarAddress = !AddressField


    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    Location
    New Hampshire Coast
    Posts
    18
    Thanks for the code sample. Do you know what reference(s) I need to include to get dao to work?


    Originally posted by izyrider
    you loop through a recordset

    DAO code:

    dim dabs as dao.database
    dim recs as dao.recordset
    set dabs = currentdb
    set recs = dabs.openrecordset("theNameOfYourQueryInQuotes")
    with recs
    do while not .eof

    'here is your loop until all records have been visited

    .movenext
    loop
    end with
    set recs = nothing
    set dabs = nothing

    in your loop you use !fieldName to get data from the query record
    i.e.

    VarAddress = !AddressField


    izy

  4. #4
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    its should be called "Data Access Objects " in references..or something along that line

  5. #5
    Join Date
    Mar 2004
    Location
    New Hampshire Coast
    Posts
    18
    Ooops, sorry, I found it.

    One more question. If I want to set the Email Address to one of the data elements in the query, what syntax should I use?

    Actually, I want to do the same for the message subject.

    Thanks again.

    Originally posted by MylesEngland
    Thanks for the code sample. Do you know what reference(s) I need to include to get dao to work?

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    either add the reference to dao 3.6 (under M for microsoft)

    or move the code to ADO (sorry - can't help you with that)

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    assuming you have a field "Sales Rep E Mail Address" (because like everyone else, you have wretched spaces in your field names) then

    dim strEaddress as string





    and in the loop:
    strEaddress = ![Sales Rep E Mail Address]

    and put strEaddress in your .sendobject or whatever you are using for the actual mailing.
    in fact you don't need the var and you can use the !field directly.



    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Mar 2004
    Location
    New Hampshire Coast
    Posts
    18

    Cool

    thanks izzy,

    I used recs!FieldName with no var. Works great! thanks again.

    Originally posted by izyrider
    assuming you have a field "Sales Rep E Mail Address" (because like everyone else, you have wretched spaces in your field names) then

    dim strEaddress as string





    and in the loop:
    strEaddress = ![Sales Rep E Mail Address]

    and put strEaddress in your .sendobject or whatever you are using for the actual mailing.
    in fact you don't need the var and you can use the !field directly.



    izy

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you are still inside

    with recs
    end with

    you don't need the recs!field, !field will do it.

    (saves typing and possibly executes faster)


    izy
    currently using SS 2008R2

Posting Permissions

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