Jan 18 2022 12:07 PM
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
Jan 18 2022 12:49 PM
Jan 18 2022 01:09 PM
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
Jan 18 2022 01:14 PM
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
Jan 18 2022 01:19 PM
Jan 22 2022 10:09 AM
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
Jan 24 2022 05:44 AM
Jul 22 2022 12:34 PM
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:
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
Dec 14 2022 09:14 PM
Dec 15 2022 05:24 AM