Email Reminder

[jereece]

Attached is an Access database that I am creating to help my work group stay on top of routine tasks as well as small project deadlines. It's kind of like a reminder program. One thing I would like to do is add a button on the main form so that when clicked, it would send an email to all the primary task owners giving them a list of tasks that are greater than or equal to "Date()-7". So this email will tell them they are within 7 days of a task being due or it's past due. I thought about having this automatic upon opening, but several people open it daily and I would hate for them to get a bunch of emails every day. I think having this launched from a button is fine because I typically check the status several times a week and I can run the send email feature as I need to.

Bottom line is I have no idea how to do this. Can someone take a look at my Access file and see if they can help?

I appreciate any help you can offer.

Thanks,
Jim

 

[lobodava]

An idea: you can create a table and keep the information regarding the e-mails that were sent. When the form is loaded - check the table and find if the e-mail was sent today.

 

[jereece]

You said "create a table and keep the information regarding the e-mails that were sent" but what I am saying is I don't know how to have emails sent for those whose due date is >date()-7. I know how to write a query that shows a list of these, but how do I have it send emails to the owners??

I would appreciate it if someone could look at my database.

Thanks,
Jim

 

[lobodava]

Could you please post your database in MSA 2000 format?

 

[jereece]

Sure. It is attahed. I appreciate you or anyone looking at this.

Thanks,
Jim

 

[lobodava]

Once you can make a query, could you please post it here?

I suppose that it looks like:

SELECT Task.PRIMARY, Task.[TASK DESCRIPTION], Task.[START DATE]
FROM Task
WHERE (((Task.[START DATE])>=Date()-7));

Isn't it?

 

[ap_abe]

There are a couple of ways that I know of that you could tackle this. There isn't any way (that I am aware of) that allows you to send email directly from Access--obviously you need a client of some sort. I'm assuming (read: hoping) that you are using Microsoft Outlook as your email client. If not, read no further--the rest will not apply.

With that assumption, try using the DoCmd.SendObject Method. This is the simpler of the solutions, but it limits you to plain text body content. There is decent documentation in the Access help files. Syntax is as follows:

DoCmd.SendObject(ObjectType, ObjectName, OutputFormat, To, Cc, Bcc, Subject, MessageText, EditMessage, TemplateFile)

For the sake of readability and debugging, it will be easiest to create a string variable with your message body in it--maybe the subject and To addresses too, up to you.

For example:

Dim strSubject As String
Dim strMessage As String

strSubject = "Tasks Due on or Before " & Date() + 7

'At this point I'm assuming you have a recordset (rstTasks) with all the tasks 
'that meet your criteria--you said you knew how to create that query, so I'm 
'leaving that up to you.

With rstTasks
    While Not .EOF
        strMessage = "The following task is due on or before " & Date() + 7 & vbCrLf & rstTasks!TaskInfo
        'Date() - 7 will only give you tasks overdue by more than 7 days
        'TaskInfo should be Whatever you want to identify the tasks with
        DoCmd.SendObject , , , rstTasks!TaskOwner, , , strSubject, strMessage, True 'If you want to edit the message first, otherwise set to False
        'TaskOwner should be the email address of the Task Owner
        .MoveNext
    Wend
End With

Before running this code, make sure that you have included the Outlook object library in your project. Check by opening up your code window and clicking Tools >> References. Look for something that looks like 'Microsoft Outlook X.X Object Library.' Where X.X is the library version number. If it is not checked, or is not in the list of available references, you must check and/or add it. Look on your computer (in the Outlook install directory) for a file that looks like msoutlX.olb. Again X is a number corresponding with your version of Outlook (in some later versions no number is present).

This isn't really that hard, there's just a lot to get lined up before it will work right. There is another, more current but more difficult to explain way to handle the email. This should work alright for you if I am understanding your needs correctly.

caveat: Should you decide to let the emails get sent automatically (meaning without previewing them and clicking Send) and your Microsoft Outlook is up to date, you will receive an Outlook security warning. It is a message box asking whether you would like another program to be allowed to send email on your behalf. It was designed to stop worms from propagating, but can be kind of obnoxious.

Good luck, I will try to help further if you need it.
ap_abe

 

[jereece]

ap_able - I am not using Outlook, but rather Lotus Notes. I think I have access to a mail server, but right now I can't think of the server's name. I can find out however.

lobodava - I believe my query would be as below. I would want to email them the Task ID number, their name, and the due date. I included the email address as this would be needed. So here is the SQL of my query which I called qry_TasksDue.

SELECT Task.[ID#], Task.PRIMARY, Group.Email, Task.[DUE DATE]
FROM [Group] INNER JOIN Task ON Group.ID = Task.PRIMARY
WHERE (((Task.[DUE DATE])=Date()-7));

I appreciate the help.

Jim

 

[lobodava]

Here are some thoughts...

I would recommend you (if you don't mind) to use prefixes before tables, forms and etc.; and not to use spaces in object and field names...

As far as I remember DoCmd.SendObject use default email client and it can be not only Microsoft Outlook. If this command does not work with your Lotus Notes, then you should find COM library for Lotus Notes to send your emails.

I am afraid that I did not quite understand what kind of records should be selected, but if we take the provided query as the base for a recordset, then I would suggest the following code (I've just changed ap_abe's code a little):

Private Sub cmdSendReminders_Click()
Dim strSQL As String
Dim rstTasks As DAO.Recordset
Dim strSubject As String, strSendTo As String, strMessage As String
Dim strPreviousPrimary As String

strSQL = "SELECT Task.[ID#] AS ID, Task.PRIMARY, Group.Email, Task.[DUE DATE] AS DueDate " & _
         "FROM [Group] INNER JOIN Task ON Group.ID = Task.PRIMARY " & _
         "WHERE Task.[DUE DATE]=Date()-7 " & _
         "ORDER BY Task.PRIMARY, Task.[DUE DATE];"

Set rstTasks = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

With rstTasks
    If .EOF And .BOF Then
        MsgBox "There is no task to worry !!!"
    Else
        'MsgBox "Number of tasks: " & rstTasks.RecordCount ' - use it for testing

        strSubject = "Tasks Due on " & Format(!DueDate, "dd-mmm-yyyy")
        
        .MoveFirst
        While Not .EOF
            If strPreviousPrimary <> !PRIMARY Then 'if is the first task for new primary
                strMessage = "The following task is due on " & Format(Date - 7, "dd-mmm-yyyy") & vbCrLf & vbCrLf
                strSendTo = Nz(!Email, "")
            End If
            strMessage = strMessage & "Task ID: " & !ID & vbCrLf
            strMessage = strMessage & "Due Date: " & Format(!DueDate, "dd-mmm-yyyy") & vbCrLf & vbCrLf
        
            strPreviousPrimary = !PRIMARY
            
            .MoveNext
            
            If .EOF Then 'if it is end of the list we can send last message
                DoCmd.SendObject , , , strSendTo, , , strSubject, strMessage, True
            ElseIf strPreviousPrimary <> !PRIMARY Then 'if it is the next primary we can send the message for previous primary
                DoCmd.SendObject , , , strSendTo, , , strSubject, strMessage, True 'If you want to edit the message first, otherwise set to False
            End If
        Wend
    End If
End With

rstTasks.Close
Set rstTasks = Nothing
End Sub

 

[jereece]

I tried the code and associated it with a button, but all it says when clicked is "There is no task to worry !!". I tried this with tasks due tomorrow, today and past due. It does not seem to work.

Jim