Outlook VBA Macro to Streamline Meeting Request Creation

[wbg975]

Hi,

I'm somewhat capable in VBA, but I haven't been able to find enough help to create a macro that'll essentially streamline the creation of calendar/meeting invitations. While it sounds dumb, the vast majority of my time is taken up by having to create meeting invitations. I am praying someone can assist with creating a macro that will pop up a userform where I can enter a few different fields and also select from a drop-down of client names that will automatically populate the recipients email addresses.

So, to keep it simple, let's say I have only 2 clients (client A and B)...

I am hoping for the userform to have a few different fields:

Client: a drop-down that allows me to select between client A and B, and a defined list of email addresses would be associated with each client that would automatically populate the "To" field when selected (adds person1ATclientADOTcom when Client A is selected). The client field would also be associated with a specific email body header and footer.

Email Subject: This would by default include "Conversation with" before anything I enter into the field, so entering in "Person1" would populate the meeting invite with "Conversation with Person1"

Body: Input that would be inserted into the body of the meeting invite between the predetermined header and footer dictated by the Client drop-down

Location: Entry of a phone number to populate said field


After I input everything and click the enter button on the userform a calendar invite would open up and populate, but it would not automatically send (so I can review or add additional recipients before sending).

Any help with this would be tremendous, and I will be eternally grateful.

[gmayor]

The userform is the easy part - see http://www.gmayor.com/Userform.htm

The following code will create a meeting item. You simply need to call the macro from the userform and populate it from the userform fields. To test use

Sub Test()
MyNewMeeting "Strategy Meeting", "Conference Room", "09/26/2014", "14:00", 30, "John Smith", "Bill Jones", "Meeting to discuss planning for Christmas holidays"
End Sub



Option Explicit
Sub MyNewMeeting(strSubject As String, _
strLocation As String, _
strDate As String, _
strTime As String, _
iMinutes As Integer, _
strName1 As String, _
strName2 As String, _
strBodyText As String)

Dim olItem As AppointmentItem
Dim rRequiredAttendee As Recipient
Dim rOptionalAttendee As Recipient
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object
Set olItem = CreateItem(olAppointmentItem)
With olItem
.MeetingStatus = olMeeting
.Subject = strSubject
.Location = strLocation
.Start = strDate & Chr(32) & strTime ' & strAMPM
.Duration = iMinutes
Set rRequiredAttendee = .Recipients.Add(strName1)
rRequiredAttendee.Type = olRequired
Set rOptionalAttendee = .Recipients.Add(strName2)
rOptionalAttendee.Type = olOptional
.Display
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
oRng.Text = strBodyText
End With
Set olItem = Nothing
Set rRequiredAttendee = Nothing
Set rOptionalAttendee = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
End Sub

[dbowlds]

Hello, I know this thread is man years old, but I am hoping someone can help me here.
Graham's code above works GREAT if you have the Outlook reference library added to your project. I have tried but cannot for the life of me figure out how to change this code to conduct late binding.
Can anyone please help me here?
I am using Office 2016.

[gmayor]

The code is run from Outlook so does not need any form of binding. Can I assume then that you want to run it from a different Office application?

In that case only change as follows:



Option Explicit
'This macro requires the code from http://www.rondebruin.nl/win/s1/outlook/openclose.htm
'to open and access Outlook correctly

Sub MyNewMeeting(strSubject As String, _
strLocation As String, _
strDate As String, _
strTime As String, _
iMinutes As Integer, _
strName1 As String, _
strName2 As String, _
strBodyText As String)

Dim olApp As Object
Dim olItem As Object
Dim rRequiredAttendee As Object
Dim rOptionalAttendee As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Set olApp = OutlookApp() ' code from http://www.rondebruin.nl/win/s1/outlook/openclose.htm
Set olItem = olApp.CreateItem(1)
With olItem
.MeetingStatus = 1
.Subject = strSubject
.Location = strLocation
.Start = strDate & Chr(32) & strTime ' & strAMPM
.Duration = iMinutes
Set rRequiredAttendee = .Recipients.Add(strName1)
rRequiredAttendee.Type = 1
Set rOptionalAttendee = .Recipients.Add(strName2)
rOptionalAttendee.Type = 2
.Display
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
Set oRng = wdDoc.Range
oRng.Text = strBodyText
End With
lbl_Exit:
Set olItem = Nothing
Set rRequiredAttendee = Nothing
Set rOptionalAttendee = Nothing
Set olInsp = Nothing
Set wdDoc = Nothing
Set oRng = Nothing
Exit Sub
End Sub

[dbowlds]

Graham!!!!
Thank you, this worked perfectly!
You rock. I am so impressed that you (and others) are still supplying such excellent help to people like me, years later. This speaks highly of your passion for the technology and for helping others solve their problems. I, for one, truly appreciate it.
I see so many people ask for help on this forum and then not even acknowledge the quick and detailed responses they get from people like you. I have posted many questions on this forum and have always received such wonderful help, and help supplied in a way that teaches me how to try to solve some of the issues myself.
I send you a big thank you!!!:hi:
Doug

[gmayor]

It is nice to be appreciated - though, as you say, it is not always obvious.
The passion is for the puzzle rather than the technology, which frequently conspires to get in the way.

[dbowlds]

hahaha, good point about the technology.