Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 904
  • Last Modified:

Send Meeting Request Code - Access 2010 and Outlook 2010

I have an Access Database setup like so...

I have a table called t_users  The fields are..

Field 1 - Employee
Field 2 - Email Address

I have a table called t_rooms. The field is ..

Field 1 - Meeting Room

I have a form called f_users and it consists of a drop down box, field name Employee and I choose a name from the table t_users. I also have a drop down box, field name Room and I choose a Room from the t_rooms table.


I need a script/module....

I want to press a form button and create a meeting request to this chosen employee and I want the chosen Room as the Subject.  I also need to manipulate the date/time of the meeting request before being sent either using date fields on the form or another method.

Thanks!
0
preshomes
Asked:
preshomes
1 Solution
 
omgangCommented:
I wrote this as a public function.  You can modify for use directly on your form or you can add input parameters to pass in for your purposes.
You'll need to handle how you want to populate the other properties for the meeting, e.g. location, body, etc.
If you want to use form fields to pass the meeting start and end date/time values then replace the user prompts with value assignments from your form field.

OM Gang



Public Function SendOutlookApptReminder()
On Error GoTo Err_SendOutlookApptReminder

    Dim olOutlook As New Outlook.Application
    Dim olCalendarItem As Outlook.AppointmentItem
    Dim olMailItem As Outlook.MailItem
    Dim olRecipient As Outlook.Recipient
    Dim dteStart As Date, dteEnd As Date, dteDefault As Date
   
        'default meeting date/time is today at noon
    dteDefault = Date & " 12:00:00 PM"
   
        'prompt user for start date/time --- you should probably add a validation step to make sure the user entered a valid date/time value
    dteStart = CDate(InputBox("Enter meeting start date and time, e.g. " & dteDefault, "Start Time", dteDefault))
        'prompt user for end date/time
    dteEnd = CDate(InputBox("Enter meeting end date and time, e.g. " & DateAdd("h", 1, dteDefault), "End Time", DateAdd("h", 1, dteDefault)))
   
    Set olCalendarItem = olOutlook.CreateItem(olAppointmentItem)
   
    With olCalendarItem
        .Subject = "This is the subject"    '<---- change to = Me.Room if that's the name of the combo box on your form
        .Body = "This is the body"
        .Location = "This is the location"
        .start = dteStart
        .End = dteEnd
        .ReminderSet = True
        .ReminderMinutesBeforeStart = 15
        .BusyStatus = olBusy
        .ResponseRequested = True
        .MeetingStatus = olMeeting
    End With
   
    Set olRecipient = olCalendarItem.Recipients.Add("preshomes@ee.com")    '<--- change to Me.Employee if that's the name of the combo box on your form
    olRecipient.Type = olTo
   
    olCalendarItem.Save
    olCalendarItem.Send

Exit_SendOutlookApptReminder:
    Set olRecipient = Nothing
    Set olMailItem = Nothing
    Set olCalendarItem = Nothing
    Set olOutlook = Nothing
    Exit Function

Err_SendOutlookApptReminder:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Function SendOutlookApptReminder of Module Module1"
    Resume Exit_SendOutlookApptReminder
   
End Function
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now