asked on

MsAccess Email trigger

I want to trigger an email from MsAccess that will insert a record into the body of an email based on a date difference in days ie 30 days before an expiration date.

The database will always be running so the event has to be trigger by the pc date.

Scott McDaniel (EE MVE )

Do you mean you want to generate emails from an Access database based on some expiration date you have stored in a table?

jtequia

ASKER

Yes 30 days before the expiration date.

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jtequia

ASKER

I put the code in the form see attached , I tested but no email generated, the email address should be picked off an email field on the same table.

Thanks
Capture.JPG

Jeffrey Coachman

...and to pull your "Record" data into the email body, you could try something like this:

DoCmd.SendObject acSendNoObject, , , "someone@someplace.net", , , "MySubject", "Something is expiring:" & vbCrLf & "StudentID: " & rst!StudentID & vbCrLf & "ClassID: " & rst!ClassID

Note that if you want to send the email immediately, you should change the code to:
DoCmd.SendObject acSendNoObject, , , "someone@someplace.net", , , "MySubject", "Something is expiring:" & vbCrLf & "StudentID: " & rst!StudentID & vbCrLf & "ClassID: " & rst!ClassID,false

*But* then you may get the email security popup.
To avoid this, you can use something like this:
http://www.contextmagic.com/express-clickyes/

JeffCoachman

Jeffrey Coachman

<the email address should be picked off an email field on the same table.
>
You did not state this in your original post...

Something like this then:

DoCmd.SendObject acSendNoObject, , , rst!Email, , , "MySubject", "Something is expiring:" & vbCrLf & "StudentID: " & rst!StudentID & vbCrLf & "ClassID: " & rst!ClassID

jtequia

ASKER

Still hasn't triggered an email, I inserted the code in a Form's VB code.

Scott McDaniel (EE MVE )

Any errors? The screenshot you posted highlights the line in RED, which means there is a syntx error.

Are you sure the SELECT statement is returning data? While we'll certainly help you, it's up to YOU to do the brunt of the troubleshooting ...

You may also need to Format the FACDueDate field as well, if it contains the Date and Time:

SELECT * FROM SomeTable WHERE Format(FACDueDate, "mmddyyyy")=#" & Format(DateAdd("d", -30, Now), "mmddyyyy") & "#")

Jeffrey Coachman

LSM, I think the error was from the trailing ")" in your code...

DoCmd.SendObject acSendNoObject, ,"",,"someone@someplace.net",,,"MySubject", "Something is expiring")
sb
DoCmd.SendObject acSendNoObject, ,"",,"someone@someplace.net",,,"MySubject", "Something is expiring"

Jeffrey Coachman

...Then LSM's post is actually the solution here...
...my post was just addressed the minor syntax issue...

I have requested that this be changed to accept LSM's post

;-)

JeffCoachman

jtequia

ASKER

Yes, I meant to give LSM the credit, please change and credit post ID: 37884180