Email Records

[teelee]

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

Thanks

[strive4peace]

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", _
"any...@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 --> "any...@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]

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]

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, _
"any...@anywhere.com", _
"updated records", _
true, _
"John Doe"

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

[strive4peace]

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]