Open Outlook Appointment form and populate some fields for validation

[dpelizzari]

I have been tasked with making an addition to my Access 2007 database, we want to create a button on a screen that will open up the Appointment screen from Outlook (not directly create the appointment), and populate the Subject line and invited attendees from fields within Access (user will need to check the schedule for the time to schedule the appointment and add additional data). Once the appointment has been saved, we would like Access to update a checkbox to show the appointment has been scheduled. Since I am pretty green with VB and Access, any help would be greatly appreciated!

 

[dbDamo]

http://support.microsoft.com/kb/160502
http://www.fabalou.com/Access/General/appointment_outlook.asp
http://www.bigresource.com/Tracker/Track-ms_access-gN35c01A/


I'm sure one of these will give you what you're after, with a little modification of course

 

[dpelizzari]

Thanks, dbDAMO, I tried similar code I found online, the problem with most of them is they want to automatically create and send the appointment, I am looking to partially create and have a user add other information or check scheduling conflicts depending on who the attendees are.

Code samples appreciated!

 

[darbid]

Here is a couple of things you can look at

First this function needs a date. It will then bring up the calendar for this day.

Public Function gotocalendar(datetoview As Date)

Dim olApp As Outlook.Application
Dim olNS As Namespace
Dim olView As Outlook.View
Dim olExp As Outlook.Explorer
Dim bln_QuitOutlook As Boolean  'if outlook is not on then turn it on but turn it off after finishing
    
On Error Resume Next
bln_QuitOutlook = False
Set olApp = GetObject(, "Outlook.Application")

If Err.Number = 429 Then
    Set olApp = New Outlook.Application
    bln_QuitOutlook = True
End If

On Error GoTo 0
    
Set olNS = olApp.GetNamespace("MAPI")

    
If olApp.ActiveExplorer Is Nothing Then
    olNS.GetDefaultFolder(olFolderCalendar).display
Else
    Set olApp.ActiveExplorer.CurrentFolder = _
            olNS.GetDefaultFolder(olFolderCalendar)
     olApp.ActiveExplorer.display
End If

Set olExp = olApp.ActiveExplorer.CurrentFolder.GetExplorer
Set olView = olExp.CurrentView
olView.GoToDate datetoview
    
Set olNS = Nothing
Set olView = Nothing

If bln_QuitOutlook Then
    olApp.Quit
End If

Set olApp = Nothing
 
End Function

For an appointment item you just need to display it. So the code you talk about should be ok.

here is some code it is NOT perfect and may have mistakes in it but it will give you an idea.

If you dont read and understand all my comments and add the code as needed it will never work. but you can always then come back here and ask a specific question.

Dim objNS As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder 'get name of other persons folder
Dim objRecip As Outlook.Recipient 'other persons name
Dim strName As String 'the name or email of the persons folder
Dim objAppt As Outlook.AppointmentItem
Dim objApp As Outlook.Application


On Error Resume Next

' ### name of person whose Calendar you want to use ###
strName = str_app_user


'This example assume that the Outlook object is already there.
'see my above example to get the outlook object and then you add it here



Set objNS = objApp.GetNamespace("MAPI")

Set objRecip = objNS.CreateRecipient(strName)

'note that this uses GetSharedDefaultFolder which is a little bit more flexible cause you can then choose the calendar
'you can change this to GetDefaultFolder
Set objFolder = objNS.GetSharedDefaultFolder(objRecip, olFolderCalendar)

If Not objFolder Is Nothing Then
    Set objAppt = objFolder.Items.Add
    
    If objAppt Is Nothing Then
        Set objAppt = objApp.CreateItem(olAppointmentItem)
    End If
    
    
Else
    MsgBox "no access to the folder"
End If


 
        With objAppt
            .Start = Format(Me.txt_start, "Short Date") & " " & Format(Me.txt_starttime, "Short Time")
            .End = Format(Me.txt_end, "Short Date") & " " & Format(Me.txt_endtime, "Short Time")
            .Location = Me.txt_location
            .Subject = Me.txt_subject
            .MeetingStatus = olMeeting
            .ReminderMinutesBeforeStart = 20
            .BusyStatus = olBusy
            .RequiredAttendees = Me.cbo_participants
            .Mileage = appointmentID
            .Recipients.ResolveAll
            

            .Save
            
            .display
            
            
 
        End With
        
        
        
        Set objNS = Nothing
Set objFolder = Nothing
Set objRecip = Nothing

Set objApp = Nothing

'dont forget to also set outlook to nothing.  Also do the Outlook object last.  Outlook will not close properly otherwise.

 

[dpelizzari]

Wow, I sifted through the code you sent, along with some other stuff I found online... here is my code currently:
Private Sub Command5_Click()
If Me.Dirty Then
Me.Dirty = False
End If
If Me.chkAddedtoOutlook = True Then
MsgBox "This appointment has already been created", vbCritical
Exit Sub
End If
Dim olapp As Object
Dim olappt As Object
Set olapp = Outlook.Application
Set olappt = olapp.CreateItem(olAppointmentItem)
With olappt
.Subject = "New PC Refresh"
.BusyStatus = olBusy
.RequiredAttendees = "pelizdr"
.Recipients.ResolveAll
.Display
End With
Set olappt = Nothing
Set olapp = Nothing
Me.chkAddedtoOutlook = True
MsgBox "Appointment Added!", vbInformation
End Sub

It is opening up a new Calendar item, and setting the attendee and subject line as expected (awesome!). I of course will need to add more variables, but it is getting much better. Thanks for the help!