Improve company productivity with a Business Account.Sign Up

x
?
Solved

Access VBA - insert record data into Outlook calendar based on date

Posted on 2009-05-14
5
Medium Priority
?
1,407 Views
Last Modified: 2012-05-07
Hi

I have an Access table that contains three columns: "Date", "Time", "Description".
I want to use Access VBA to loop through the records and add each record
to the correct date (based on the "Date" field) at the correct time (based on the "Time" field). I want to then add the description to this calendar entry (based on the "Description" field.
Thank you
0
Comment
Question by:Murray Brown
  • 2
  • 2
5 Comments
 
LVL 7

Expert Comment

by:shambalad
ID: 24404468
Try the attached code.
Todd

Sub CreateCalendarItem(strSubject As String, strDate As String, strTime As String)
   Dim olkAppt As Outlook.AppointmentItem
   Dim OlkApp As Outlook.Application
   Dim dte As Date
   
   ' Must add Outlook to references for this to work
   dte = CDate(strDate & " " & strTime)
   
   Set OlkApp = New Outlook.Application
   With OlkApp
      Set olkAppt = .CreateItem(Outlook.OlItemType.olAppointmentItem)
      With olkAppt
         .Start = dte
         .Subject = strSubject
         .ReminderSet = False
         olkAppt.Save
      End With
   End With
   
   ' Clean up.
   Set OlkApp = Nothing
   Set olkAppt = Nothing
End Sub
 
Sub testCreateCalendarItem()
   Dim strSubject As String, strDate As String, strTime As String
   strSubject = "Create Menu Item in VBA"
   strDate = "5/20/2009"
   strTime = "8:30:00 PM"
   CreateCalendarItem strSubject, strDate, strTime
End Sub

Open in new window

0
 
LVL 7

Accepted Solution

by:
shambalad earned 2000 total points
ID: 24404482
The attached incorporates the VBA loop to read through the table. It also creates the Outlook object in the calling sub.
Todd

Sub LoadCalendarItems()
   Dim OlkApp As Outlook.Application
   Dim strSubject As String
   Dim rst As DAO.Recordset
   Dim db As DAO.Database
   Dim strDate As String
   Dim strTime As String
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("MyTable")
   Set OlkApp = New Outlook.Application
   With rst
      Do Until .EOF
         strDate = .Fields("Date")
         strTime = .Fields("Time")
         strSubject = .Fields("Description")
         CreateCalendarItem OlkApp, strSubject, strDate, strTime
         .MoveNext
      Loop
      .Close
   End With
 
   ' Clean up.
   Set OlkApp = Nothing
   Set rst = Nothing
   Set db = Nothing
End Sub
 
Sub CreateCalendarItem(OlkApp As Outlook.Application, _
            strSubject As String, strDate As String, _
            strTime As String)
   Dim olkAppt As Outlook.AppointmentItem
   Dim dte As Date
   
   ' Must add Outlook to references for this to work
   dte = CDate(strDate & " " & strTime)
   
   With OlkApp
      Set olkAppt = .CreateItem(Outlook.OlItemType.olAppointmentItem)
      With olkAppt
         .Start = dte
         .Subject = strSubject
         .ReminderSet = False
         olkAppt.Save
      End With
   End With
   
   ' Clean up.
   Set olkAppt = Nothing
End Sub

Open in new window

0
 

Author Closing Comment

by:Murray Brown
ID: 31581815
Many thanks Todd.Great answer!
0
 
LVL 2

Expert Comment

by:hgj1357
ID: 25093977
How can you get a warning that Outlook isn't running? This code requires Outlook to be running, right?
0
 
LVL 2

Expert Comment

by:hgj1357
ID: 25094002
My mistake! It just takes a few seconds to show up if the event was added with Outlook not running.

How would I add a contact? That would be pretty awesome!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

606 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