Creating Outlook Appointment

Question

I have created a basic code wherein it will create an Outlook Appointment via VBA excel UserForm. My Problem, however, is that I am at lost on how to set the 'Start' based on the values of TextBox10 and Textbox11 since it does not accept both values of the textboxes together.

 Dim oAppt As AppointmentItem

    Candidate = TextBox2.Value + " " + TextBox1.Value
    InitialDate = TextBox10.Value
    InitialTime = TextBox11.Value

        Set oAppt = Outlook.Application.CreateItem(olAppointmentItem)

            oAppt.Subject = Candidate
            oAppt.Start = TextBox10.Value + TextBox11.Value
            oAppt.Start = IntialTime
            oAppt.Recipients.Add ("[email protected]")
            oAppt.Save
        Candidate = Sheets("Client Lineup List").Range("F" & lMaxRows + 1).Value

Answer 1

Firstly, use Option Explicit always.

Convert your TextBox values to a date/time first. Example below

Dim myDate As Date
myDate = CDate(TextBox10.Value & " " & TextBox11.Value)

However, in this case further considerations are required - check that a valid date and time is entered otherwise my example code above will error out. One simple part solution is to use a DatePicker instead of TextBox10. There used to be a DateTimePicker in VB, but I am not sure where the current equivalent in VBA is now.

In addition, check how I have concatenated the strings (with a space in between), use "&", not "+" which can have interesting side effects, especially if you have not set Option Explicit and not declared your variables (making them Variant).

Answer 2

You should convert your TextBox values to a date/time.

You could quickly convert a column of text that resembles dates into actual dates with the VBA equivalent of the worksheet's Data ► Text-to-Columns command.

(TextBox10.Value & " " & TextBox11.Value).TextToColumns Destination:=.Cells(1), DataType:=xlFixedWidth, FieldInfo:=Array(0, xlYMDFormat)
.NumberFormat = "yyyy-mm-dd"   'change to any date-based number format you prefer the cells to display

Note: you should check that a valid date and time.

For more information, you can refer to this link:

Excel VBA - Convert Text to Date?