Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    2

    Unanswered: greetings!! kindly help me out

    hey people,

    sleepless nights have brought me here ... before this panel of experts ... in hope that we may get answers that would enable me to sleep with ease ...

    i'm currently working on access trying to develop a code that will automatically send reminders about specific records once a certain period of time has lapsed ... please provide me with any assistance in getting this senario resolved ...

    hola

  2. #2
    Join Date
    Feb 2004
    Posts
    90
    You need to provide more information:

    "automatically send reminders" - How do you want these reminders to be sent? A pop up in Access itself? Through email?

    "once a certain period of time has lapsed" - in relation to what?

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Reminders

    I agree with Goldy - need more info. The only thing that comes to mind with the little information you supplied is to do something with the timer event on a specific form and check to see if a specific time has been reached or if you're using SQL Server, a scheduled event. Once that time has been reached, a docmd.sendobject for sending an email. There are a lot of different options and like Goldy replied, if you can give us some more info.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jun 2006
    Posts
    103
    Quote Originally Posted by pkstormy
    I agree with Goldy - need more info. The only thing that comes to mind with the little information you supplied is to do something with the timer event on a specific form and check to see if a specific time has been reached or if you're using SQL Server, a scheduled event. Once that time has been reached, a docmd.sendobject for sending an email. There are a lot of different options and like Goldy replied, if you can give us some more info.
    Not sure how do you want to send email... but here is a piece of vba code that i used to send email for the person who in charge of some staff if his/her staff didn't submit timesheet for a specific day, hope it gives you some reference.

    It will detect if it is a working day and grap all staff that didn't submit timesheet and send a timesheet chaser for all their approver.

    By the way, I am using Lotus Note

    Code:
    'To sent out the email chasers
    Public Sub SendChaser(ByVal AppName As String, ByVal AppEmail As String)
    
        Dim DateString As Date
        DateString = Now() - 1
        If Weekday(DateString, vbMonday) = 7 Then
            DateString = Now() - 3
        End If
        
        Dim EmailTitle As String
        EmailTitle = "Late Timesheet Submission - " & Format(DateString, "dd-Mmm-yy")
        
        Dim EmailText As String
        EmailText = "Dear " & AppName & "," & vbCr & _
                    vbCrLf & _
                    "List of Staff who have not submitted timesheets:" & vbCr & _
                    vbCrLf
                    
        Worksheets("StaffList").Activate
        Dim counter As Integer
        counter = 2
        ActiveSheet.Range("E" & counter).Activate
        
        If ActiveCell.Text = "" Then
            Exit Sub
        End If
        
        Dim DateHeader, StaffName, DateDetails As Variant
        DateHeader = "Date: " & Format(DateString, "dd-MMM-yyyy") & " (" & Format(DateString, "Ddd") & ")" & vbCr
            
        Do While ActiveCell.Text <> ""
            StaffName = StaffName & ActiveSheet.Range("E" & counter).Text & vbCr
    
            counter = counter + 1
            ActiveSheet.Range("E" & counter).Activate
        Loop
        DateDetails = DateDetails & DateHeader & StaffName & vbCrLf
        EmailText = EmailText & DateDetails & vbCrLf & "MIS Team" & vbCrLf & vbCrLf & _
                    "#This is a system-generated email. Please DO NOT REPLY.#"
        'MsgBox EmailText
        Call SendNotesMail(EmailTitle, "", AppEmail, EmailText, True)
    
    End Sub
    
    'This public sub will send a mail and attachment if neccessary to the
    'recipient including the body text.
    'Requires that notes client is installed on the system.
    Public Sub SendNotesMail(subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)
    'Set up the objects required for Automation into lotus notes
        Dim mailDb As Object 'The mail database
        Dim UserName As String 'The current users notes name
        Dim MailDbName As String 'THe current users notes mail database name
        Dim mailDoc As Object 'The mail document itself
        Dim AttachME As Object 'The attachment richtextfile object
        Dim Session ' As Object 'The notes session
        Dim EmbedObj As Object 'The embedded object (Attachment)
        Dim WshShell 'As Object
        
        'Start a session to notes
        Set Session = CreateObject("Notes.NotesSession")
        UserName = Session.UserName
        MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
        Set mailDb = Session.GetDatabase("", MailDbName)
        Call mailDb.OPENMAIL
        'Get the sessions username and then calculate the mail file name
        'You may or may not need this as for MailDBname with some systems you
        'can pass an empty string or using above password you can use other mailboxes.
        UserName = Session.UserName
        MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
        'Open the mail database in notes
        Set mailDb = Session.GetDatabase("", MailDbName)
         If mailDb.IsOpen = True Then
              'Already open for mail
         Else
             mailDb.OPENMAIL
         End If
        'Set up the new mail document
        Set mailDoc = mailDb.CREATEDOCUMENT
        mailDoc.Form = "Memo"
        mailDoc.sendTo = Recipient
        mailDoc.subject = subject
        mailDoc.body = BodyText
        mailDoc.SAVEMESSAGEONSEND = SaveIt
        'Set up the embedded object and attachment and attach it
        If Attachment <> "" Then
            Set AttachME = mailDoc.CREATERICHTEXTITEM("Attachment")
            'Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
            'MailDoc.CREATERICHTEXTITEM ("Attachment")
        End If
        'Send the document
        mailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
        mailDoc.SEND 0, Recipient
        'Clean Up
        Set mailDb = Nothing
        Set mailDoc = Nothing
        Set AttachME = Nothing
        Set Session = Nothing
        Set EmbedObj = Nothing
    
    End Sub

  5. #5
    Join Date
    Jun 2006
    Posts
    2

    setting a timer for a scheduled task

    Thanks mkggoh, I'll try it out then get back to you.

    I'm currently working with MS Access. I would like to create pop up reminders in ms access itself to remind clients of an annual scheduled check up from their last recorded visit. I need a report that will remind me to contact the specified clients 1 month prior their next visit.

    The docmd.sendobject for sending an email directly sounds interesting. Kindly let me know more about this too.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •