Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17

    Unanswered: Auto email notification HELP

    Hi, does anyone know how to be able to auto email someone in MS access 2000?

    The way I would like to do it is comparing a date field with present date and if the date field is inferior to present date, auto email a user, but only once!

    Is this possible?

    Thanks!

    Owen

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Code to email is:



    Set myOlApp = CreateObject("Outlook.Application")
    Set myitem = myOlApp.CreateItem(olMailItem)
    Set myRecipient = myitem.Recipients.Add("A valid@email addy")
    '
    ' attachments..
    '
    Set myAttachments = myitem.Attachments
    myAttachments.Add "C:\an attachment.xls"
    '
    '
    '
    myitem.Subject = "Issues between " & Me.From_Date & " to " & Me.To_Date

    Msg = Chr(13) & "Hi," & Chr(13) & Chr(13)
    Msg = Msg & "Please find attached blah blah blah...."


    myitem.Body = Msg

    myitem.send

    'myitem.display will display if you want (suprise!!)



    You'll probably have to do some sort of date check then incorporate code like above..

    The easiest thing would be to have that date field update once its run.

    As I don't know how your tables with these dtaes is setup I don't know what code to write.

    If you let me have a bit more detail I might be able to write some more..

    Hope it helps (and have a good weekend Owen)

    Regards

    Ken
    Last edited by Ken_Hart; 03-05-04 at 12:54.

  3. #3
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17
    Originally posted by Ken_Hart
    Code to email is:



    Set myOlApp = CreateObject("Outlook.Application")
    Set myitem = myOlApp.CreateItem(olMailItem)
    Set myRecipient = myitem.Recipients.Add("A valid@email addy")
    '
    ' attachments..
    '
    Set myAttachments = myitem.Attachments
    myAttachments.Add "C:\an attachment.xls"
    '
    '
    '
    myitem.Subject = "Issues between " & Me.From_Date & " to " & Me.To_Date

    Msg = Chr(13) & "Hi," & Chr(13) & Chr(13)
    Msg = Msg & "Please find attached blah blah blah...."


    myitem.Body = Msg

    myitem.send

    'myitem.display will display if you want (suprise!!)



    You'll probably have to do some sort of date check then incorporate code like above..

    The easiest thing would be to have that date field update once its run.

    As I don't know how your tables with these dtaes is setup I don't know what code to write.

    If you let me have a bit more detail I might be able to write some more..

    Hope it helps (and have a good weekend Owen)

    Regards

    Ken
    Thanks again, Ken!

    I will try this ! Nice of you to help me and I appreciate an expert at this, since I don't know too much about VB or programming.

    For the date in my table, it is just a medium date format (ie: 19-jun-04). I could make up a query to compare this date with present date, then run the code above, right?

    Regards,

    Owen

  4. #4
    Join Date
    Mar 2004
    Posts
    29
    Thank YOU!!! Heh, didn't even have to ask the question.

  5. #5
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Hi Owen,

    I'm not really an expert, I've just been messing around alot with access. :P
    But I try to help anyone who wants to learn stuff. Then they can help others and everyone gets better in what they do.

    Sure, you could run a query to see the difference in dates, like a datediff function..

    If yuo come unstuck with writing code/queries to sort this let me know and I'll sort it for you. Just let me know the table name and field name and I'll make sure it works.

    Regards

    Ken.
    Last edited by Ken_Hart; 03-06-04 at 10:59.

  6. #6
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    17
    The table name is "TRDX_Calibration_tbl" and the field name is "TRDXNextCalDate" this date is calculated based upon another date field and combo box field where the user selects in the combo box a number (Months) for an interval between dates. When this number is selected I used DateAdd and gives me the NextCalDate.:

    Thanks,

    Owen

    Originally posted by Ken_Hart
    Hi Owen,

    I'm not really an expert, I've just been messing around alot with access. :P
    But I try to help anyone who wants to learn stuff. Then they can help others and everyone gets better in what they do.

    Sure, you could run a query to see the difference in dates, like a datediff function..

    If yuo come unstuck with writing code/queries to sort this let me know and I'll sort it for you. Just let me know the table name and field name and I'll make sure it works.

    Regards

    Ken.

Posting Permissions

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