Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email from Access (2003)

    I need to generate an email from a form and I need to grab the last updated information from that form. Where should I start? Any help is greatly appreciated.
    Thanks,
    Kristen

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

    Re: Email from Access (2003)

    Do you mean the most recently updated record? If so, do you have a field that tracks the date/time a record was last updated?

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> You haven't provided any feedback to the replies you received during the last two years. That way, other Loungers don't know whether the replies were helpful.

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from Access (2003)

    I added an autonumber field...also there is a date field but there could be more than one a day, so I think the autonumber is the best bet.

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

    Re: Email from Access (2003)

    An AutoNumber field can be used to determine which record was most recently created, but it doesn't tell you which record was most recently updated. It is possible for a user to go back and edit an already existing record. This would make this record the most recently updated one, but it won't have the highest AutoNumber value.

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from Access (2003)

    For this particular project I am just looking for the most recently created. For the updates they run a report at the end of the month.

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

    Re: Email from Access (2003)

    OK. How do you want to send the info? Could it be an attachment to the e-mail, or do you want to insert values from the record in the body of the e-mail?

  7. #7
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from Access (2003)

    I want to insert the values in the body of the email.

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

    Re: Email from Access (2003)

    You could open a recordset that returns the latest record, and use that. Here is a simple example.

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM [NameOfTable] WHERE [NameOfAutoNumberField] = " & _
    "(SELECT Max([NameOfAutoNumberField]) FROM [NameOfTable])", dbOpenDynaset

    strMsg = "Dear " & rst![FirstName] & "," & vbCrLf & _
    "This is the report for " & rst![DateField]

    DoCmd.SendObject To:=rst![EmailField], Subject:="Something", MessageText:=strMsg

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

  9. #9
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from Access (2003)

    Are there some references that need to be loaded with this database?

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

    Re: Email from Access (2003)

    Yes, to the Microsoft DAO 3.6 Object Library (in Tools | References). This reference is set by default in a database created in Access 2003, but it might not be set in a database created in an earlier version of Access.

  11. #11
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from Access (2003)

    I am having trouble with getting part of the code to work. The part where you are establishing which recordset that it should look at. Any ideas what I need to do......

    Set rst = dbs.OpenRecordset("SELECT * FROM [Incident] WHERE [Autonumber] = " & _
    "(SELECT Max([Autonumber]) FROM [Incdent])", dbOpenDynaset

    Error: Expected list seperator or )

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

    Re: Email from Access (2003)

    Oops - there should be a closing parenthesis ) after dbOpenDynaset.

  13. #13
    Star Lounger
    Join Date
    Oct 2002
    Location
    New Salem, North Dakota, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email from Access (2003)

    Okay....Thanks...this worked awesome.......

    Thanks for you help.

  14. #14
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Email from Access (2003)

    Hans,

    I was searching for help on emailing from access.
    The help you provided on this feed is helpful, however I am doing something slightly different and was wondering if you could assist.
    On click of a button on my form, I need to send the "current record" in an email. Below is what you provided in this thread, what would I change the "Set rst=" to inorder to choose the current record.
    Any help would be greatly appreciated.
    -------------------------------------------------
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg As String

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM [NameOfTable] WHERE [NameOfAutoNumberField] = " & _
    "(SELECT Max([NameOfAutoNumberField]) FROM [NameOfTable])", dbOpenDynaset

    strMsg = "Dear " & rst![FirstName] & "," & vbCrLf & _
    "This is the report for " & rst![DateField]

    DoCmd.SendObject To:=rst![EmailField], Subject:="Something", MessageText:=strMsg

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    --------------------------------------------------

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

    Re: Email from Access (2003)

    If you want to use the current record in a form, you don't need to open a recordset. You can use the values from the form directly. For example:

    Dim strMsg As String
    strMsg = "Dear " & Me![FirstName] & "," & vbCrLf & _
    "This is the report for " & Me![DateField]

    DoCmd.SendObject To:=Me![EmailField], Subject:="Something", MessageText:=strMsg

    In the code behind a form, the keyword Me refers to the form itself. Of course, you must substitute the appropriate names and text.

Page 1 of 2 12 LastLast

Posting Permissions

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