Solved

Send Meeting Request Code - Access 2010 and Outlook 2010

Posted on 2011-02-11
1
882 Views
Last Modified: 2012-05-11
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
Comment
Question by:preshomes
1 Comment
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
Follow this checklist to learn more about the 15 things you should never include in an email signature from personal quotes, animated gifs and out-of-date marketing content.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

763 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

10 Experts available now in Live!

Get 1:1 Help Now