Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Email users individual assigned attachment (2003)

    I have been working on this code but it seems not to do what i am looking for. I would like to send on the main form (On load) emails to users their tasks that are due in two days. i have a query that already determines those task and users name. When the email is sent I want to update the two fields in the query by selecting the check mark and inputing the date sent. This is what i have so far.

    Private Sub Form_Load()
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim Recipients As DAO.Field
    Dim SentMail As DAO.Field
    Dim SentDate As DAO.Field

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("qryDueTask", dbOpenDynaset)
    Set Recipients = RS![OPRName]
    Set SentMail = RS![SentEmail]
    Set SentDate = RS![EmailDate]

    Do While RS.EOF = False
    DoCmd.SendObject acSendQuery, qryDueTask, acFormatXLS, Recipients, , , " Action Tracker upcoming Task Due Report", _
    "Please review the attached file.", False

    RS.SentMail = 1
    RS.SentDate = Date
    RS.MoveNext

    Loop

    Set Recipients = Nothing
    RS.Close
    Set RS = Nothing
    Set DB = Nothing
    Set SentMail = Nothing
    Set SentDate = Nothing
    Exit Sub

    End Sub

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

    Re: Email users individual assigned attachment (2003)

    You send the same query to everyone. Is that OK?

    If you want to modify a record in a DAO recordset, you should insert

    RS.Edit

    before modifying fields, and

    RS.Update

    after modifying the fields, before moving on to the next record.

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Email users individual assigned attachment (2003)

    I will try that. I want to send the query as a excel attachment with the information that pertain only to the person in qryDueTask. the qryDueTask seems to be empty. when trying to debug it while DoCmd.SendObject. Below are my modification.

    Private Sub Form_Load()
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim Recipients As DAO.Field
    Dim SentMail As DAO.Field
    Dim SentDate As DAO.Field

    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("qryDueTask", dbOpenDynaset)
    Set Recipients = RS![OPRName]
    Set SentMail = RS![SentEmail]
    Set SentDate = RS![EmailDate]

    Do While RS.EOF = False
    DoCmd.SendObject acSendQuery, qryDueTask, acFormatXLS, Recipients, , , " Action Tracker upcoming Task Due Report", _
    "Please review the attached file.", False

    If SentMail = False Then
    RS.Edit
    SentMail = True
    SentDate = Date
    RS.Update
    End If

    RS.MoveNext

    Loop

    Set Recipients = Nothing
    RS.Close
    Set RS = Nothing
    Set DB = Nothing
    Set SentMail = Nothing
    Set SentDate = Nothing
    Exit Sub

    End Sub

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

    Re: Email users individual assigned attachment (2003)

    The name of the query should be enclosed in quotes:

    DoCmd.SendObject acSendQuery, "qryDueTask", acFormatXLS, Recipients, , , " Action Tracker upcoming Task Due Report", _
    "Please review the attached file.", False

  5. #5
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Email users individual assigned attachment (2003)

    Thanks Hans, I always forget about the small details. Your the best!. I just noticed the email attachment sent by the database has all personnel in the query. How can I make the code to send just the record from the query to the person responsible for it without including other users in the attachment. Thanks in advance.

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

    Re: Email users individual assigned attachment (2003)

    We'd have to know more about the query. For example, what is it's SQL string?

  7. #7
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Email users individual assigned attachment (2003)

    This is the SQL View of the access Query "qryDueTask"
    SELECT tblTask.TaskID, tblTask.Task, tblTask.OPRName, tblTask.Status, tblTask.DueDate, tblTask.SentEmail, tblTask.EmailDate
    FROM tblTask WHERE (((tblTask.Status)="PENDING") AND ((tblTask.DueDate)=Date()+2));

    Do you recommend to create a SQL statement to get the data and be able to send individual email to each name responsible two days prior their task due date.

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

    Re: Email users individual assigned attachment (2003)

    Try this:

    First, make a copy of the query qryDueTask, and name it qryDueTask2.
    Next, change the code as follows:
    <code>
    Dim DB As DAO.Database
    Dim QD As DAO.QueryDef
    Dim RS As DAO.Recordset
    Dim Recipients As DAO.Field
    Dim SentMail As DAO.Field
    Dim SentDate As DAO.Field
    Dim strSQL As String

    Set DB = CurrentDb
    Set QD = DB.QueryDefs("qryDueTask2")
    Set RS = DB.OpenRecordset("qryDueTask", dbOpenDynaset)
    Set Recipients = RS![OPRName]
    Set SentMail = RS![SentEmail]
    Set SentDate = RS![EmailDate]

    Do While RS.EOF = False
    strSQL = "SELECT TaskID, Task, OPRName, Status, " & _
    "DueDate, SentEmail, EmailDate FROM tblTask " & _
    "WHERE Status='PENDING' AND DueDate=Date()+2 AND " & _
    OPRName = '" & Recipients & "'"
    QD.SQL = strSQL
    DoCmd.SendObject acSendQuery, "qryDueTask2", acFormatXLS, Recipients, , , _
    " Action Tracker upcoming Task Due Report", _
    "Please review the attached file.", False

    If SentMail = False Then
    RS.Edit
    SentMail = True
    SentDate = Date
    RS.Update
    End If

    RS.MoveNext
    Loop

    Set Recipients = Nothing
    RS.Close
    Set RS = Nothing
    Set DB = Nothing
    Set SentMail = Nothing
    Set SentDate = Nothing
    Exit Sub
    </code>
    The code changes the SQL for qryDueTask2 to include only the record(s) for the current OPRName, and sends qryDueTask2.

  9. #9
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Email users individual assigned attachment (2003)

    Hans, YOU'RE THE GREATEST!...... It works great!

Posting Permissions

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