Solved

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

Posted on 2009-05-14
5
1,372 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:murbro
  • 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 500 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:murbro
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 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

14 Experts available now in Live!

Get 1:1 Help Now