Automatic Send Email Notification (1 Viewer)

[Sunny Boy 1318]

Hi Everyone,

To make it simple, I will be using a simple example. I have a Table with PersonName and DOB. I want access to send me email notification one day before someone's birthday. I have set up the query accordingly.

I have a problem with my code and saying something about 'Syntax Error' - My error lies here and I am not sure on how to move on.

Here is the full code: Can someone kindly help? Thank You!

Public Function fDOBNotices() As Boolean

Dim db As Database, rs As Recordset
Dim strTo As String, strSubject As String, strBody As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("DOBNotification")

strTo = "sunramkissoon@apolloblake.com"
strSubject = "Birthday Notification"
rs.MoveFirst
Do While Not rs.EOF
strBody = "This is to inform you that " & rs.Fields("PersonName") & "will be celebrating their birthday tomorrow!"
DoCmd.SendObject(, , , strTo, , , strSubject, strBody)
rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

End Function

 

[pr2-eugin]

Hello Sunny Boy 1318, Welcome to AWF..

Two things I noted..

1. You have used opening and closing brackets for the SendObject method.. That might be the problem.. Try this..

DoCmd.SendObject To:= strTo, Subject:= strSubject, MessageText:= strBody

2. You have used rs.MoveFirst.. This is what Allen Browne says..

Using any of the Move methods (MoveFirst, MoveLast, MoveNext, or MovePrevious) causes an error if the recordset has no records.
Solution:

Test before using any of the Move methods. Either of these approaches works:

If Not (rs.BOF And rs.EOF) Then 'There are no records if Beginning-Of-File and End-Of-File are both true.
If rs.RecordCount <> 0 Then '100% reliable in DAO, but some ADO recordsets return -1 as the count.

Just Curious, say if 50 individuals are to celebrate B'day tomorrow.. Would you want to send 50 individual emails to the same person? Or just one email listed with all 50 names?

 

[Sunny Boy 1318]

Hey Paul, Thanks for pointing out these two. It actually working now.

I also needed to add a no to that the email goes automatically... Proper Code for this will be:

DoCmd.SendObject acSendNoObject, , , strTo, , , strSubject, strBody, no

You're Right Paul - That will be a Good Question and my Answer would be that I would really want to send all the 50 Names in just one email. I really have not think of that until you pointed this. Using the above code, i will certainly need to loop one by one which can be a hassel. How can I move by sending all 50 names in One Single Email...


Regards,

Sunny Boy

 

[pr2-eugin]

How about this..

Public Function fDOBNotices() As Boolean
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim strTo As String, strSubject As String, strBody As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("DOBNotification")

    strTo = "sunramkissoon@apolloblake.com"
    strSubject = "Birthday Notification"
    strBody = "This is to inform you that the following people will be celebrating their birthday tomorrow!" & vbNewLine
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            strBody = strBody & rs.Fields("PersonName") & vbNewLine
            rs.MoveNext
        Loop
        DoCmd.SendObject To:= strTo, Subject:= strSubject, MessageText:= strBody, EditMessage:=False
    End If
    Set rs = Nothing
    Set db = Nothing
End Function

 

[Sunny Boy 1318]

Great Paul - This works as well

More one Question came to my mind - I have set a AutoExec Macro to execute this and send the email. My Database will be split into Back-End and Front-End. My Front-End will always remain open.

Will i still receive the email? Or does someone need to close and open the Database everyday?

 

[NigelShaw]

Hi,

if it was me, i'd make the function more flexible. you shouldn't hard code email addresses and notifications-

Public Function fDOBNotices(strTo As String, strSubject As String, strBody As String) As Boolean
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim strTo As String, strSubject As String, strBody As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("DOBNotification")
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            strBody = strBody & rs.Fields("PersonName") & vbNewLine
            rs.MoveNext
        Loop
        DoCmd.SendObject To:= strTo, Subject:= strSubject, MessageText:= strBody, EditMessage:=False
    End If
    Set rs = Nothing
    Set db = Nothing
End Function

Then call it like this-

[COLOR="SeaGreen"]'use a recordset call to get data from tables[/COLOR]
[COLOR="SeaGreen"]'my example is hard coded but write something like strTo = DLookup(etc etc)[/COLOR]
strTo = "sunramkissoon@apolloblake.com"
strSubject = "Birthday Notification"
strBody = "This is to inform you that the following people will be celebrating their birthday tomorrow!"

Call fDOBNotices(strTo, strSubject, strBody)

this way, you can use the function many times with different data as / when needed thus reducing the amount specific routines you would need.


HTH


Nigel

 

[Sunny Boy 1318]

Thank you Nigel,

What if I want the email body to be several lines. Ex.

Hi Managers,

I would like to inform you that the following people will be celebrating their Birthday Tomorrow:

Name1
Name2

The above is my first question and second one is below...

Let's say, I want to add their Age/ Dept next to their Name. How to do that like below:

Name1, Dept3
Name2, Dept4

Any Idea?

 

[pr2-eugin]

I have set a AutoExec Macro to execute this and send the email. My Database will be split into Back-End and Front-End. My Front-End will always remain open.

Will i still receive the email? Or does someone need to close and open the Database everyday?

AutoExec macro will be executed only once.. So what you can do is open a (hidden) Form using the AutoExec macro.. Does not need any control on it what so ever, just a blank form.. We need is just the Form Timer event.. Using the properties of the blank Form set the TimerInterval to be how long you need it to be.. It takes in long values, If TimerInterval is 1000 it is 1 second.. That is it will run ever one second.. but we do not need this.. We will run this say every hour maybe?? As it just needs to send email at some point.. Then in the Form timer event.. Check the time and send email..

Private Sub Form_Timer()
    If TimeValue(Now) > #16:30:00# Then
        Call fDOBNotices
        DoCmd.Close acForm, Me.Name
    End If
End Sub

I have set the form to be closed after the mail is sent, but you can also create a log table whihc will indicate if the mail has been sent out..

What if I want the email body to be several lines. Ex.

In the code, the vbNewLine will force it to go to the Next line.. If you wish to include the Age and Dept, just add them to the Query DOBNotification.. and add them to the body.. something like..

Public Function fDOBNotices() As Boolean
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim strTo As String, strSubject As String, strBody As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("DOBNotification")

    strTo = "sunramkissoon@apolloblake.com"
    strSubject = "Birthday Notification"
    strBody = "This is to inform you that the following people will be celebrating their birthday tomorrow!" & vbNewLine & vbNewLine
    If rs.RecordCount <> 0 Then
        Do While Not rs.EOF
            strBody = strBody & rs.Fields("PersonName") & ", " & rs.Fields("Department") & vbNewLine
            rs.MoveNext
        Loop
        DoCmd.SendObject To:= strTo, Subject:= strSubject, MessageText:= strBody, EditMessage:=False
    End If
    Set rs = Nothing
    Set db = Nothing
End Function

I have expanded my answer, but by all means use Nigel's method.. i.e. Passing email address instead of hardcoding them..

 

[Sunny Boy 1318]

Hi Nigel,

I am just trying this to do it your way. I wrote the module and everything is fine. Can you guide me how to call for this? Do i need to use AutoExec here?

 

[NigelShaw]

Hi

Auto exec is just for running routines when the application opens. What isn't working?

Thanks

 

[pr2-eugin]

Hello Nigel, Sunny has opened up a new thread.. I think he has it resolved.. but not sure..

 

[Sunny Boy 1318]

Thanks Nigel - Issue Resolved