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.

[TABLE="width: 544"]
<!--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>[TR]
[TD="class: xl24, width: 82"]Subject[/TD]
[TD="class: xl24, width: 125"]Body[/TD]
[TD="class: xl24, width: 55"]Date[/TD]
[TD="class: xl24, width: 61"]Start Time[/TD]
[TD="class: xl24, width: 55"]End Time[/TD]
[TD="class: xl24, width: 91"]Attendee[/TD]
[TD="class: xl24, width: 75"]Macro[/TD]
[/TR]
[TR]
[TD="class: xl25"]JD to Call Mike[/TD]
[TD="class: xl25"]Discuss hard lemonade[/TD]
[TD="class: xl26, align: right"]10/18/18[/TD]
[TD="class: xl27, align: right"]9:00 AM[/TD]
[TD="class: xl27, align: right"]9:01 AM[/TD]
[TD="class: xl28"]bob@bob.com[/TD]
[TD="class: xl25"]Button 1[/TD]
[/TR]
[TR]
[TD="class: xl25"]Mike to call JD[/TD]
[TD="class: xl25"]Discuss whiskey[/TD]
[TD="class: xl26, align: right"]10/19/18[/TD]
[TD="class: xl27, align: right"]9:00 AM[/TD]
[TD="class: xl27, align: right"]9:01 AM[/TD]
[TD="class: xl28"]mike@mike.com[/TD]
[TD="class: xl25"]Button 2[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[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.