Consulting

Results 1 to 3 of 3

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

  1. #1

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

    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?

  2. #2
    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
    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
    Quote Originally Posted by gmayor View Post
    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

Posting Permissions

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