Routine to auto-send weekly email plus 3 attachments, code needed!

[JeffBarker]

Hi guys,

Yet another random request from me that I'm hoping someone will be able to assist with - this time based around a membership database that we're currently running on behalf of a client.

The database holds around 2,000 members, each with their own expiry date, and each week we email a newsletter to everyone.

At the moment I'm physically running the queries every Friday to extract the info for these emails, and depending on the member expiry date we tailor some of the content that they see.

For example, if their membership expires in the next 21 days then the email header says "you have three weeks until your membership expires", if their membership expires in 14 days the header says "you have two weeks" and so on.

What I need is a code that will run every Friday morning and generate the four queries we need for the newsletter and attach and send them in an email to my colleague - but only if the query has data in it.

I've got the Weekday bit sorted:

Private Sub cmdWeeklyEmail_Click()

Dim MyDate As Date
Dim MyWeekday As Integer

MyDate = Date
MyWeekday = Weekday(MyDate, vbSunday)

If MyWeekday = vbThursday Then
    MsgBox "It's Thursday!"
Else
    MsgBox "Code's rubbish"
End If

My original thought was to run the code on a Friday when the DB is opened, but as both myself and other colleagues will cose and re-open the database many times within a day I understand that the code will run every single time that happens on a Friday.

So I need two things

1) A solution for how to ensure the routine is run only once on a Friday when the DB is first opened by a user
2) A way of only attaching the queries as an Excel spreadsheet if there are records in them

Thanking you all in advance - I shall be working on this myself, so as and when I crack a section I'll report back here!

 

[JeffBarker]

So I've just cracked a way of only attaching the queries that contain data:

Private Sub cmdWeeklyEmail_Click()

Dim MyDate As Date
Dim MyWeekday As Integer

MyDate = Date
MyWeekday = Weekday(MyDate, vbSunday)

If MyWeekday = vbThursday Then 'obviously this will be Friday, I'm just using vbThursday as today is Thursday!
    If DCount("*", "qryHelpdeskEmail") > 0 Then
        DoCmd.OpenQuery "AHCP_HelpdeskEmail"
    Else
        MsgBox "No records"
    End If
else
    Exit Sub
end if

What I'll do is extract the queries with data into Excel, then attach those files to the email before sending.

So all I need now is a surefire way of ensuring this routine only gets run once on a Friday!

Thanks again in advance, people!

 

[Solo712]

So I've just cracked a way of only attaching the queries that contain data:

Private Sub cmdWeeklyEmail_Click()
 
Dim MyDate As Date
Dim MyWeekday As Integer
 
MyDate = Date
MyWeekday = Weekday(MyDate, vbSunday)
 
If MyWeekday = vbThursday Then 'obviously this will be Friday, I'm just using vbThursday as today is Thursday!
    If DCount("*", "qryHelpdeskEmail") > 0 Then
        DoCmd.OpenQuery "AHCP_HelpdeskEmail"
    Else
        MsgBox "No records"
    End If
else
    Exit Sub
end if

What I'll do is extract the queries with data into Excel, then attach those files to the email before sending.

So all I need now is a surefire way of ensuring this routine only gets run once on a Friday!

Thanks again in advance, people!

Hi Jeff,
the simplest way to track the single-purpose emails would be to create a new field in the member's table, sth like datelastemail. Stamp it with Now() when you send email. Check this field each time before the email routine runs. If it has the current date, skip the record.

Best,
Jiri