Solved

Exporting Birthday information from Access to Outlook

Posted on 2009-04-05
10
583 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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now