Automatically send weekly email with report (1 Viewer)

[dfsabrown]

Hi

I have a MS Access 2010 database with various forms, reports etc.

I would like an email to be sent once a week which attaches an existing report ("Actions - outstanding"). I don't want it to happen on with a command button, but just automatically, although i think the database will have to be opened to prompt this (in theory the database should be opened several times a day by various different users.)

anyone have any ideas on the code & where i should insert it?

thanks

Sarah

 

[dfsabrown]

so far i have managed to raise the email when the database is opened:

Docmd.Sendobject acSendReport, "Actions - outstanding", acFormatPDF, Nz(DLookup("[Lead Investigator email]", "[Case Details]")), Nz(DLookup("[case email address]", "[case details]")), "Outstanding Actions", "Please find attached report of outstanding actions."
Exit Sub
End Sub

i just need help to make sure it is only sent once a week or once a day and not every time the database is opened, especially as i have multiple users going into the database during each day.

i have found this, which i think fixes it to a monday:

Dim MyDate As date
Dim MyWeekday As interger
Mydate= date
MyWeekday = Weekday (Mydate, vb Monday)
If myWeekday=vbmonday then

but how can i make sure it only happens once on a monday?

thanks

 

[DavidAtWork]

You could write a function that is run at start up (within an autoexec macro, use RunCode) and in the function it checks what day of the week it is using Weekday(Date) and if it is the day of the week you want to send the email, execute your email code with your report as an attachment

David

 

[DavidAtWork]

Sorry just seen your extra code, to ensure you only send once, you could use a log table to record the email send and in your code you can check the log table to see if there is already an entry for the current date

David

 

[dfsabrown]

i'm having a few problems with the code, this is my current code:

Private Sub Form_Open()

Dim MyDate As Date
Dim MyWeekday As interger
MyDate = Date
MyWeekday = Weekday(MyDate, vbMonday)

If MyWeekday = vbMonday Then

DoCmd.SendObject acSendReport, "Actions - outstanding", acFormatPDF, Nz(DLookup("[Lead Investigator email]", "[Case Details]")), Nz(DLookup("[case email address]", "[case details]")), , Nz(DLookup("[Case name]", "[Case Details]")), "Please find attached report of outstanding actions.", false

Else

End If

Exit Sub
End Sub


These are my issues:

- the email does not send automatically (even though i have out false at the end)
- the email is generated every time i open the database, even though it is not on a monday

i also need help creating the log table and getting the code to check the table for current date.

thanks!

 

[DavidAtWork]

First of all create a log table, name it tbl_emailLog, have logID as an auto id field and a date field 'sentDate' so when the email is sent you can record the send

I don't think your check for Monday is working correctly so I'd use
If Weekday(Date) = 2 Then '2 is the returned value for Monday

If DCount("*","tbl_emailLog","[sentDate] = #" & Date() & "#") > 0 Then
'do nothing
Else
'send email
Docmd.SendObject
Docmd.RunSQL("INSERT INTO tbl_emailLog(sentDate) VALUES(' " + Format(Date()) + " ')")
End If
End If


Your send object line appears to have the right number of arguments, but I think using Nz in your DLookup you need to specify a default "" if the DLookup fails, so the line:
Nz(DLookup("[case email address]", "[case details]")), should become
Nz(DLookup("[case email address]", "[case details]"),""),

there is also no criteria in your DLookup which is fine if [case details] is a query with in-built criteria or a table with only one record in it, but if [case details] is a table with many records, you'll need to set criteria to return the correct values.

David

 

[dfsabrown]

David, thank you so much for your help.

My code is now below with the email log table also created.

I have it on the "load event" of my front form, but i am getting a "Run-time error 94 Invalid use of Null" with the "If Weekday(Date) = 2 Then" line being highlighted.
--------------------------------------------

Private Sub Form_Load()

If Weekday(Date) = 2 Then

If DCount("*", "tbl_emailLog", "[sentDate] = #" & Date & "#") > 0 Then
'do nothing
Else
'send email

DoCmd.SendObject acSendReport, "Actions - outstanding", acFormatPDF, Nz(DLookup("[Lead Investigator email]", "[Case Details]"), ""), Nz(DLookup("[case email address]", "[case details]"), ""), , Nz(DLookup("[Case name]", "[Case Details]")), "Please find attached report of outstanding actions."

DoCmd.RunSQL ("INSERT INTO tbl_emailLog(sentDate) VALUES(' " + Format(Date) + " ')")
End If
End If
Exit Sub
End Sub
--------------------------------------------------------

 

[DavidAtWork]

I can't see a problem with the line you're referring to, I just tested it and it worked fine. I did notice you're still missing a ,"" from the third Nz expression
You could also move the Exit Sub up to the line where I have 'do nothing putting it in front of 'do nothing.
If the error persists, I'd suggest copying the code to Notepad and then remove everything just leave
If Weekday(Date) = 2 Then: msgbox "This works ok",vbokonly

make sure you change weekday value to 3 as today is Tuesday

David

 

[pr2-eugin]

The only problem I could see is the Format in the RunSQL has no Format.. So I have changed the code as..

Private Sub Form_Load()
    If Weekday(Date) = 2 Then
        If DCount("*", "tbl_emailLog", "[sentDate] = " & [COLOR=Red][B]Format(Date, "\#mm\/dd\/yyyy\#")[/B][/COLOR] & "")[COLOR=Red][B] = 0[/B][/COLOR] Then
[COLOR=Green]            'send email[/COLOR]
            DoCmd.SendObject acSendReport, "Actions - outstanding", acFormatPDF, Nz(DLookup("[Lead Investigator email]", "[Case Details]"), ""), Nz(DLookup("[case email address]", "[case details]"), ""), , Nz(DLookup("[Case name]", "[Case Details]")), "Please find attached report of outstanding actions."
            DoCmd.RunSQL ("INSERT INTO tbl_emailLog(sentDate) VALUES(" & [B][COLOR=Red]Format(Date, "\#mm\/dd\/yyyy\#")[/COLOR][/B] & ")")
        End If
    End If
End Sub

 

[DavidAtWork]

@pr2
VALUES(' " + Format(myDate) + " ')" does work without a format definition when you have a date variable, however in this instance it may need to be

VALUES(' " + Format(Date()) + " ')"

David

 

[dfsabrown]

David, think i am getting there!

this is now the code:

------------------------------------------------------
Private Sub Form_Load()


If weekday(Now) = vbTuesday Then

If DCount("*", "tbl_emailLog", "[sentDate]=#" & Date & "#") > 0 Then

Exit Sub
'do nothing
Else
'send email

DoCmd.SendObject acSendReport, "Actions - outstanding", acFormatPDF, Nz(DLookup("[Lead Investigator email]", "[Case Details]"), ""), Nz(DLookup("[case email address]", "[case details]"), ""), , Nz(DLookup("[Case name]", "[Case Details]"), ""), "Please find attached report of outstanding actions."

DoCmd.RunSQL ("INSERT INTO tbl_emailLog(sentDate) VALUES('" + Format(Date) + "')")


End If
End If

End Sub
--------------------------------------------------

the weekday issue seems to work but now get different error message:

Run-time error 3075
Syntax error in date in query expression '[sentDate]=#'.

with the following line highlighted:

If DCount("*", "tbl_emailLog", "[sentDate]=#" & Date & "#") > 0 Then


thanks again

 

[pr2-eugin]

Is sent Date in the table Date/Time type or Text?

 

[dfsabrown]

the sent date field is a Date/time data type

 

[pr2-eugin]

Humor me and try the code in Post#9.. See what happens..

 

[dfsabrown]

Hi Paul, i did but i got the error message:

run-time error '3075'
Syntax error (missing operator) in query expression '[sent date]='.

i googled this and it looks like i might just be missing a ' or " somewhere but no idea where. Also would it matter that the date format is probably DD/MM, not MM/DD (ie UK format not US format)

this was the code (did i get it right, there wasn't an "Else" part of the if etc, etc??)

--------------------------------


Private Sub Form_Load()


If weekday(Now) = vbTuesday Then

If DCount("*", "tbl_emailLog", "[sentDate] = " & Format(Date, "\#mm\/dd\/yyyy\#") & "") = 0 Then

'send email

DoCmd.SendObject acSendReport, "Actions - outstanding", acFormatPDF, Nz(DLookup("[Lead Investigator email]", "[Case Details]"), ""), Nz(DLookup("[case email address]", "[case details]"), ""), , Nz(DLookup("[Case name]", "[Case Details]"), ""), "Please find attached report of outstanding actions."

DoCmd.RunSQL ("INSERT INTO tbl_emailLog(sentDate) VALUES(" & Format(Date, "\#mm\/dd\/yyyy\#") & ")")

End If

End If

End Sub

---------------------------------------

 

[pr2-eugin]

Okay, try..

If DCount("*", "tbl_emailLog", "[sentDate] = #" & Format(Date, "mm\/dd\/yyyy") & "#") = 0 Then

The problem with International Dates is explained here..

Also your Error shows [sent <space>Date] but your code has [sentDate] is that something you have not looked at properly or is just a typo?

 

[dfsabrown]

thanks Paul, the <space> in error message was just a typo.

I have updated Dcount line per your suggestion, i have also changed Date to now (see red below). AND it seems to work (yay!), the email is sent but now code not working on inserting the sentdate into the emaillog table.

get error message:
run-time error '3134'
syntax error in INSERT INTO statement.

hopefully last bit!!


code:
---------------------------------------------------
Private Sub Form_Load()

If weekday(Now) = vbTuesday Then
If DCount("*", "tbl_emailLog", "[sentDate] = #" & Format(Now, "mm\/dd\/yyyy") & "#") = 0 Then

'send email

DoCmd.SendObject acSendReport, "Actions - outstanding", acFormatPDF, Nz(DLookup("[Lead Investigator email]", "[Case Details]"), ""), Nz(DLookup("[case email address]", "[case details]"), ""), , Nz(DLookup("[Case name]", "[Case Details]"), ""), "Please find attached report of outstanding actions.", false

DoCmd.RunSQL ("INSERT INTO tbl_emailLog(sentDate) VALUES(" & Format(Date, "\#mm\/dd\/yyyy\#") & ")")


End If

End If

End Sub
----------------------------------------------------------

 

[pr2-eugin]

Okay I think the problem is not with the code but with the word Date.. Have you anywhere in your Table/form have a field/control named as Date? If yes, that is the problem, rename the field or use DateValue(Now)..

If no, check the format of the field in the table is it Long Date or Short date? If Long date then change the format to Short date, as Date IS NOT EQUAL TO Now.. Date produces result as 26/03/2013 and Now produces result as 26/03/2013 09:22:28, whihc are not the same..

If nothing works out.. Change Date to Now in the Insert statement.. Also.. On another note.. rather than using DoCmd.RunSQL, I prefer using CurrentDB.Execute.. As it does not prompt for User warning for appending records to table..

CurrentDB.Execute "INSERT INTO tbl_emailLog(sentDate) VALUES(" & Format(DateValue(Now), "\#mm\/dd\/yyyy\#") & ")"

 

[dfsabrown]

I think i have done it! thanks for tip on CurrentDB.Execute as i didn't want a user message

i used this code, it worked but does it look ok to you?

thank you so much for all your help, can't thank everyone enough!


---------------------------------------------------------
Private Sub Form_Load()

If weekday(Now) = vbTuesday Then
If DCount("*", "tbl_emailLog", "[sentDate] = #" & Format(Now, "mm\/dd\/yyyy") & "#") = 0 Then

'send email

DoCmd.SendObject acSendReport, "Actions - outstanding", acFormatPDF, Nz(DLookup("[Lead Investigator email]", "[Case Details]"), ""), Nz(DLookup("[case email address]", "[case details]"), ""), , Nz(DLookup("[Case name]", "[Case Details]"), ""), "Please find attached report of outstanding actions.", False

CurrentDb.Execute ("INSERT into tbl_emailLog (sentDate) select date()")

End If

End If

End Sub

 

[DavidAtWork]

I tested
If DCount("*", "tbl_emailLog", "[sentDate]= #" & Date & "#") > 0

and it works finein A2003, the field sentDate, is it enclosed in square brackets, if there's no space in the field name then it makes no difference but if the field name is:
"sent Date" then you need the square brackets [sent Date], likewise with the log table name

David