VBA: Excel to Outlook Meeting

[irishmanman]

Ready for a puzzle? I'm not fluent in VBA, and am hoping someone can point me rightly.

I'm trying to create a solution that takes the below table and sends an Outlook invitation automatically.

I have 200 rows, and don't update them all. So, it may be worth having a button for each row, so that as I update rows, I can click to create meetings as I go.

A) Will this spreadsheet become unruly?
B) Because I have so many rows, I'm worried about creating a bunch of buttons, and then if I insert a new row or column, it won't be clear which button ties to which row. Is there a cleaner way to do what I want?
C) Is there a resource I can use to create the code?*

*I found a few similar pages out there, but candidly, nothing in the format I needed or that I could understand. Thanks for reading, and I appreciate any guidance you can offer.

Subject	Body	Date	Start Time	End Time	Attendee	Macro
JD to Call Mike	Discuss hard lemonade	10/18/18	9:00 AM	9:01 AM	bob@bob.com	Button 1
Mike to call JD	Discuss whiskey	10/19/18	9:00 AM	9:01 AM	mike@mike.com	Button 2

<!--StartFragment--> <colgroup><col width="82"> <col width="125"> <col width="55"> <col width="61"> <col width="55"> <col width="91"> <col width="75"> </colgroup><tbody>
<!--EndFragment--></tbody>

 

[Puertorekinsam]

I wish I could remember where I got the original code to make this work so I can give credit where credit is due.... but I modified what I have to fit your needs.

The first step is to make sure you have the references needed. In VBA, go to tools--> References--> and turn on the Microsoft Outlook xx.xx library (mine is 15.0)

Now you will need to create a Macro that can send your email. Since each row contains the same type of data, you can do this as a sub routine that needs a variable sent to it as the row number:

Sub Send_Invite_Auto(RowNum As Integer)
    
    Dim olApp As Outlook.Application
    Dim olApt As AppointmentItem
    
    Set olApp = New Outlook.Application                 'Creating Outlook Session
    Set olApt = olApp.CreateItem(olAppointmentItem)     'Creating an Appointment
    
    With olApt
        .Subject = Cells(RowNum, 1)                     'Subject
        .Start = Cells(RowNum, 3) + Cells(RowNum, 4)    'Enter Date + Time here.
        .Recipients.Add (Cells(RowNum, 6))              'Recipient Name, Alias, or any other Attribute.
        .MeetingStatus = olMeeting                      'olAppointmentItem with Meeting status olMeeting
                                                        'becomes a OL Meeting Item.
                                                    
        .Duration = DateDiff("n", Cells(RowNum, 4), Cells(RowNum, 5))                         'In Minutes
        .Body = Cells(RowNum, 2)                        'Body
        .Location = "UPDATE ME"                            'Location of the meeting.
        .Display                                        'for Displaying olMeeting window.
    End With
    
    Application.Wait DateAdd("s", 2, Now)               'waiting for 2 sec to let OL window to display.
    SendKeys "%s", True                                 'Sending Mail.
    Set olApt = Nothing
    
    MsgBox "Invite Sent", vbInformation
    
End Sub

Please note above, you did not say where the Location of the meeting is in your table, so I call it out as UPDATE ME.

Next you need a way to call this sub. You can create a new button for each row, but like you said that gets massive, you could control that with a Class Module, but I don't think that's needed.
I went with a double click of the cell in column 7.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column = 7 Then
    Call Send_Invite_Auto(Target.Row)
End If
End Sub

 

[Worf]

Send keys can be problematic, use the send method of the appointment item object.