Exporting Birthday information from Access to Outlook

Attached is the code I use to export Birthday information from Access to outlook.  It may not be pretty, but it works.  Rather than have all birthday records exported into Outlook, I have a form that asks the user to select the month of birthday information to be exported.  Since I have multiple users, what I would like is for this form to pop up, at the beginning of the month, so that each user can choose whether or not to create Outlook reminders.  Any ideas on how to do this would be appreciated.  Specific code would be appreciated.  I would prefer that these messages be generated through Access and not as Outlook appointment reminders.

Thanks!
Public Function ExportBirthdaysToOutlook()
On Error GoTo ErrorHandler
      
   Dim fldCalendar As Outlook.Folder
   Dim appt As Outlook.AppointmentItem
   Dim strApptName As String
   Dim dteStartTime As Date
   Dim dteEndTime As Date
   Dim strStatus As String
   Dim lngStatus As Long
   
   Set appOutlook = GetObject(, "Outlook.Application")
   Set nms = appOutlook.GetNamespace("MAPI")
   Set fldCalendar = nms.GetDefaultFolder(olFolderCalendar)
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblBirthdays")
   
   With rst
      Do While Not .EOF
         'Check that there is an appointment subject
            strApptName = Nz(![Title])
            Debug.Print "Appointment name: " & strApptName
            If strApptName = "" Then
               GoTo NextAppt
            End If
                     
            'Create new appointment item in local
            'Calendar folder
            Set appt = fldCalendar.Items.Add
            appt.Subject = "Birthday Reminder! " & ![Contact] 'strApptName
            appt.Start = ![Reminder] 
            appt.End = ![Reminder]
            appt.Location = ""
            appt.Body = Nz(![Contact] & vbNewLine & "Phone Number: " & ![PhoneNumber] & vbNewLine & ![CompanyName] & vbNewLine & ![Constituency] & vbNewLine & "Date of Birth: " & ![DateofBirth] & vbNewLine & "Current Age: " & ![Age])
            appt.Close (olSave)
 
NextAppt:
         .MoveNext
         Loop
   End With
    MsgBox "Selected birthday records exported to Outlook", vbInformation
    
ErrorHandlerExit:
   Exit Function
 
ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
 
End Function

Open in new window

Harry BattDirector of DevelopmentAsked:
Who is Participating?
 
rockiroadsCommented:
well you could continue to us the birthday table since you have a flag there but have you got anything that resets it? this is why I thought of creating a lastnotifydate. You could put that in your table then each time this common form runs, check that last notify date with current date (like I showed in the example earlier but using a different table). If it differs then assume no one has been prompted.
Prompt the user, then create if they say
Afterwards, set LastNotifyDate to Now() and your flag so users will not get prompted until first user checks that form next month
0
 
Jeffrey CoachmanMIS LiasonCommented:
The issue here is the statement:
 "I would like is for this form to pop up, at the beginning of the month"

So on the last day of the month you want the form to open.
No problem,

Suppose the user is not at work that day?
The form will not open, then they will have missed oportunity to run the code.


In any event you have to set up a "Scheduled task" in Windows to do this.

Create a macro that simply opens the form.
Name the macro something like:  mcrEOM_BDAY

Then run a command like this in the Task scheduler.
"C:\Program Files\YourSpecificOfficeFolder\MSACCESS.EXE" "C:\YourFolder\YourDatabase.mdb" /x  mcrEOM_BDAY

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Sorry, I clicked "Submit" too fast...

Set the task to run:
- Monthly
- Start Time:  (Whatever time you feel the employee will be in house and logged in)
- Day: 28  (to catch the short February months)
- (Make sure all the months are checked on.)

JeffCoachman
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
OK,

On second thought the 28 day might be a weekend...

Set it to run on the Last Friday of the month.

;-)

Jeff
0
 
rockiroadsCommented:
Do you have a common form that users use? If not then you can use a AutoExec macro

Best thing here to ensure they do not get reminded again (in case they open app multiple times a day) is to create a table of users which contains a flag to indicate if they have been told.

I would do something like this.

Create a table tblUsers
UserID (AutoNumber)
UserName (name of user, can use windows logon id to hold this)
LastNotifyDate (date)
Notified (yes/no)

Now based on data here, prompt user. Auto add if user not in db

In code (either AutoExec or form) create a function to check if user has been notifid for current month
eg

Public Function CheckAndNotifyUser() As Boolean

    Dim rs As dao.Recordset
    Dim bPrompt As Boolean

    CheckAndNotifyUser = False
    bPrompt = False
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblUsers WHERE UserName = '" & Environ("Username") & "'")
   
    'User not in db, add then promt
    If rs.EOF = True Then
        rs.AddNew
        rs!UserName = Environ("Username")
        bPrompt = True
    Else
        'If date in current month but not notified or last notified date differs then prompt
        If (Format(rs!LastNotifyDate, "YYYYMM") = Format(Now(), "YYYYMM") And rs!Notified = False) Or Format(rs!LastNotifyDate, "YYYYMM") <> Format(Now(), "YYYYMM") Then
            rs.Edit
            bPrompt = True
        End If
    End If
   
   
    If bPrompt Then
        'Prompt user, if they say yes then return true
        If MsgBox("Do you want to create reminders?", vbYesNo, "Confirm") = vbYes Then
            CheckAndNotifyUser = True
        End If
       
        'Update record to indicate user notified
        rs!Notified = True
        rs!LastNotifyDate = Now()
        rs.Update
    End If
       
    rs.Close
    Set rs = Nothing
end function



Now when u call this function, if it returns true then either in here or wherever you call it from, run your outlook code you got.

I would expect this line

Set rst = dbs.OpenRecordset("tblBirthdays")

to change to include the username

eg

Set rst = dbs.OpenRecordset("select * from tblBirthdays where usernamefield = '" & environ("username") & "'")


0
 
rockiroadsCommented:
the suggested changes to Set rst = dbs.OpenRecordset("tblBirthdays"), that is only if it is user specific details in there otherwise leave as is


One final thing, if you want any user to change a global setting then the function provided, you dont bother with a username. Just select by month
0
 
rockiroadsCommented:
here it is if you have a global setting for any user to modify
Table is now called tblLastNotify and contains one field LastNotifyDate

Public Function CheckAndNotifyUser() As Boolean

    Dim rs As dao.Recordset
    Dim bPrompt As Boolean

    CheckAndNotifyUser = False
    bPrompt = False
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblLastNotify WHERE Format(LastNotifyDate,'YYYYMM') = " & Format(Now(), "YYYYMM"))
   
    'Current date not in db
    If rs.EOF = True Then
        If MsgBox("Do you want to create reminders?", vbYesNo, "Confirm") = vbYes Then
            CheckAndNotifyUser = True
            rs.Edit
            rs!LastNotify = Now()
            rs.Update
        End If
    End If
   
    rs.Close
    Set rs = Nothing
end function


again, same as before. either call from here or caller (if true returned) to your export to outlook code
0
 
Jeffrey CoachmanMIS LiasonCommented:
rr,
Did I miss something?
;-)

Jeff
0
 
rockiroadsCommented:
Hi Jeff, nothing wrong with your automated method. In fact it works very well.

But I was going by this line

"so that each user can choose whether or not to create Outlook reminders."

So assumed that some user interaction was required, hence the suggestion I made.
0
 
Harry BattDirector of DevelopmentAuthor Commented:
I do have a common form that people use.  It allows selection by month and also by classification.  The records that have birthday information have classifications, in my case, volunteers, board members, staff, others; and some users might only want to set reminders for a particular class.  I do like the user table suggestion--I had a flag on the birthday table, but once one user set the flag to true, other users would have been unable to export reminders.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.