Solved

Exporting Birthday information from Access to Outlook

Posted on 2009-04-05
10
586 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:hbatt
  • 5
  • 4
10 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24072449
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24072478
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24072484
OK,

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

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

;-)

Jeff
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 24072497
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24072520
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24072547
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24072624
rr,
Did I miss something?
;-)

Jeff
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24072638
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
 

Author Comment

by:hbatt
ID: 24072760
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24072772
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question