Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Email Records

Discussion in 'Information Technology' started by teelee, Jul 28, 2006.

Tags:
  1. teelee

    teelee
    Expand Collapse
    Guest

    Does anyone know if you can email records after data is entered?

    Thanks
     
  2. Loading...


  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    you can use the AfterUpdate event of the form

    but rather than emailing one record at a time, why not do this:

    add this field to the underlying recordset:

    DateCreated, date, DefaultValue --> =Now()

    when the form is opened, record the starttime

    on the form Close event, email all the record whose
    DateCreated >= StartTime

    here is some generic emailing code -- if you are emailing a
    form or report, it must first be filtered...

    '========================================= Email
    '------------------------------------ eMailObject
    'send a message through the DEFAULT Email program
    '
    Sub eMailObject ( _
    pSendType as Long, _
    pObjectName As String, _
    pEmailAddress As String, _
    pFriendlyName As String, _
    pBooEditMessage As Boolean, _
    pWhoFrom As String)

    'Email attachment to someone
    'and construct the subject and message

    'example useage:
    ' on the command button code to process a report -->
    ' eMailObject _
    "qrySonglist", _
    "anyone@mymailbox.com", _
    "Original Songs from an upcoming Star", _
    false, _
    "Susan Manager"

    'PARAMETERS
    'pSendType -->
    ' acSendReport = 3
    ' filter property need be saved
    ' acSendForm = 2
    ' the active form filter will be respected
    ' acSendQuery = 1
    ' ... etc
    'pObjectName --> "qrySonglist"
    'pEmailAddress --> "anyone@mymailbox.com"
    'pFriendlyName --> Original Songs from an upcoming Star"
    'pBooEditMessage --> true if you want to edit message
    ' before mail is sent
    ' --> false to send automatically
    'pWhoFrom --> "Susan Doe"

    'you can substitute acFormatSNP
    ' --> acFormatHTML
    ' --> acFormatRTF
    ' --> acFormatXLS
    ' --> acFormatTXT
    ' etc

    on error goto Err_proc

    DoCmd.SendObject _
    pSendType, _
    pObjectName, _
    acFormatSNP, _
    pEmailAddress _
    , , , pFriendlyName _
    & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
    pFriendlyName & " is attached --- " _
    & "Regards, " _
    & pWhoFrom, _
    pBooEditMessage

    Exit_proc:
    Exit Sub

    Err_proc:
    MsgBox Err.Description, , _
    "ERROR " & Err.Number & " eMailObject"

    'press F8 to find problem and fix
    'comment or remove next line when code is done
    Stop : Resume

    Resume Exit_proc

    End Sub

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    remote programming and training
    strive4peace2006 at yahoo.com
    *
    Have an awesome day ;)


    teelee wrote:
    > Does anyone know if you can email records after data is entered?
    >
    > Thanks
     
  4. teelee

    teelee
    Expand Collapse
    Guest

    Hi thanks for responding. Can you tell me how I would write this and where I
    would put it? Do I go to properties box and select AfterUpdate and use an
    Expression builder, a Macro, or a Code builder? This is all new to me and I
    just want to make sure it's all correct.

    Thanks

    "strive4peace" wrote:

    > you can use the AfterUpdate event of the form
    >
    > but rather than emailing one record at a time, why not do this:
    >
    > add this field to the underlying recordset:
    >
    > DateCreated, date, DefaultValue --> =Now()
    >
    > when the form is opened, record the starttime
    >
    > on the form Close event, email all the record whose
    > DateCreated >= StartTime
    >
    > here is some generic emailing code -- if you are emailing a
    > form or report, it must first be filtered...
    >
    > '========================================= Email
    > '------------------------------------ eMailObject
    > 'send a message through the DEFAULT Email program
    > '
    > Sub eMailObject ( _
    > pSendType as Long, _
    > pObjectName As String, _
    > pEmailAddress As String, _
    > pFriendlyName As String, _
    > pBooEditMessage As Boolean, _
    > pWhoFrom As String)
    >
    > 'Email attachment to someone
    > 'and construct the subject and message
    >
    > 'example useage:
    > ' on the command button code to process a report -->
    > ' eMailObject _
    > "qrySonglist", _
    > "anyone@mymailbox.com", _
    > "Original Songs from an upcoming Star", _
    > false, _
    > "Susan Manager"
    >
    > 'PARAMETERS
    > 'pSendType -->
    > ' acSendReport = 3
    > ' filter property need be saved
    > ' acSendForm = 2
    > ' the active form filter will be respected
    > ' acSendQuery = 1
    > ' ... etc
    > 'pObjectName --> "qrySonglist"
    > 'pEmailAddress --> "anyone@mymailbox.com"
    > 'pFriendlyName --> Original Songs from an upcoming Star"
    > 'pBooEditMessage --> true if you want to edit message
    > ' before mail is sent
    > ' --> false to send automatically
    > 'pWhoFrom --> "Susan Doe"
    >
    > 'you can substitute acFormatSNP
    > ' --> acFormatHTML
    > ' --> acFormatRTF
    > ' --> acFormatXLS
    > ' --> acFormatTXT
    > ' etc
    >
    > on error goto Err_proc
    >
    > DoCmd.SendObject _
    > pSendType, _
    > pObjectName, _
    > acFormatSNP, _
    > pEmailAddress _
    > , , , pFriendlyName _
    > & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
    > pFriendlyName & " is attached --- " _
    > & "Regards, " _
    > & pWhoFrom, _
    > pBooEditMessage
    >
    > Exit_proc:
    > Exit Sub
    >
    > Err_proc:
    > MsgBox Err.Description, , _
    > "ERROR " & Err.Number & " eMailObject"
    >
    > 'press F8 to find problem and fix
    > 'comment or remove next line when code is done
    > Stop : Resume
    >
    > Resume Exit_proc
    >
    > End Sub
    >
    > '~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    > *
    > Have an awesome day ;)
    >
    >
    > teelee wrote:
    > > Does anyone know if you can email records after data is entered?
    > >
    > > Thanks

    >
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hello,

    The procedure I gave you goes into a general module. From
    the database window, click on Modules and then click NEW ...
    paste the EmailObject code I gave you.

    Then, compile your database

    Whenever you write or paste code, your should ALWAYS compile
    it before you attempt to run it.

    from the menu: Debug, Compile

    fix any errors on the yellow highlighted lines

    keep compiling until nothing happens (this is good!)

    Now, you will need a way to call it and construct the
    parameters that it will need.

    ~~~

    here is something you can try...

    The following code assumes you are behind a form called
    ReportMenu

    make an unbound control
    Name --> FormStartTime
    visible --> False (you may wish it to show while you are
    developing, change to False to implement for users)

    on the form OnLoad event
    (turn on the properties window
    select the form
    click the Events tab
    click in the LOAD event
    press the F1 key to read the help)

    --> create an [Event Procedure] to store what date and time
    the form was opened

    choose [Event Procedure] from the combo and then click the
    Builder button
    OR just click the Builder ... button and choose Code Builder
    from the dialog box

    This will put you between the procedure declaration and the
    End Sub

    insert this line between those 2 lines -->

    me.FormStartTime = now()

    save your form and test it.

    leave it open while you make a query

    ~~~~

    now that we have a form that records a start time, make a
    query to show records created after that startTime

    choose the table you wish to report from

    on the query grid, put the fields you wish to eMail

    and then

    field --> DateCreated
    show --> false
    criteria --> >= forms!ReportMenu!FormStartTime

    for purposes of an example, I will assume you will name this
    query --> qryExportRecords

    ~~~

    now, on the ReportMenu form OnClose event...

    eMailObject _
    acQuery, _
    qryExportRecords, _
    "anyone@anywhere.com", _
    "updated records", _
    true, _
    "John Doe"


    if you wish to use the eMailObject routine -- otherwise, you
    can use the SendObject action



    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    remote programming and training
    strive4peace2006 at yahoo.com
    *
    Have an awesome day ;)


    teelee wrote:
    > Hi thanks for responding. Can you tell me how I would write this and where I
    > would put it? Do I go to properties box and select AfterUpdate and use an
    > Expression builder, a Macro, or a Code builder? This is all new to me and I
    > just want to make sure it's all correct.
    >
    > Thanks
    >
    > "strive4peace" wrote:
    >
    >
    >>you can use the AfterUpdate event of the form
    >>
    >>but rather than emailing one record at a time, why not do this:
    >>
    >>add this field to the underlying recordset:
    >>
    >>DateCreated, date, DefaultValue --> =Now()
    >>
    >>when the form is opened, record the starttime
    >>
    >>on the form Close event, email all the record whose
    >>DateCreated >= StartTime
    >>
    >>here is some generic emailing code -- if you are emailing a
    >>form or report, it must first be filtered...
    >>
    >>'========================================= Email
    >>'------------------------------------ eMailObject
    >>'send a message through the DEFAULT Email program
    >>'
    >>Sub eMailObject ( _
    >> pSendType as Long, _
    >> pObjectName As String, _
    >> pEmailAddress As String, _
    >> pFriendlyName As String, _
    >> pBooEditMessage As Boolean, _
    >> pWhoFrom As String)
    >>
    >> 'Email attachment to someone
    >> 'and construct the subject and message
    >>
    >> 'example useage:
    >> ' on the command button code to process a report -->
    >> ' eMailObject _
    >> "qrySonglist", _
    >> "anyone@mymailbox.com", _
    >> "Original Songs from an upcoming Star", _
    >> false, _
    >> "Susan Manager"
    >>
    >> 'PARAMETERS
    >> 'pSendType -->
    >> ' acSendReport = 3
    >> ' filter property need be saved
    >> ' acSendForm = 2
    >> ' the active form filter will be respected
    >> ' acSendQuery = 1
    >> ' ... etc
    >> 'pObjectName --> "qrySonglist"
    >> 'pEmailAddress --> "anyone@mymailbox.com"
    >> 'pFriendlyName --> Original Songs from an upcoming Star"
    >> 'pBooEditMessage --> true if you want to edit message
    >> ' before mail is sent
    >> ' --> false to send automatically
    >> 'pWhoFrom --> "Susan Doe"
    >>
    >> 'you can substitute acFormatSNP
    >> ' --> acFormatHTML
    >> ' --> acFormatRTF
    >> ' --> acFormatXLS
    >> ' --> acFormatTXT
    >> ' etc
    >>
    >> on error goto Err_proc
    >>
    >> DoCmd.SendObject _
    >> pSendType, _
    >> pObjectName, _
    >> acFormatSNP, _
    >> pEmailAddress _
    >> , , , pFriendlyName _
    >> & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
    >> pFriendlyName & " is attached --- " _
    >> & "Regards, " _
    >> & pWhoFrom, _
    >> pBooEditMessage
    >>
    >>Exit_proc:
    >> Exit Sub
    >>
    >>Err_proc:
    >> MsgBox Err.Description, , _
    >> "ERROR " & Err.Number & " eMailObject"
    >>
    >> 'press F8 to find problem and fix
    >> 'comment or remove next line when code is done
    >> Stop : Resume
    >>
    >> Resume Exit_proc
    >>
    >>End Sub
    >>
    >>'~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >> *
    >> Have an awesome day ;)
    >>
    >>
    >>teelee wrote:
    >>
    >>>Does anyone know if you can email records after data is entered?
    >>>
    >>>Thanks

    >>
     
  6. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: Email Records -- SendObject parameters

    For understanding VBA better, email me and request my VBA
    manual -- I have 3 chapters written and one of these days
    will post them where they can be downloaded, but for now, I
    will be happy to email them. Reading these will give you a
    good foundation to understand the replies to your post.

    In my previous post, I did not address using SendObject
    directly in the code behind your report menu form.

    The general routine I gave you is good to use for
    understanding how to write modular code -- and seeing an
    example of how SendObject is used to send email and an
    Access object (query, form, report, etc). I skipped some
    parameters that you may want to use.

    ... for you, it would be better to do your first program
    directly with the built-in capabilities of Access


    to use SendObject directly in your code -->

    DoCmd.SendObject

    and then these parameters...

    'SendObject
    '[objecttype]
    '[, objectname]
    '[, outputformat]
    '[, to]
    '[, cc]
    '[, bcc]
    '[, subject]
    '[, messagetext]
    '[, editmessage]
    '[, templatefile]


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    remote programming and training
    strive4peace2006 at yahoo.com
    *
    Have an awesome day ;)


    teelee wrote:
    > Hi thanks for responding. Can you tell me how I would write this and where I
    > would put it? Do I go to properties box and select AfterUpdate and use an
    > Expression builder, a Macro, or a Code builder? This is all new to me and I
    > just want to make sure it's all correct.
    >
    > Thanks
    >
    > "strive4peace" wrote:
    >
    >
    >>you can use the AfterUpdate event of the form
    >>
    >>but rather than emailing one record at a time, why not do this:
    >>
    >>add this field to the underlying recordset:
    >>
    >>DateCreated, date, DefaultValue --> =Now()
    >>
    >>when the form is opened, record the starttime
    >>
    >>on the form Close event, email all the record whose
    >>DateCreated >= StartTime
    >>
    >>here is some generic emailing code -- if you are emailing a
    >>form or report, it must first be filtered...
    >>
    >>'========================================= Email
    >>'------------------------------------ eMailObject
    >>'send a message through the DEFAULT Email program
    >>'
    >>Sub eMailObject ( _
    >> pSendType as Long, _
    >> pObjectName As String, _
    >> pEmailAddress As String, _
    >> pFriendlyName As String, _
    >> pBooEditMessage As Boolean, _
    >> pWhoFrom As String)
    >>
    >> 'Email attachment to someone
    >> 'and construct the subject and message
    >>
    >> 'example useage:
    >> ' on the command button code to process a report -->
    >> ' eMailObject _
    >> "qrySonglist", _
    >> "anyone@mymailbox.com", _
    >> "Original Songs from an upcoming Star", _
    >> false, _
    >> "Susan Manager"
    >>
    >> 'PARAMETERS
    >> 'pSendType -->
    >> ' acSendReport = 3
    >> ' filter property need be saved
    >> ' acSendForm = 2
    >> ' the active form filter will be respected
    >> ' acSendQuery = 1
    >> ' ... etc
    >> 'pObjectName --> "qrySonglist"
    >> 'pEmailAddress --> "anyone@mymailbox.com"
    >> 'pFriendlyName --> Original Songs from an upcoming Star"
    >> 'pBooEditMessage --> true if you want to edit message
    >> ' before mail is sent
    >> ' --> false to send automatically
    >> 'pWhoFrom --> "Susan Doe"
    >>
    >> 'you can substitute acFormatSNP
    >> ' --> acFormatHTML
    >> ' --> acFormatRTF
    >> ' --> acFormatXLS
    >> ' --> acFormatTXT
    >> ' etc
    >>
    >> on error goto Err_proc
    >>
    >> DoCmd.SendObject _
    >> pSendType, _
    >> pObjectName, _
    >> acFormatSNP, _
    >> pEmailAddress _
    >> , , , pFriendlyName _
    >> & Format(Now(), " ddd m-d-yy h:nn am/pm"), _
    >> pFriendlyName & " is attached --- " _
    >> & "Regards, " _
    >> & pWhoFrom, _
    >> pBooEditMessage
    >>
    >>Exit_proc:
    >> Exit Sub
    >>
    >>Err_proc:
    >> MsgBox Err.Description, , _
    >> "ERROR " & Err.Number & " eMailObject"
    >>
    >> 'press F8 to find problem and fix
    >> 'comment or remove next line when code is done
    >> Stop : Resume
    >>
    >> Resume Exit_proc
    >>
    >>End Sub
    >>
    >>'~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >> *
    >> Have an awesome day ;)
    >>
    >>
    >>teelee wrote:
    >>
    >>>Does anyone know if you can email records after data is entered?
    >>>
    >>>Thanks

    >>
     

Share This Page