Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Exporting Birthday information from Access to Outlook

Posted on 2009-04-05
10
Medium Priority
?
604 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:Harry Batt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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:Harry Batt
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 2000 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

604 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