[Vikerz]

Hi there,

Need help on this, please. The problem is i need to track the due date of documents submissions and auto alert via e-mail + popup message. I have a field name [ID], [File Number], [Unit No], [Unit Name], [Meeting-means starting date] and [Alert- means due date]. Every submissions tracking starts on meeting date and 14 days after the start date is the due date. I need to track the due date after the meeting and alert me and my colleagues via e-mail alert + popup message additional if possible. My apologies for my english and explanation, just a noob. Please I really need this to work. Thanks .

[etaf]

what application are you using ??
Excel, access,SQL, other - please specify

[OBP]

If this is Access then see this Thread.
http://forums.techguy.org/business-a...can-alert.html

or this which sends a word doc.

http://forums.techguy.org/business-a...el-access.html

[Vikerz]

Originally Posted by OBP

If this is Access then see this Thread.
http://forums.techguy.org/business-a...can-alert.html

or this which sends a word doc.

http://forums.techguy.org/business-a...el-access.html

Sorry guys I forgot to mentioned, I'm using Access 2007 and not much familiar with the code.

OBP I saw that already, the first link above but im a bit confused.

Thanks!

[OBP]

Well I can't work in Access 2007, but if you can save the database in Access 2003 format and email me a zipped copy I can do it for you.
Or if you Post it as a zipped attachment on here I or one of the other posters can take a look for you.
Just ensure that it does not contain any Personal Data.
I have to go soon ( I am in the UK) as my wife kicks me off the Computer in the evenings, I have already been on here for 9 hours.

[Vikerz]

That was really great help, many thanks. I'll zipped a copy and send it.

[OBP]

I have private mailed you my email address.
I will post the VBA code that we use so that others can see it.

[Vikerz]

That's pretty cool and seems great to me.

[OBP]

Vikerz, I have emailed you a question about the automatic email alert.

[Vikerz]

Originally Posted by OBP

Vikerz, I have emailed you a question about the automatic email alert.

I got your e-mail and I already reply on it. Cheers!

[OBP]

For anybody interested in doing something similar here is the VBA code in the Form that send data from various queries to an Emailing Module

Private Sub Form_Load()
On Error GoTo errorcatch
filename = "OneDayEmails"
rep = "1"
Call sendemails(filename, rep)
filename = "TodaysEmails"
rep = "2"
Call sendemails(filename, rep)
filename = "ThreeDayEmails"
rep = "3"
Call sendemails(filename, rep)

MsgBox "done"
Exit Sub
errorcatch:
MsgBox Err.Description
End Sub

Here is the VBA Code in the Module that sends the VBA code to the Person in the Record in a field called initials and a copy (in a different format) to the administrator.

Public filename As String, rep As String
Public Sub sendemails(filename As String, rep As String)
Dim subject As String, Body As String, EmailAddress As String, emailadmin As String, adminbody As String, adminsubject As String
Dim rs As Object, recount As Integer, count As Integer, rst As Object, initrecount As Integer, initcount As Integer
Dim sql As String
On Error GoTo errorcatch
Set rst = CurrentDb.OpenRecordset("IntitialsQuery")
rst.MoveLast
rst.MoveFirst
initrecount = rst.RecordCount
    For initcount = 1 To initrecount
        EmailAddress = rst![emailAddress]
        subject = ""
        Body = rst![Name] & "," & Chr$(13) & Chr$(13)
        sql = "SELECT * " & _
        "FROM " & filename & _
        " WHERE Initials = '" & rst![InitialID] & "'"
        Set rs = CurrentDb.OpenRecordset(sql)
        If rs.RecordCount > 0 Then
            rs.MoveLast
            rs.MoveFirst
            recount = rs.RecordCount
            For count = 1 To recount
                subject = subject & rs![File Number] & "  "
                Body = Body & "FILE NUMBER: " & rs![File Number] & Chr$(13) & "UNIT NUMBER: " & rs![Unit No] & Chr$(13) & "UNIT NAME: " & rs![Unit Name] & Chr$(13) & Chr$(13)
                rs.MoveNext
            Next count
            Body = Body & "Drawings submission is due on " & Date + 1
            Body = Body & ". This is Report (" & rep & "). Please do the needful within this day." & Chr$(13) & Chr$(13) & "Thank you!" & Chr$(13) & Chr$(13) & "Administrator"
            'MsgBox subject & vbNewLine & Body
        End If
        rs.Close
        If subject <> "" Then DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
        rst.MoveNext
    Next initcount
    'MsgBox subject & vbNewLine & Body
Set rs = CurrentDb.OpenRecordset(filename)
adminbody = "Administrator, please note. " & Chr$(13) & Chr$(13)
emailadmin = "email goes here"
If rs.RecordCount > 0 Then
    rs.MoveLast
    rs.MoveFirst
    recount = rs.RecordCount
    For count = 1 To recount
        adminsubject = adminsubject & rs![File Number] & "  "
        adminbody = adminbody & "FILE NUMBER: " & rs![File Number] & Chr$(13) & "UNIT NUMBER: " & rs![Unit No] & Chr$(13) & "UNIT NAME: " & rs![Unit Name] & Chr$(13) & Chr$(13)
        rs.Edit
        If rep = "1" Then rs!emailalertsent1 = -1
        If rep = "2" Then rs!emailalertsent2 = -1
        If rep = "3" Then rs!emailalertsent3 = -1
        rs.Update
        rs.Bookmark = rs.LastModified
        rs.MoveNext
    Next count
    adminbody = adminbody & "Drawings submission is due on " & Date + 1 & Chr$(13) & Chr$(13)
    'MsgBox adminsubject & vbNewLine & adminbody
    DoCmd.SendObject , , , emailadmin, , , adminsubject, adminbody, False
End If
rs.Close

Set rs = Nothing
rst.Close
Set rst = Nothing

Exit Sub
errorcatch:
MsgBox Err.Description

End Sub

Note it uses 2 Public Variables, filename and rep to pass the data to the Module

[slurpee55]

Originally Posted by OBP

I have to go soon ( I am in the UK) as my wife kicks me off the Computer in the evenings, I have already been on here for 9 hours.

That is probably good for you, OBP! Make sure she takes care of you - you know, we old guys need to eat and stuff....

[OBP]

Eating, mmmm, damn, is that where I have being going wrong then.

[slurpee55]

Yep - ya know, you can't run on electrons all day!

[Vikerz]

Hey OBP, TWO Thumbs for your work and really work smooth in A2007. The best, Cheers!