Consulting

Results 1 to 7 of 7

Thread: Outlook VBA Macro to Streamline Meeting Request Creation

  1. #1

    Outlook VBA Macro to Streamline Meeting Request Creation

    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.

  2. #2
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Mar 2018
    Location
    Leesburg
    Posts
    68
    Location
    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.

  4. #4
    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
    Last edited by gmayor; 04-25-2018 at 08:55 PM.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Regular
    Joined
    Mar 2018
    Location
    Leesburg
    Posts
    68
    Location
    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!!!
    Doug

  6. #6
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Regular
    Joined
    Mar 2018
    Location
    Leesburg
    Posts
    68
    Location
    hahaha, good point about the technology.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •