How to set up a custom Outlook reminder?

Question

I created an ActiveX button, to which I have assigned a code that will hide/unhide the button based on a cell value.

When the button is unhidden, I want to assign a code to it so when it is clicked, an Outlook appointment item will pop up based on specific ranges from a data tab that is hidden in the final file.

I also have code running that hides/unhides specific columns based on the value in a specific cell.

To elaborate:

The tab with the button is called 'Payment'
The sheet with the ranges for the Outlook Appointment Item (Subject, Body, Start time) is called 'Payment Data' - this sheet/tab will be hidden

I want:

a) In the 'Payment' tab: Have the ActiveX button be hidden if cell E7 states 'To be confirmed' b) In the 'Payment' tab: Have row 5 be unhidden if cell E4 states 'Fail' c) In the 'Payment' tab: Have the ActiveX button pop up an Outlook Appointment item when it is clicked, sourcing the Outlook Appointment information from the 'Payment Data' tab

The first two work. This is the code I have so far:

Private Sub CommandButton1_Click()

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Range("E7") = "To be confirmed" Then
    CommandButton1.Visible = True
Else
    CommandButton1.Visible = False
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E4") = "Fail" Then
    Rows("5:5").EntireRow.Hidden = False
ElseIf Range("E4") = "Pass" Then
    Rows("5:5").EntireRow.Hidden = True
ElseIf Range("E4") = "N/A" Then
    Rows("5:5").EntireRow.Hidden = True
ElseIf Range("E4") = "" Then
    Rows("5:5").EntireRow.Hidden = True
End If

End Sub

Sub SETREM_BUTTON()
Dim OutApp As Outlook.Application
Dim OutMeet As Outlook.AppointmentItem
Set OutApp = Outlook.Application
Set OutMeet = OutApp.CreateItem(olAppointmentItem)
With OutMeet
    .Subject = "Test"
    .RequiredAttendees = "test@test.com"
    .OptionalAttendees = "test2@test.com"
    .Start = #9/30/2021 6:00:00 PM#
    .Duration = 90
    .Importance = olImportanceHigh
    .ReminderMinutesBeforeStart = 15
    .Body = "Reminder" & vbLf & vbLf & "Do XYZ"
    .MeetingStatus = olMeeting
    .Location = "Office"
    .Display
End With

End Sub

For the code where I want the reminder information to be - this works as long as I type in the information in quotation marks - but I want the Subject, Body and time to be sourced from the hidden 'Payment Data' tab.

I tried something similar to the below (I don't need all the options like required attendees, location etc.):

Sub SETREM_BUTTON() 
Dim OutApp As Outlook.Application 
Dim OutMeet As Outlook.AppointmentItem 
Set OutApp = Outlook.Application 
Set OutMeet =OutApp.CreateItem(olAppointmentItem) 
    
With OutMeet With Sheets("Payment Data").Sheet 
    .Subject = .Range ("B34")
    .Value .Start = .Range (B35").Value 
    .Duration = 90 
    .Importance = olImportanceNormal
    .ReminderMinutesBeforeStart = 15 
    .Body = .Range ("B36").Value
    .MeetingStatus = olMeeting 
    .Display
    
    End With 
End With 
    
End Sub

How to set up a custom Outlook reminder?

0 Answers