Create Outlook meeting from Access VBA

Copper Contributor

I am the treasurer at a United Methodist Church (UMC).  I would like to create an Outlook meeting based on input from an Access database form.  The catch is that I would like the organizer of the meeting to be another user, specifically, a dummy user that I created called UMC Events.  This user's calendar holds all of the events scheduled at the church.  The secretary and I have the UMC Events mailbox added to our own Outlook profile.  Normally, we would go to the UMC Events calendar and manually create the meeting from there.  Recently, we added a schedule of fees associated with people renting our rooms for various events.  I said I would add this to the database that I created a few years ago to manage the church's information.  I thought it would be good to be able to schedule the meeting right from this form, rather than have to go back to Outlook to schedule.  A few years ago, I found the code below on a user's forum and added it, with a few modifications.  Unfortunately, I don't understand some of the code, so I don't know what would need to be changed.  When I run this, it creates the meeting and invites UMC Events, but it doesn't go on UMC Event's calendar and it puts it on my calendar, which I don't want.  Short of logging out and logging in as UMC Events, is there a way to force the organizer of the meeting to be someone other than yourself?

 

Private Sub cmdScheduleMeeting_Click()
Dim dteDate As Date
Dim dteStart As Date
Dim dteEnd As Date
Dim strLocation As String
Dim strSubject As String
Dim strInvitees As String
Dim myOutlook As Outlook.Application
Dim oNS As Outlook.NameSpace
Dim oRecip As Outlook.Recipient
Dim oFolder As Outlook.Folder
Dim myMeeting As AppointmentItem
Dim oAccount As Outlook.Account
Dim Accounts As Outlook.Accounts
Dim N As Integer
With Me                                                                              
    dteDate = !EventDate                      'This group of code pulls in values                                               
    dteStart = !StartTime                     'from fields on the form on which                                               
    dteEnd = !EndTime                         'the button exists.  GroupID is a                                                
    strLocation = EventLocation(!EventID)     'field which would represent a group                                       
    strSubject = !Event                       'within the church who is invited to                                       
    If IsNull(!GroupID) Then                  'the meeting.  It would be left blank                                          
        strInvitees = "Email address removed"    'if someone were renting a room                             
    Else                                            'for a personal event.  umcevents@
        strInvitees = "Email address removed" & MembersEmailsByGroup(!GroupID)
    End If                                          'umcofmacedonia.org is the email 
End With                                            'address for UMC Events.
Set Accounts = Session.Accounts                     'I don't fully understand the lines
Set myOutlook = CreateObject("outlook.application")'regarding oNS, oRecip, and oFolder.                                  
Set oNS = myOutlook.GetNamespace("MAPI")           'These were in the original code I                                 
Set oRecip = oNS.CreateRecipient("UMC Events")    'copied.  The oRecip line originally                                 
Set oFolder = oNS.GetSharedDefaultFolder(oRecip, olFolderCalendar)                   
Set myMeeting = myOutlook.CreateItem(olAppointmentItem) 'had another username there, so                                             
With myMeeting                                      'I thought by changing it to UMC 
    .Resources = strLocation                        'Events, it would force it to  
    .MeetingStatus = olMeeting                      'be the organizer.
    .RequiredAttendees = strInvitees
    .Subject = strSubject
    .Start = dteDate + dteStart
    .End = dteDate + dteEnd
    .ReminderMinutesBeforeStart = 15
    .Location = strLocation
    For Each oAccount In Accounts
        If oAccount.DisplayName = "Email address removed" Then
            .SendUsingAccount = oAccount
            .Save
            .Send
        End If
    Next
End With
Set myMeeting = Nothing
Set myOutlook = Nothing
End Sub                                                      
                                                       
                                                   
                                                
                                                
                                               
9 Replies
Jill, I think you are very close. Instead of calling Application.CreateItem, create the item directly using oFolder.Items.Add
So instead of
Set myMeeting = myOutlook.CreateItem(olAppointmentItem)
try
Set myMeeting = oFolder.Items.Add(olAppointmentItem)

@Jill_Marlow 

I think you must replace this with your umc events mail address:

 

If oAccount.DisplayName = "Email address removed" Then

 

like:

 

If oAccount.DisplayName = "Email address removed" Then

 

If this isn't working then replace this too like:

 

If oAccount.SmtpAddress= "Email address removed" Then

 

That worked!  Thanks so much.  I created a test event and it put it on UMC Event's calendar and not mine.  The only glitch is, and this happened originally, is that the room is on the meeting twice.  I'll have to look at the code more closely to see if the room is somehow being invited twice.  Thanks again! @Maria Barnes 

I just figured it out. I had both .resources and .location which were set to the name of the room chosen in the form. I didn't need both, so I deleted the line with .location.

I have another related question.  I see that there are properties associated with a meeting that have to do with recurrence.  How would I modify the code to include a recurrence of, say, the second Monday of every month until December of the current year?  @Jill_Marlow 

Jill, you should be able to set the following items to make the meeting use a recurrence. This assumes you have already set the .Start to a 2nd Monday date. FYI the GetRecurrencePattern returns either a RecurrencePattern already setup or a blank object and then you can fill it.
Dim oRecurrence As Outlook.RecurrencePattern
Set oRecurrence = myMeeting.GetRecurrencePattern
With oRecurrence
.RecurrenceType = olRecursMonthNth
.PatternEndDate = "12/5/2022'
End With
Also see https://documentation.help/Microsoft-Outlook-Visual-Basic-Reference/olobjRecurrencePattern.htm for more info about different types of recurrence

@Jill_Marlow 

As I said in my previous reply, the test I created was successful.  I hadn't used the code since then.  I tried another test because I added another criterion.  Now when I run the code, it puts the meeting on the UMC Events calendar, but on the line .Send to send the invitation to the invitees and the resources, I get the following error:

Jill_Marlow_0-1658518136765.png

Here is the code that is scheduling the meeting:

With myMeeting
.Resources = strLocation
.MeetingStatus = olMeeting
.RequiredAttendees = strInvitees
.Subject = strSubject
.Start = dteStartDate + dteStartTime
.End = dteEndDate + dteEndTime
.ReminderMinutesBeforeStart = 15
For Each oAccount In Accounts
If oAccount.DisplayName = "email address removed for privacy reasons" Then
.SendUsingAccount = oAccount
.Save
.Send
End If
Next
End With

 

I restarted Outlook as the error message suggested, but I still get the same error.  Since I last used this code, I got a new computer which is running Windows 11 if that makes a difference.  Does anyone know how to resolve this issue?  Thanks.

 

Jill

I am very interested in this working as well. But on the line 8 I get an error. When I chance it to as Application it works, but not line 9 comes up with error and I am not sure what do. It says user-defined type not defined.
Did you set the references and Dim the objects required for Outlook Automation?