Sending e-mail on After Update Event on Form

[Kirsti]

Hi,



I'm trying to send an e-mail on the After Update Event on a date field in a Form. I then want to send the e-mail to the name on that form.

The Name field is populated via a dropdown list, source of which is a table that also contains e-mail addresses.

I also want the body of the e-mail to contain 2 other fields in the form.


I have been trying to do this using the 'sendobject' action in a macro, but am getting nowhere fast.

Does anyone have experience with this? If so could you please give me an example of code that you are using?

Thanks,
Kirsti

[June7]

Why SendObject and macro and not the VBA procedure from your other thread?

I don't use macros but should be able to concatenate the data into the SendObject arguments.

[Kirsti]

Hi June7,

I didn't want to use the VBA procedure from my other thread because it requires each user to save code in a module in Outlook which is in the too hard basket for most of my users.

I have decided on VBA rather than a macro though, and I'm half way there with the code. I'll repost when I either have the code completed, or need help! Thanks.

[Kirsti]

Ok, I have it working using this code:

Private Sub Field5_AfterUpdate()
Dim sSubject As String
Dim stMail As String
Dim strBody As String
'message subject
sSubject = "My Message Subject"
'sends email to name in Field1 on the current form
stMail = DLookup("[Field1]", "tblTable1", "[Field1]= Forms![frmMyForm]![Field1]")
'email body contains Field2 and Field3 from current form
strBody = "MyText " & Forms![MyForm]![Field2] & " " & _
DLookup("[Field1]", "tblTable2", "[Field1]= Forms![frmMyForm]![Field3]") & _
" MyText"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acSendNoObject, , , stMail, , , sSubject, strBody, True
End Sub