Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    email from Access using Outlook (Access 2k win 2k outlook 2k)

    Hello again

    OK, I've been trying to get my database to email a report to several different people, the report is in the form of a standard template with the blanks filled in from a query.

    The following code is what I'm using to achieve this (please don't laugh, I am not a programmer and this will prove it).

    Private Sub sendemail_Click()

    Dim dbs As DAO.Database
    Dim rstfolloup As DAO.Recordset


    Set dbs = CurrentDb
    Set rstfolloup = dbs.OpenRecordset("folloupemailqry", dbOpenDynaset)

    DoCmd.Echo False, "Access is sending your emails"


    If Not IsNull(Me.quotenum) Then
    With rstfolloup
    rstfolloup.MoveFirst
    Do Until .EOF
    DoCmd.OpenReport "folloupemailrep", acViewPreview
    DoCmd.SendObject acSendReport, "folloupemailrepv2", acFormatRTF, rstfolloup!custcontactemailPL, , , "Quote Follow Up", "Hello, checking up on this enquiry"
    DoCmd.Close acReport, "folloupemailrep", acSaveNo
    DoCmd.GoToRecord , , acNext
    rstfolloup.MoveNext
    Loop
    End With
    End If

    DoCmd.Echo True, "Access has sent your emails"

    End Sub

    The code generally works fine, I get the right number of emails arriving in my inbox, all with the right version of the report attached to them. My problem is that when the recordset arrives at the last record it naturally gives me an error, the code is saying go to the next record and there isn't a next record, hence error. How do I get around this, I need to make the form cycle through the records or the reports are all the same, using code to cycle through the record set with out using the form results in all recipients getting the same report as well.

    I've tried moving the goto next record command around in the nest of 'if then', 'do while' and 'with' loops, all to no, successful, avail (just new types of error).

    It's obvious that my method is flawed, but I haven't the foggiest idea what I should have done, or how to suppress the error message for that matter.

    As a separate issue, is there any way to avoid having to click 'send' for every email?

    Thanks for the help

    Ian

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

    Re: email from Access using Outlook (Access 2k win 2k outlook 2k)

    Some remarks:
    1. <LI>You put Echo False before the loop and Echo True after it, so the user won't see anything happening. Yet you open a report in Preview mode and close it again in the loop. This seems useless.
      <LI>You loop through the records of a recordset AND through the records of the form. Is there any need for this?
      <LI>If so, are you sure that the form and the recordset have the same number of records? If folloupemailqry is the record source of the form, this will be the case.
      <LI>If the form and recordset are meant to be synchronized, you should move to the first record in the form before starting the loop.
    HTH

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: email from Access using Outlook (Access 2k win 2k outlook 2k)

    Hans

    Thanks for the comments, I've had another look at the code after thinking about your questions and here's the modified version, no more error message <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> it still makes me click the send button 'x' times though. Yet again my sanity is slowly returning. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Private Sub sendemail_Click()

    Dim dbs As DAO.Database
    Dim rstfolloup As DAO.Recordset


    Set dbs = CurrentDb
    ???Set rstfolloup = dbs.OpenRecordset("folloupemailqry", dbOpenDynaset)??? not sure if this should still be there, but have left it

    DoCmd.Echo False, "Access is sending your emails"


    If Not IsNull(Me.quotenum) Then
    With Me.Recordset
    DoCmd.GoToRecord , , acFirst
    Do Until .EOF
    DoCmd.SendObject acSendReport, "folloupemailrepv2", acFormatRTF, rstfolloup!custcontactemailPL, , , "Quote Follow Up", "Hello, checking up on this enquiry"
    .Movenext
    Loop
    End With
    End If

    DoCmd.Echo True, "Access has sent your emails"

    End Sub

    Thanks for help

    Ian

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

    Re: email from Access using Outlook (Access 2k win 2k outlook 2k)

    Ian,

    If you're using Me.Recordset, you don't need rstfolloup, and you should use !custcontactemailPL instead of rstfolloup!custcontactemailPL. Also, you don't need DoCmd.GotoRecord , , acFirst.

    If Outlook is asking you for a confirmation each time, it's probably because of the Outlook security patch. If you want to avoid this, do a search in this Forum for "redemption" (without the quotes). Outlook Redemption is a library that uses Extended MAPI instead of MAPI to handle automated sending of e-mails.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: email from Access using Outlook (Access 2k win 2k outlook 2k)

    Hans

    Thanks, made the suggested improvements. I had a look for redemption and found it, thanks for that.

    Ian

Posting Permissions

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