automatic email notification

[ecp85]

I have a form where I give the user 30 days free membership, after the 30 days I would like an email sent to the user and myself telling them their 30 days is up...

Is there a way to have an email sent automatically at the end of the 30 days?

Thanks for any help on this.

 

[mission2java_78]

Create a table tblAppUsage
3 fields
ID (Autonumber)
DateRegistered (date)
DateExpired (date)

Create an autoexec macro used to open when the db opens. Call this function in the autoexec macro

public Function AppStillGood()

Dim db as Database
Dim rs as Recordset
Dim dtTemp as Date

Set db = Currentdb
Set rs = db.OpenRecordset("tblAppUsage")

if rs.bof then  'first time user
  rs.addnew
  rs("DateRegistered").Value = Now
  rs.Update
else
  rs.MoveFirst
  dtTemp = DateAdd(rs("DateRegistered").Value,30)  'add 30 days to registered date
  if (dtTemp < Now()) then
       'expired user
       rs.edit
       rs("DateExpired").Value = Now
       rs.Update
       'send email
  else
       'do nothing..user still has time
  end if
end if

set rs=nothing
set db=nothing

end Function

Jon

 

[ecp85]

Thanks for your response Jon.

Unfortunately my Access skills are pretty limited.

Currently the date field recording the start date is part of my Contacts Table.

Do I need to create a query table that merges the table you suggested and my contact table?

Could I then pull the email field from the contact table as well (where the email gets automatically sent)?

Also, where do I get the body (text) of the email from?

This might be too advanced for me, but I appreciate the help.

Thanks.

Eric

 

[mission2java_78]

Not sure why you have your contacts and the date they started to use the app in different tables, since this is generally a one to one correspondence. As long as the Contacts table has a key back to your original table...then I'd say yes you're going to need to join the tables together, to include all records from the contacts table.

Once you join these by your ID you can pull of the e-mail that you desire.

Than use the code I've posted.

If you need help sending the email, than post back.

Jon

 

[hi there]

hi guys,

i was just browsing through and stumbled upon this thread. this is a very interesting thread. i wondering if the logging code specified below can be used to make the dbase non-functional. so after the expiration date has come the user will not be able to open the dbase anymore. could someone also elaborate on how to send the email. thanks in advance for any comments or suggestions.

 

[mission2java_78]

hi guys,

i was just browsing through and stumbled upon this thread. this is a very interesting thread. i wondering if the logging code specified below can be used to make the dbase non-functional. so after the expiration date has come the user will not be able to open the dbase anymore. could someone also elaborate on how to send the email. thanks in advance for any comments or suggestions.

Yes if the expiration has hit use the

exit sub

or better yet issue a complete close of the app

DoCmd.Quit

Make sure you release allocated memory before closing by setting your objects to Nothing.

As for email I've written how to use the outlook object model on over 100 threads. Do a search for sending email using outlook or just email.

Jon

 

[mission2java_78]

hi guys,

i was just browsing through and stumbled upon this thread. this is a very interesting thread. i wondering if the logging code specified below can be used to make the dbase non-functional. so after the expiration date has come the user will not be able to open the dbase anymore. could someone also elaborate on how to send the email. thanks in advance for any comments or suggestions.

Note one change i just made is to change <= to <. Since they should be able to use it on that final day. Otherwise you're shortening em a day.

Jon

 

[hi there]

hi jon,

thanks for the reply. just after responding to this thread i found all those setting up email threads your're talking about. so thanks for info. so if i add the DoCmd.Quit in place of the code to send emails, what will happen the next time the user tries to open the dbase after the expiration date has occurred. will it start to open the application and then the sub routine will kick in an close it? is there a way i can add a little MsgBox to display something like "your trial period is over" and give the user a second or two to read this before the application closes.

thanks for all your help.

 

[mission2java_78]

hi jon,

thanks for the reply. just after responding to this thread i found all those setting up email threads your're talking about. so thanks for info. so if i add the DoCmd.Quit in place of the code to send emails, what will happen the next time the user tries to open the dbase after the expiration date has occurred. will it start to open the application and then the sub routine will kick in an close it? is there a way i can add a little MsgBox to display something like "your trial period is over" and give the user a second or two to read this before the application closes.

thanks for all your help.

Yes it will kick them out
Yes add a message box before DoCmd.QUit

MsgBox "You're expired"
DoCmd.Quit

Jon

 

[hi there]

thanks jon.