Daily Sending of Email Notifications

[Sunny Boy 1318]

Hi,

I have set up a query to send birthday email notifications one day before the occurrence. Everything is good with this. I have set the AutoExec to send the email and it is working fine.

My problem is that :banghead: AutoExec works only when you open the Database. My Database always stays open. Does this mean that everyday i will need to close and open my database??? I also want to send the email at a specific time during the day???

Any help for these 2 issues???

Thanks,

Sunny

 

[pr2-eugin]

Hello Sunny Boy 1318, I think I answered that query in Post #8.. in the previous thread.. "Automatically Send Email Notification", quiet clearly.. I might have been wrong.. Did it not make much sense?

 

[Sunny Boy 1318]

Hi Paul,

Not, whatever you mentioned was very helpful but I am not able to use the time interval to send the report.

Like i said, my database is always open and i want the email to be sent at a given time without having someone to open and close the database everyday. I am sorry but this part I was not able to grasp it correctly? Can you go again please?

 

[pr2-eugin]

Okay I am not sure if you have read through the entire post.. I did mention.

So what you can do is open a (hidden) Form using the AutoExec macro.. ........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..

I have put up a small sample, look over it.. The form will be opened by the AutoExec Macro.. Then it will never be closed.. So the form is set to be triggered every 10 seconds, you can change it later.. It will send an email after 12:30:00 PM.. I have also created the log table..

Check the sample, if you do not follow something post back..

 

[Sunny Boy 1318]

Thank you Paul.

I know where my mistake was and everything is set now. Thank you so much. It certainly helped me a lot.

Just a concern now - Like I said, my database is split into back-end and front-end. They are both saved in the sharedrive. I am always connected to the server and my front-end is always open. Now, if another user opens the database from the server, do you think that the AutoExec is start running again and email will be sent again???

Thank you for your input Sir!

Regards,

Sunny

 

[pr2-eugin]

Yes and No..

Yes - If in case both timer events are triggered at the same time..
No - If you have some conditions to check along with the Time and Log entry.. Maybe allow only few people to send email? Or set the time constraint even narrow?

 

[Sunny Boy 1318]

Hey Everyone,

One more Question!!!

What if I need to send another email like this but containing information different than that of DOB - Let's say, I want access to also send email notifications regarding someone completing their One Year within the Organization?

Do I need to create another module and hidden form for this or can I use the same form.

How can I go about adding another work here???

Regards,

Sunny

 

[pr2-eugin]

Same form, but another Query or add another criteria to the same Query..

 

[Sunny Boy 1318]

So means that same module should be used??? If so, my codes end like this...

Loop
DoCmd.SendObject To:=strTo, Subject:=strSubject, MessageText:=strBody, EditMessage:=False
End If
Set rs = Nothing
Set db = Nothing
End Function

How to go around setting up another code in the same module?

 

[pr2-eugin]

You have to create another recordset..

Public Function fDOBNotices() As Boolean
    Dim db As DAO.Database, rs As DAO.Recordset,[COLOR=Blue] rs2 As DAO.Recordset[/COLOR]
    Dim strTo As String, strSubject As String, strBody As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("DOBNotification")
    Set rs2 = db.OpenRecordset("NewQueryName")
    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
    
[COLOR=Blue]    strTo = "sunramkissoon@apolloblake.com"
    strSubject = "One Year Notification"
    strBody = "This is to inform you that the following people will be completing their One year service!" & vbNewLine & vbNewLine
    If rs2.RecordCount <> 0 Then
        Do While Not rs2.EOF
            strBody = strBody & rs.Fields("[COLOR=Red]someOtherField[/COLOR]") & ", " & rs.Fields("[COLOR=Red]anotherFieldMaybe[/COLOR]") & vbNewLine
            rs2.MoveNext
        Loop
        DoCmd.SendObject To:= strTo, Subject:= strSubject, MessageText:= strBody, EditMessage:=False
    End If
    Set rs2 = Nothing[/COLOR]
    Set db = Nothing
End Function

 

[Sunny Boy 1318]

Thank you Paul - This works perfectly fine. I think you omitted some fields in the code just for testing - LOL But i was able to figure out....

When writing the code for the second recordset, it should be rs2 - But i got it right - Thanks a Lot

 

[pr2-eugin]

Ha ha.. I just looked at it again.. I did, do that mistake.. How silly.. Well I just copied over the same code from above.. Now you know that how simple it could be..

Good you have it sorted mate..

PS: I have edited my code..

 

[Sunny Boy 1318]

Hi Paul,

My Code ends like this. I want to add a new line after access displays all the names of the people celebrating their birthdays. I tried add a new line using some vbNewLine codes but nothing happens!!! How can the code be twisted so that I can add an ending phrase into the email...

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

 

[pr2-eugin]

What do you mean nothing happens? Also the code you posted seems to be in one single line, can you edit the code again?

 

[Sunny Boy 1318]

Here you are .... I want to add a line with some text after it lists the name & department of the people...



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

 

[pr2-eugin]

How about,

strTo = "sunramkissoon@apolloblake.com"
strSubject = "Birthday Notification"
strBody = "Hi Everyone," & vbNewLine & vbNewLine & "This is to inform you that the following people will be celebrating their birthday tomorrow!" & vbNewLine & vbNewLine & vbNewLine
If rs.RecordCount <> 0 Then
    Do While Not rs.EOF
        strBody = strBody & rs.Fields("FullName") & " From " & rs.Fields("Department") & vbNewLine
        rs.MoveNext
    Loop
    [B]strBody = strBody [COLOR=Red]& vbNewLine & "So here is the last line I wish to add."[/COLOR][/B]
    DoCmd.SendObject To:=strTo, Subject:=strSubject, MessageText:=strBody, EditMessage:=False
End If
Set rs = Nothing

 

[Sunny Boy 1318]

Works fine now... Thank you!