Send automatic Email every day 1

[TomTiddle]

Hi
I need to send a daily email with a file attached (pipe delimted.
Can I automate this so it sends without any input until I stop it?

 

[philcon]

Hi Tom,

You could have a form hiding in the background, and within it use the ontimer event to trigger your e-mail. if you need the coding to send the e-mail, let me know.

Obviously the application would have to be on continuously on the pc sending the e-mails.

Regards


Phil

 

[TomTiddle]

Phil
if you could let me have the code that would brilliant
Thanks

 

[Dalain]

The form does not nessarly need to be hidden, as long as it cannot be closed without closing access.

On the forms Timer interval Event enter 86400000.
(1000 = 1 sec therfore 24hr = 86400000) then on the OnTimer event of the form

You should also be able to set the time to send the email
For example to set the time enter a time like "03:00 AM". In a field called TimeToSendEmail enter the time then on the Lost focus event, enter the code.

Private Sub TimeToSendEmail_LostFocus()
Dim lngTimeNow, lngNewTime, lngTimerInterval As Long

lngTimeNow = fTimeOfDay(Now())
lngNewTime = fTimeOfDay(TimeValue(me.TimeToSendEmail))

'86400000 - lngTimeNow = Time left to finish the day + lngNewTime
me.lngTimerInterval = 86400000 - lngTimeNow + lngNewTime

End Sub

This is the function used to calulate the number of seconds for a set time.
Function fTimeOfDay(datTime As Date)
Dim lngHour, lngMin, lngSec As Long

'calculate number of seconds for a set time of day
lngHour = Hour(datTime) * 60 * 60
lngMin = Minute(datTime) * 60
lngSec = Second(datTime)
fTimeOfDay = (lngSec + lngMin + lngHour) * 1000

End Function


On the forms Timer interval Event enter 86400000.
(1000 = 1 sec therfore 24hr = 86400000) then on the OnTimer event of the form

To send the Email enter

Private Sub Form_Timer()
' Send Email DoCmd.SendObject , "", "", "To", "Cc", "Bcc", "Subject", "MessageText", False, ""
me.timerinterval = 86400000

End Sub

hope this helps
Dalain

 

[jkirkland]

You can also go to the Windows Task scheduler and create a task which will open your Access database at whatever times you wish.

The trick is that your database must have an autoexec macro that uses the "SendObject" command. The database will open when Windows triggers it and the autoexec macro will activate immediately. Remember to put an "Exit" command in the macro to close the db after the emails have been sent.

If users are going to be opening this database then this autoexec macro might be annoying. You can always create a new Access database though and import the report or form that you are emailing (along with linking the necessary tables and importing the underlying queries)

Sorry for the length of the reply.

 

[TomTiddle]

thanks Guys I pretty new to this, I will try this on Mon and let you know how I get on
Cheers

 

[ramonzulueta]

Good day. I am searching the internet and found your write up about sending email using access. I just would like to ask if you could help me. My problem is,we have an Access database of our clients. It has fields for email addresses and date of expiry of their rents. I would like to write a webbased application (using asp, access ,iis, winnt) that would email our clients automatically according to the dates in the database fields, giving notice that their rent is about to expire like for example, within one month. I just would like to ask if you could somehow give me some idea or point me to the right direction that will give me insights. Thanks very much indeed and hoping for your kind response.

 

[philcon]

Ramon,

I wouldn't have thought that your application needed to be web-based if all you wish to do is send out e-mails at a given event.

Utilising the on timer event(as above), you could run a query everyday, which identified those tenants whose rent was about to expire. Then utiilsing the fields within that query you could send personalised e-mails out.

 

[shwin]

Hi guys,

I have a very similar question to TomTiddle's. My problem is that the tables are linked to a server and I don't want people recieving emails from everyone who opens the application. I was thinking that when the email is sent it would take note of it and not send it again. My idea was to have it check a check box when it is sent and only check boxes without a check in them will be avaiable for sending. Apparently this is easier said than done. If you could advise me as to whether this makes any sense and also if it the right thing to be trying I would be grateful.

Thanks in advance for your help!

 

[AccessGuruCarl]

shwin - Yes your on the right track...

You need to create a query that selects records where checkbox = false

Run the code to email the record...

Then run an update query to update the checkbox to true where email = email just sent.

Let me know if you need a code sample...



AccessGuruCarl

 

[AccessGuruCarl]

I forgot to mention... You have to disable security settings in your email app, To send automatically. At least with Outlook & Outlook Express. Otherwise you have to click the warning message to allow email to send...

To disable settings in Outlook Express...
Open Outlook Express goto
Tools
Options
Security Tab

Un-check - Warn me when other applications try to send mail as me.

Settings should be "close to the above" for Outlook.

Now you won't get the warning to allow the email out when you run your code...

Not sure if these settings are required for different email programs, or if you run the code on a mail server, you'll have to try different senerio's... If this is the case, Or maybe someone from the forum has some better input on this.

Hope this helps...

AccessGuruCarl

 

[shwin]

Thanks for the help AccessGuruCarl!

If you can provide a code sample it would be great!

Thanks!

 

[AccessGuruCarl]

I'll give you a good example Sunday...
I'm going out of town for the weekend, leaving shortly..

Here is the basics if you want to try

Dim db as Database
Dim rst as Recordset
Dim strSQL as String
dim strUpdate as SQL
Dim intCount as Integer
Dim X
  strSQL = "Select txtEmailAddress, booSent From tblEmp Where booSent = False" 

  Set db = Currentdb
  Set rst = db.CreateQueryDef("Temp",strSQL)

Docmd.OpenQuery("Temp"
For intCount = 1 to rst.RecordCount
 ' The mail Code
 ' Create an update Qry,then view SQL, Copy & Paste
 ' for correct syntax...
  strSQL = Update booSent Where txtEmailAddress = rst.txtEmailAddress

DoCmd.RunCode(strSQL)
Next intCount
rst.Close
[End Code]

This should get you started if you want to work over the weekend. I'll post a better sample when I return

Good Luck...

AccessGuruCarl

 

[AccessGuruCarl]

Forgot to mention..

before the DoCmd.Runcode

Put. DoCmd.SetWarnings False

Then DoCmd.RunCode(strSQL)

DoCmd.DeleteObject acQuery,("Temp"

DoCmd.SetWarnings True

Otherwise you will recieve a msg everytime it runs, plus an error because qry already exists.

Have Fun...

AccessGuruCarl

 

[jbento]

This is a very good thread!!!

I will definitely keep this one in my favorites.

Thanks,

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!