auto generated email

  • Thread starter Thread starter Slez via AccessMonster.com
  • Start date Start date
S

Slez via AccessMonster.com

I have reviewed as many postings on this topic, but am in need of help
generating the actual code to accomplish it. I appreciate any help!

After a new record has been added in a form, I would like to automatically
send an email to an individual as a sort of notification. I know that I need
to use either the After Insert or After Update event, and that I should use
DoCmd.SendObject. What I don't know how to do is put all the code together.

The record is added in frmFinishSampleEntry.
Applications are Access 2002 and Outlook.
Destination recipient is listed as "Smith, Joe" (example)
I'd like to include the record in the field [JobNumber] in either the subject
or text of the email.
Would also like to include the text "A new sample has been entered."

As mentioned, help is greatly appreciated!
 
Two possibilities come to mind:

1) create a query that will select ONLY the record that you have just
created and use sendobject (see help for format) and that will include
it as an attachment I believe.

2) use Automation:


Set o = CreateObject("Outlook.Application")

Set m = o.CreateItem(0)
m.To = "Paul Tucker" ' The distribution list name goes here
m.Subject = "SUPP Analysis Spreadsheet "
m.body = Chr(13) & Chr(13) & _
" Date: " & date & Chr(13) & _
Chr(13)

' as part of body add fields and descripsions that you have references
to
' use chr(13) to put each on separate line.

' (html is available but might be more difficult but you could then
' handle the data as if it were a table, field by field)

m.attachments.Add ReportDir & exportName ' point to the full address of
attachment here

' There can be multiple of the above

' m.display ' display shows email and the user has to press send button


m.send ' Send does it automatically with security from Outlook

Ron
 
Thanks for your response! I'm trying the Automation option in your response,
but am having trouble right at the start. I placed the following code in the
After Insert Event:

Private Sub Form_AfterInsert()

Set O = CreateObject("Outlook.Application")

Set m = O.CreateItem(0)
m.To = "Slezewski, Kevin"
m.Subject = "Finish Sample Request Entered"
m.Body = Chr(13) & "JobNumber"
m.display

End Sub

After a record is entered, I get a Compile Error: "Variable not defined" and
it highlights the first "O =". I am not grasping what variable it is
referring to.
Can you offer any further assistance? Thanks in advance!
Slez
 
try this:

dim O as Object

although I don't have that. I am going to research back on some of my
other references on this to see if I can see anything else.

Ron
 
Well, that got me a little farther, I inserted that into the code, but then
got the same compile error except that it highlights the "m =". I then tried
to insert: "Dim m As Item" , but it didn't recognize that and another error
message resulted.

If you have any other ideas I'll take 'em!
Thanks for your continued help!
Slez
 
now add:

dim m as Outlook.MailItem


I think it has something to do with explicit declarations (which I do
not have)

Ron
 
It makes sense once again, but now gives me a "Compile error: User-defined
type not defined"
I'll keep experimenting with it. In the meantime, if anything else comes to
mind, I'll appreciate the input.
Thanks for all your help!
Slez
 
Two things else that I can think of.

Check in your module near the top for
explicite and comment out that line.

and/or
check your references and see if OLE Automation has been checked.
If not then check it.

and/or
check your referenes and see if anything related to 'Microsoft
Outlook" is checked. Play with the various references in the Microsoft
area. It may be one of those.

Ron
 
OK...I just can't give up on this yet and will continue to tap your brain as
long as you let me!

I checked into the references. OLE Automation is checked, and I also checked
"Microsoft Office Outlook View Control" & "Microsoft Outlook 11.0 Object
Library"

I am working in Access 2002 with a 2000 file format, and have verified that
our Outlook is 2003, version 11.0.8010.8107 (if that means anything). But it
looks like I should be on the right page with that.

My current code is as follows:

Private Sub Form_AfterInsert()

Dim O As Object
Dim m As Outlook.MailItem

Set O = CreateObject("Outlook.Application")

Set m = O.CreateMailItem(0)
m.To = "Slezewski, Kevin"
m.Subject = "Finish Sample Request Entered"
m.Body = Chr(13) & "JobNumber"
m.display

End Sub

I now get a Run-time error '438': Object doesn't support this property or
method
When I Debug, it highlights "Set m = O.CreateMailItem(0)"

Any thoughts?
Again...thank you!
Slez
 
Ron - It works! It works!

I viewed the post from Stefan, made a few adjustments, and ended up with the
following code:

Private Sub Form_AfterInsert()

Dim O As Outlook.Application
Dim m As Outlook.MailItem

Set O = CreateObject("Outlook.Application")

Set m = Outlook.CreateItem(0)
m.To = "Slez, Kevin"
m.Subject = "Finish Sample Request Entered"
m.Body = (JobNumber)
m.display

End Sub

I need to do some fine tuning with the body to get the finished product I'm
looking for, but it actually works! Turns out I'm leaving in 10 minutes for
a week's vacation, so the timing is awesome. Thanks so much for all of your
help and guidance with this! It is greatly appreciated!!
Slez
 
Back
Top