UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Email Reminders On Particular Date, Access 2016    
 
   
ServiceMSA
post Aug 21 2019, 01:08 PM
Post#1



Posts: 19
Joined: 11-July 19



Hello again,
I am working on a small database to test the functionality of sending email reminders based on user entered dates.
The basic idea of the database is that a sales associate will enter information from leads they have and interactions with the customer.
Currently I have three tables Customers, Contacts, and interactions.
The Interactions table will have a date of the contact to the potential sales lead and a follow up date.
I would like to send out emails based on the follow up dates that contain the pertinent information from the last interaction.
I have looked through the code database, searched the forum but haven't found anything like this.
Does anyone have an example of something similar that might help?

Go to the top of the page
 
theDBguy
post Aug 21 2019, 01:24 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,315
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not sure what you're looking for exactly but I doubt you'll find one that matches your needs 100%. However, each portion of your requirements should be readily available, and all you have to do is put them together. For example, which step do you not know how to do? Do you already know how to generate an email message?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ServiceMSA
post Aug 21 2019, 01:44 PM
Post#3



Posts: 19
Joined: 11-July 19



I have done emails with excel VBA before and they look pretty similar to what you do for Access.
All my previous experience was with sending one autogenerated email from Excel when the user pressed a command button.
So I will need to know how to do the batch part of the process, I am guessing a while loop.
I will also need to know where best to store the code so it initializes correctly.
Go to the top of the page
 
dale.fye
post Aug 21 2019, 02:20 PM
Post#4



Posts: 160
Joined: 28-March 18
From: Virginia


Well, Access has the SendObject method, which allows you to create an outlook email which contains text in the body, and up to one attachment of several types. If you need more than one attachment, then you would need to automate Outlook from Access. But you would be left with the Outlook security messages which you would have to deal with (popup messages for each email). You can work around this with CDO, but I prefer vbMAPI from EverythingAccess. It is not free ($120), but I find it well worth the one time investment, and its ease of use.

Where are you sending these emails, to the sales person or the contact?

You would need to create a recordset which identifies those records which have not already been sent (I would include a field for emailSent - Yes/No in your interactions table). This would select those records where the FollowupDate > Date() - 3 (or maybe 4, to accomodate weekends and holidays when you don't run the code) and where eMailSent = false.

Then you would simply loop through the recordset, construct the To, From, Subject, and email body for whichever method you choose, and then send the email.
Once the email is sent, I would then set the eMailSent value to -1 to show that the email had been sent (or maybe make this a date field and actually enter the date it was sent).

Then move to the next record in the recordset.

HTH
Dale

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
ServiceMSA
post Aug 21 2019, 02:53 PM
Post#5



Posts: 19
Joined: 11-July 19



Hello,
Thank you for the reply.
In response to your question, I would be sending this to the sales member who entered the data as a reminder to contact that customer or lead again.
From my google research it looks like to do this I should probably use an autoexec macro to run a sub.
The subroutine should then perform the following actions:
  1. Use the time method to compare the current time against a preset trigger time.
  2. Compare a date stored for the last time a batch was sent to the current date to prevent multiple batches.
  3. Query the database to pull records for reminders that need to be sent, which would be the subset of records between the last sent date and the current date.
  4. Loop through that subset pulled by the query and send the information out to the appropriate person.
  5. Record the current date into the stored last batch send date so we know where to start next time.


Does this sound about right?
Go to the top of the page
 
ServiceMSA
post Aug 21 2019, 02:53 PM
Post#6



Posts: 19
Joined: 11-July 19



Hello,
Thank you for the reply.
In response to your question, I would be sending this to the sales member who entered the data as a reminder to contact that customer or lead again.
From my google research it looks like to do this I should probably use an autoexec macro to run a sub.
The subroutine should then perform the following actions:
  1. Use the time method to compare the current time against a preset trigger time.
  2. Compare a date stored for the last time a batch was sent to the current date to prevent multiple batches.
  3. Query the database to pull records for reminders that need to be sent, which would be the subset of records between the last sent date and the current date.
  4. Loop through that subset pulled by the query and send the information out to the appropriate person.
  5. Record the current date into the stored last batch send date so we know where to start next time.


Does this sound about right?

Edit: Sorry for the duplicate post I seem to be having a multiple click issue today.
This post has been edited by ServiceMSA: Aug 21 2019, 02:54 PM
Go to the top of the page
 
tina t
post Aug 21 2019, 03:05 PM
Post#7



Posts: 6,069
Joined: 11-November 10
From: SoCal, USA


QUOTE
use an autoexec macro to run a sub

yes, you can do that - but keep in mind that an autoexec macro runs each time the database is opened. it will not run by itself. if you can be sure that the database will be opened in a timely manner every day, or every workday, according to your needs, then no problem. but if you can't depend on that, then you'll need to come up with a process to open the database automatically every day (or every day that applies) for you. Windows Task Scheduler comes to mind, but others may offer better suggestions. And of course, that means the PC must be on, during the timeframe required.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
ServiceMSA
post Aug 21 2019, 03:24 PM
Post#8



Posts: 19
Joined: 11-July 19



Do you not normally leave databases open and running?
I am new to the game so all the best practices aren't exactly beaten into my head yet.
My thought was to basically start a loop that checks every so often if it needs to run the code.
Go to the top of the page
 
MadPiet
post Aug 21 2019, 04:39 PM
Post#9



Posts: 3,296
Joined: 27-February 09



You can leave the database open and then have a form with an OnTimer event, or you can use the built-in Windows Scheduler and call a macro that calls the code you need to run. You'd check for the date being "right" and then if it is, you'd automate the sending of the e-mails and call that code.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th September 2019 - 02:04 PM