Send email on field change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to automate a process within my database. The process being that
when a Super User rejects a transaction it will generate an email to the user
who last modified the record with details of why the transaction has been
rejected.

Here is the code I have been trying to use.

Private Sub Form_AfterUpdate()

If [AR Manager Approval] = "Rejected" Then

DoCmd.SendObject acSendNoObject, _
To:=[LastModifiedBy], _
Subject:="Your transaction request has been rejected", _
MessageText:="Your request to have" & [CustomerName] &
[Invoice Number] & _
" sent to debt refferal has been rejected for the following
reasons - " & _
[Rejected Reason]

End If

End Sub

I am getting the following run time error when the code tries to execute.
Error 2465 - Bad Debt Tracking can't find the field '|' reffered to in your
expression.

I have checked the code and all looks fine. But I am not familiar with vba
in access.

Can someone please help with this query or maybe suggest an alternative?
 
Your statement is missing a couple of arguments, and some of the arguments
are not the right format. Try this:

Private Sub Form_AfterUpdate()
If [AR Manager Approval] = "Rejected" Then
Dim msgSubject As String
Dim msgBody As String
msgSubject = "Your transaction request has been rejected"
msgBody = "Your request to have" & [CustomerName] & [Invoice Number] & _
" sent to debt refferal has been rejected for the following reasons - "
& [Rejected Reason]
DoCmd.SendObject acSendNoObject, , , [LastModifiedBy], , msgSubject,
msgBody
End If
End Sub
 
Thanks Brian

Brian said:
Your statement is missing a couple of arguments, and some of the arguments
are not the right format. Try this:

Private Sub Form_AfterUpdate()
If [AR Manager Approval] = "Rejected" Then
Dim msgSubject As String
Dim msgBody As String
msgSubject = "Your transaction request has been rejected"
msgBody = "Your request to have" & [CustomerName] & [Invoice Number] & _
" sent to debt refferal has been rejected for the following reasons - "
& [Rejected Reason]
DoCmd.SendObject acSendNoObject, , , [LastModifiedBy], , msgSubject,
msgBody
End If
End Sub

Enigo said:
Hi,

I am trying to automate a process within my database. The process being that
when a Super User rejects a transaction it will generate an email to the user
who last modified the record with details of why the transaction has been
rejected.

Here is the code I have been trying to use.

Private Sub Form_AfterUpdate()

If [AR Manager Approval] = "Rejected" Then

DoCmd.SendObject acSendNoObject, _
To:=[LastModifiedBy], _
Subject:="Your transaction request has been rejected", _
MessageText:="Your request to have" & [CustomerName] &
[Invoice Number] & _
" sent to debt refferal has been rejected for the following
reasons - " & _
[Rejected Reason]

End If

End Sub

I am getting the following run time error when the code tries to execute.
Error 2465 - Bad Debt Tracking can't find the field '|' reffered to in your
expression.

I have checked the code and all looks fine. But I am not familiar with vba
in access.

Can someone please help with this query or maybe suggest an alternative?
Click to expand...
Click to expand...
 
YOu are welcome. Did it work?

Enigo said:
Thanks Brian

Brian said:
Your statement is missing a couple of arguments, and some of the arguments
are not the right format. Try this:

Private Sub Form_AfterUpdate()
If [AR Manager Approval] = "Rejected" Then
Dim msgSubject As String
Dim msgBody As String
msgSubject = "Your transaction request has been rejected"
msgBody = "Your request to have" & [CustomerName] & [Invoice Number] & _
" sent to debt refferal has been rejected for the following reasons - "
& [Rejected Reason]
DoCmd.SendObject acSendNoObject, , , [LastModifiedBy], , msgSubject,
msgBody
End If
End Sub

Enigo said:
Hi,

I am trying to automate a process within my database. The process being that
when a Super User rejects a transaction it will generate an email to the user
who last modified the record with details of why the transaction has been
rejected.

Here is the code I have been trying to use.

Private Sub Form_AfterUpdate()

If [AR Manager Approval] = "Rejected" Then

DoCmd.SendObject acSendNoObject, _
To:=[LastModifiedBy], _
Subject:="Your transaction request has been rejected", _
MessageText:="Your request to have" & [CustomerName] &
[Invoice Number] & _
" sent to debt refferal has been rejected for the following
reasons - " & _
[Rejected Reason]

End If

End Sub

I am getting the following run time error when the code tries to execute.
Error 2465 - Bad Debt Tracking can't find the field '|' reffered to in your
expression.

I have checked the code and all looks fine. But I am not familiar with vba
in access.

Can someone please help with this query or maybe suggest an alternative?
Click to expand...
Click to expand...
Click to expand...
 
Brian,

Would there be a way of having an email generated and sent out when an
enquiry is nearing its deadline?

I have a field that says [Target Reply Date] and would like to be able to
have a notification of some sort be it an email or the record flashing red
when I open the table, when it is about a week from completion.

I am teaching myself Access at the moment for work as our IT has no formal
training and it keeps surprising me with what it can do.

Thanks

Nikki

Brian said:
YOu are welcome. Did it work?

Enigo said:
Thanks Brian

Brian said:
Your statement is missing a couple of arguments, and some of the arguments
are not the right format. Try this:

Private Sub Form_AfterUpdate()
If [AR Manager Approval] = "Rejected" Then
Dim msgSubject As String
Dim msgBody As String
msgSubject = "Your transaction request has been rejected"
msgBody = "Your request to have" & [CustomerName] & [Invoice Number] & _
" sent to debt refferal has been rejected for the following reasons - "
& [Rejected Reason]
DoCmd.SendObject acSendNoObject, , , [LastModifiedBy], , msgSubject,
msgBody
End If
End Sub

:

Hi,

I am trying to automate a process within my database. The process being that
when a Super User rejects a transaction it will generate an email to the user
who last modified the record with details of why the transaction has been
rejected.

Here is the code I have been trying to use.

Private Sub Form_AfterUpdate()

If [AR Manager Approval] = "Rejected" Then

DoCmd.SendObject acSendNoObject, _
To:=[LastModifiedBy], _
Subject:="Your transaction request has been rejected", _
MessageText:="Your request to have" & [CustomerName] &
[Invoice Number] & _
" sent to debt refferal has been rejected for the following
reasons - " & _
[Rejected Reason]

End If

End Sub

I am getting the following run time error when the code tries to execute.
Error 2465 - Bad Debt Tracking can't find the field '|' reffered to in your
expression.

I have checked the code and all looks fine. But I am not familiar with vba
in access.

Can someone please help with this query or maybe suggest an alternative?
Click to expand...
Click to expand...
Click to expand...
 
Yes. That is no problem. However, it needs to be done in code (VBA), not on
accessing a table directly.

If the application is in use on a daily basis, I would add code on the open
event of one of the main forms to check for time until the deadline & send an
e-mail when within the allotted time.

If the application is not in daily use, I would instead write a separate
application and link it to the same data file (if you have not done this yet,
look up "front end" and "back end" on the forums to see how to split the
database into data & program parts. I would have this second application do
one thing: open a form. On the form's timer event, I would tell it to check
every hour or so to see if it is within the desired timeframe and send the
e-mail automatically, then change a value in a Yes/No field in the table
indicating that it has already sent the notification (so that it does not
continue to send notifications every 10 minutes for the next week). Of
course, this means it will have to check the value of the flag field (Yes/No)
before sending the e-mail to see if it has already done so.

One side note, though. When you use the SendObject method in Access, it
tells Outlook to send an e-mail, which triggers Outlook's security feature
that pops up the message asking the user if he wants to approve the sending
of the message by the other program (your Access application). This can be a
problem if the program runs unattended, as there will be no user watching the
screen at 12:10 in the morning when the program wants to send the message.
You can get past this by using something like ClickYes Pro, which is a little
program that runs on the computer, simply waiting for this scenario to occur,
then approves the sending of the message for you.


NikkiNYCC said:
Brian,

Would there be a way of having an email generated and sent out when an
enquiry is nearing its deadline?

I have a field that says [Target Reply Date] and would like to be able to
have a notification of some sort be it an email or the record flashing red
when I open the table, when it is about a week from completion.

I am teaching myself Access at the moment for work as our IT has no formal
training and it keeps surprising me with what it can do.

Thanks

Nikki

Brian said:
YOu are welcome. Did it work?

Enigo said:
Thanks Brian

:

Your statement is missing a couple of arguments, and some of the arguments
are not the right format. Try this:

Private Sub Form_AfterUpdate()
If [AR Manager Approval] = "Rejected" Then
Dim msgSubject As String
Dim msgBody As String
msgSubject = "Your transaction request has been rejected"
msgBody = "Your request to have" & [CustomerName] & [Invoice Number] & _
" sent to debt refferal has been rejected for the following reasons - "
& [Rejected Reason]
DoCmd.SendObject acSendNoObject, , , [LastModifiedBy], , msgSubject,
msgBody
End If
End Sub

:

Hi,

I am trying to automate a process within my database. The process being that
when a Super User rejects a transaction it will generate an email to the user
who last modified the record with details of why the transaction has been
rejected.

Here is the code I have been trying to use.

Private Sub Form_AfterUpdate()

If [AR Manager Approval] = "Rejected" Then

DoCmd.SendObject acSendNoObject, _
To:=[LastModifiedBy], _
Subject:="Your transaction request has been rejected", _
MessageText:="Your request to have" & [CustomerName] &
[Invoice Number] & _
" sent to debt refferal has been rejected for the following
reasons - " & _
[Rejected Reason]

End If

End Sub

I am getting the following run time error when the code tries to execute.
Error 2465 - Bad Debt Tracking can't find the field '|' reffered to in your
expression.

I have checked the code and all looks fine. But I am not familiar with vba
in access.

Can someone please help with this query or maybe suggest an alternative?
Click to expand...
Click to expand...
Click to expand...
 
Just another thought. On a form, you can use Conditional Formatting to change
the color of the text in a box to red when the value of your date control is
within a week of completion. This would be as good as or better than sending
an e-mail if you use the applicaiton on a daily basis.

NikkiNYCC said:
Brian,

Would there be a way of having an email generated and sent out when an
enquiry is nearing its deadline?

I have a field that says [Target Reply Date] and would like to be able to
have a notification of some sort be it an email or the record flashing red
when I open the table, when it is about a week from completion.

I am teaching myself Access at the moment for work as our IT has no formal
training and it keeps surprising me with what it can do.

Thanks

Nikki

Brian said:
YOu are welcome. Did it work?

Enigo said:
Thanks Brian

:

Your statement is missing a couple of arguments, and some of the arguments
are not the right format. Try this:

Private Sub Form_AfterUpdate()
If [AR Manager Approval] = "Rejected" Then
Dim msgSubject As String
Dim msgBody As String
msgSubject = "Your transaction request has been rejected"
msgBody = "Your request to have" & [CustomerName] & [Invoice Number] & _
" sent to debt refferal has been rejected for the following reasons - "
& [Rejected Reason]
DoCmd.SendObject acSendNoObject, , , [LastModifiedBy], , msgSubject,
msgBody
End If
End Sub

:

Hi,

I am trying to automate a process within my database. The process being that
when a Super User rejects a transaction it will generate an email to the user
who last modified the record with details of why the transaction has been
rejected.

Here is the code I have been trying to use.

Private Sub Form_AfterUpdate()

If [AR Manager Approval] = "Rejected" Then

DoCmd.SendObject acSendNoObject, _
To:=[LastModifiedBy], _
Subject:="Your transaction request has been rejected", _
MessageText:="Your request to have" & [CustomerName] &
[Invoice Number] & _
" sent to debt refferal has been rejected for the following
reasons - " & _
[Rejected Reason]

End If

End Sub

I am getting the following run time error when the code tries to execute.
Error 2465 - Bad Debt Tracking can't find the field '|' reffered to in your
expression.

I have checked the code and all looks fine. But I am not familiar with vba
in access.

Can someone please help with this query or maybe suggest an alternative?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top