Can VBA copy the Date, Start Time, and End Time of an open Outlook calendar item?

[jmkeuning]

I create appointments for groups of people, and I need to propose availability to people outside my organization.

So I create an appointment with my internal users, and then use Scheduling Assistant to find availability. What I want to do it, once I have selected a time slot, have a script that copies the relevant details to the clipboard (Date, Start Time, and End Time). Because I will then paste that information, and select another date/time and copy that, etc until I have a few options. I don't save the calendar item, I just copy the details.

So, that's my question, can VBA copy the Date, Start Time, and End Time of an open Outlook calendar item?

[gmayor]

The short answer is yes, but perhaps not in the way you envisaged. You cannot simply copy and paste. However you can extract the data to he clipboard with a macro and write it to the clipboard, and reverse the process with another macro e.g. as follows:


Option Explicit
'Graham Mayor - https://www.gmayor.com - Last updated - 20 May 2021

Sub CopyApptDates()
Dim olItem As AppointmentItem
Dim sDate As String
Dim myData As DataObject
Dim strClip As String

On Error Resume Next
Select Case Outlook.Application.ActiveWindow.Class
Case olInspector
Set olItem = ActiveInspector.currentItem
Case olExplorer
Set olItem = Application.ActiveExplorer.Selection.Item(1)
End Select
sDate = olItem.Start & vbCr & olItem.End
Set myData = New DataObject
strClip = sDate
MsgBox sDate
myData.SetText strClip
myData.PutInClipboard
lbl_Exit:
Set olItem = Nothing
Set myData = Nothing
Exit Sub
End Sub

Sub PasteApptDates()
Dim olItem As AppointmentItem
Dim sDate As String
Dim myData As DataObject
On Error Resume Next
Select Case Outlook.Application.ActiveWindow.Class
Case olInspector
Set olItem = ActiveInspector.currentItem
Case olExplorer
Set olItem = Application.ActiveExplorer.Selection.Item(1)
End Select
Set myData = New DataObject
myData.GetFromClipboard
sDate = myData.GetText
If UBound(Split(sDate, vbCr)) = 1 Then
olItem.Start = Split(sDate, vbCr)(0)
olItem.End = Split(sDate, vbCr)(1)
Else
MsgBox "The clipboard does not appear to contain the correct data", vbCritical
End If
lbl_Exit:
Set olItem = Nothing
Set myData = Nothing
Exit Sub
End Sub

[jmkeuning]

The short answer is yes, but perhaps not in the way you envisaged. You cannot simply copy and paste. However you can extract the data to he clipboard with a macro and write it to the clipboard, and reverse the process with another macro e.g. as follows:
...

Yes, the first part is what I need! I made a small modification and it is doing exactly what I need. Thank you!


Sub CopyApptDates()
Dim olItem As AppointmentItem
Dim sDate As String
Dim myData As DataObject
Dim strClip As String


On Error Resume Next
Select Case Outlook.Application.ActiveWindow.Class
Case olInspector
Set olItem = ActiveInspector.CurrentItem
Case olExplorer
Set olItem = Application.ActiveExplorer.Selection.Item(1)
End Select
sDate = olItem.Start & " - " & Format(olItem.End, "h:mm AM/PM")
Set myData = New DataObject
strClip = sDate
'MsgBox sDate
myData.SetText strClip
myData.PutInClipboard
lbl_Exit:
Set olItem = Nothing
Set myData = Nothing
Exit Sub
End Sub