Solved

Send Meeting Request Code - Access 2010 and Outlook 2010

Posted on 2011-02-11
1
889 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
ID: 34891328
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Find out what you should include to make the best professional email signature for your organization.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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