Scott said:
OK this is a big question. Anyone know how to set up access so that it will
automatically send out a predrafted email each month based on criteria
related to the date
Example
Drivers License expires and want to send a batch email to everyone in my
company that here drivers license expires that particular mont and with a 30
day notice.
I don't know about using Access to do it - it would be a little clunky. I've
solved a similar problem with a small VB app. I set the Task Scheduler to
run the VB app every night @ 2:00 AM. It uses the CDONTS.NewMail object to
send the eMail(s). Even if the data is in an Access database I think this
would be easier. Here's a (very) limited snippet:
Dim oMail As CDONTS.NewMail
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim strSQL As String
Dim strCustomerName As String
Dim strMessageBody As String
Dim strDate As String
oConn.Open "driver={SQL Server};server=10.1.1.254;" & _
"uid=sa;pwd=nottachance;database=MyDb"
strSQL = "SELECT * FROM tblCustomer" _
& " WHERE ExpirationDate='" & Date + 30
oRS.Open strSQL, oConn, adOpenStatic, adLockReadOnly
Do Until oRS.EOF
strCustomerName = oRS("CustomerName")
Set oMail = New CDONTS.NewMail
oMail.From = "(e-mail address removed)"
oMail.To = oRS("DefaultEMail")
oMail.Body = txtMessage.Text
oMail.BodyFormat = CdoBodyFormatText
oMail.Subject = strCustomerName & " Driver's License is expiring!"
oMail.Importance = CdoHigh
oMail.Send
Set oMail = Nothing
oRS.MoveNext
Loop
oRS.Close
oConn.Close
Set oRS = Nothing
Set oConn = Nothing
