How to send an automatic birthday warning by Outlook 2007 e-mail?

[Guy Choquette]

Hi,
I am new to DB and I have to use Access 2007, at the company, we have a database with a table that has employee's information such as DOB, I would like to send an automatic e-mail to their supervisor 3 to 5 days before to let the supervisor send a Birthday card.
How can I do that?? I think it can be done automatically using auto macro or similar.
Can you help me?? Please do not forget I am fairly new to DB. I know VBA for Excel and Excel very well, but this has to work with Access.
Thank you in advance

[TheSmileyCoder]

Hey, and welcome to Bytes

Im sure there are several ways to do this, but the approach that springs to mind for me, is to have a simple form with a timer attached (Timer adjusted to run every 24 hours). The timer in an access form is basicly a trigger for running code.

You can then attach code to the timer event, which checks a table to see if any birthdays will occur 5 days from now, and then send an email. I haven't really worked with sending emails from access, but there are several good posts about that specific part.

I or someone else can probably detail some of the steps more, if you need.

[NeoPa]

As the question is unfortunately so broad in scope, I suggest we deal here only with the concept of triggering the process, which will deal with sending a message, probably via email, to the relevant supervisor. Sending an email you can ask about separately (or search for solutions as there are already so many of them available on this site alone).

Smiley's suggestion is workable, but does rely on the database to be running somewhere all/most of the time. An alternative is for a database to run a check on startup, of a query that displays all birthdays approaching within the next three days. If any are found then the process can be triggered and fire off whatever to all relevant supervisors. This approach would rely on the database being run at least once daily.

[Rabbit]

I've done automation of Outlook before and there's something you need to be aware of. If you try to access outlook to send e-mail, it will prompt you, you must wait 5 seconds before you can click the prompt, and it will ask you how long you want to give access to outlook.

If that is not acceptable, you will have to use some other third-party .dll to get it done.

[Guy Choquette]

Thank you all. I like your ideas, but as I am not an expert, I think the first step would be to have that query to display all birthdays, but once i have the query with the Birth Date, How do I get it to let me know of all the birthdays coming in the next 5 days??? Once I have that list, I am sure I could display it first and then I will work on sending the mail.
Thank you for helping me, all your ideas are good. I appreciate.

[Rabbit]

Use the WHERE condition and add a date condition where the date has to be between today and today plus 5 days from now. You can use the DateAdd function to add the 5 days.

[NeoPa]

... and you can use the Weekday() function to determine whether or not a weekend will fall within that periods in order to vary the number of days forward you need to look (so 3 or 5 becomes x, which is determined by checking Weekday().). Personally, I would filter the list by Date() + 2 to x, rather than simply Date() (==> today), otherwise you'll get repetitions across the period, which may prove a nuisance.

[Guy Choquette]

Thanks you to all, I am progressing, I made a query with the employee table, then I have put this expression in the next empty field:

expr1: DateSerial(Year(Date())+IIf(Month([DOB])*100+Day([DOB])<=Month(Date())*100+Day(Date()),1,0),Month([DOB]),Day([DOB]))

that I took on a forum, and it gives me the Anniversary date.
Now I have put this criteria and it gives me what I want: Date() +3, I will be able to add in one of the column the supervisor e-mail, to be able to send from there a warning e-mail...
Should I be able to create this query in a macro that would start as soon as I open the DB, if yes, then I should be able to also add the code for the outlook e-mail, should I???? Some one has an idea of the code?? and where should I put that macro
Thank you again to ALL of you.

[NeoPa]

Guy, I touched on this earlier, but you seem to have allowed the message to go by unheeded :
You are not allowed to ask multiple questions in the same thread. Each thread should have a single topic.

Details about how to email something from Access, and how to get code to run automatically, are for other threads. Both topics have already been covered exhaustively already, so you needn't, unless you choose to take that course, ask a separate question on either of these topics, or put yourself in the position of having to wait for replies. These topics certainly cannot be covered in this thread though.

I can answer the latter question as far as saying that it is certainly possible, if that helps any. The details of the topics though, are for other threads than this.

These threads may help. Otherwise see below for the search keywords I used.
Problem with e-mailing from within Access
Invoking a Database From the Command Line

I searched through bytes using keywords of smtp & vba and separately on autostart vba.

[Guy Choquette]

Thank you for the help you gave me.
SInce it is not the right place, I will search some where else.

[NeoPa]

Guy, I'm not suggesting you cannot ask about these matters in threads you post yourself. Simply that they should each be in their own thread if you do, and that it will probably be a slower way of getting the information you need than searching through the existing threads. If it is your choice to post new threads on these questions though, please go ahead and do so. We have no problems with that.