1. Here's a thread that needs an answer: Setting default address book
    Dismiss Notice

How to Copy Multi Select Listbox Data to Appointment

Discussion in 'Outlook VBA and Custom Forms' started by George Z, Mar 3, 2017.

  1. George Z

    George Z

    New Member
    With the help of various tutorials in this form I am trying to combine input data from several different input types into an outlook appointment item. With your assistance I have been able to include data from both message boxes and list boxes. How do you also include multiple selections from a multi select list box? I have been able to capture the selections in an array and display them in a message box. Though have not been able to copy the array data into the appointment subject. I have attached both the custom form code, and the macro code. I am at the point where I am unable to identify where to go from here. As always, any assistance is greatly appreciated. I am not sure if I am way off on this or close to a solution.
     

    Attached Files:

  2. Michael Bauer

    Michael Bauer

    Senior Member
    You can join the items of an array with the Join function, for instance:
    .subject = join(array, ", ")
     
  3. George Z

    George Z

    New Member
    Not sure how to apply this as you are stating. I am joining the array and displaying as message. I need the join to be included in the subject with data from other sources. Below is code for the Form. I bolded the lst reference also bolded line from macro code adding data to subject. How do I reference the array in the macro code? I separated my form and macro code in original post. . With the code below array I get type mismatch error. Thanks again for your continued support. Appreciate all the feedback.

    Private Sub ComboBox2_Change()

    End Sub


    Private Sub UserForm_Initialize()
    With ComboBox1
    .AddItem "INSP"
    .AddItem "PM"
    .AddItem "LOAD TEST"
    .AddItem "OR"

    End With

    With ComboBox2
    .AddItem "ANNUAL"
    .AddItem "SEMI-ANNUAL"
    .AddItem "QUARTERLY"
    .AddItem "MONTHLY"
    .AddItem "ADHOC INSP/PM"
    .AddItem "OR"

    End With

    With ComboBox3
    .AddItem "BEATON 19FT"
    .AddItem "BEATON 28FT"
    .AddItem "BEATON 32FT"
    .AddItem "RENTAL"
    .AddItem "CUSTOMER PROVIDED"
    .AddItem "NOT REQUIRED"

    End With

    With ListBox1
    .AddItem "BRIDGE CRANE"
    .AddItem "GANTRY CRANE"
    .AddItem "JIB CRANE"
    .AddItem "MONORAIL CRANE"
    .AddItem "POWERED HOIST"
    .AddItem "CHAINFALL HOIST"
    .AddItem "POWERED HOIST"
    .AddItem "BELOW HOOK DEVICE"
    .AddItem "WINCH"

    End With

    With ListBox2
    .AddItem "OVERHEAD DOOR"
    .AddItem "SPEED DOOR"
    .AddItem "COILING STEEL DOOR"
    .AddItem "IMPACT DOOR"

    End With

    With ListBox3
    .AddItem "VEHICLE LIFT"
    .AddItem "ENGINE LIFT"
    .AddItem "MATERIAL LIFT"
    .AddItem "PALLET JACK"
    .AddItem "JACK STAND"
    .AddItem "MATERIAL LIFT TABLE"
    .AddItem "WALKIE STACKER"
    .AddItem "STRAPPING/BANDING MACHINE"

    End With

    With ListBox4
    .AddItem "BOOM ATTACHMENT"

    End With

    With ListBox5
    .AddItem "AUTO SCRUBBER"

    End With

    With ListBox6
    .AddItem "DOCK LEVELER"

    End With

    With ListBox7
    .AddItem "BAILER/COMPACTOR"

    End With

    With ListBox8
    .AddItem "VRC"
    .AddItem "SELF RETRACTING LIFE LINE"
    .AddItem "TRIPOD"
    .AddItem "HORIZONTAL LIFE LINE"

    End With

    With ListBox9
    .AddItem "TRUCK RESTRAINTS"

    End With

    With ListBox10
    .AddItem "METAL MESH SLINGS"
    .AddItem "SYNTHETIC ROUND SLING"
    .AddItem "SYNTHETIC WEBBING SLING"
    .AddItem "WIRE ROPE SLING"
    .AddItem "ALLOY STEEL CHAIN SLING"

    End With
    End Sub


    Private Sub CommandButton1_Click()

    Dim I As Long
    Dim J As Long
    Dim msg As String
    Dim arrItems() As String

    ReDim arrItems(0 To ListBox1.ColumnCount - 1)
    For J = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(J) Then

    For I = 0 To ListBox1.ColumnCount - 1
    arrItems(I) = ListBox1.Column(I, J)
    Next I
    msg = msg & Join(arrItems, ",") & vbCrLf & vbCrLf


    End If
    Next J
    MsgBox msg


    lstNo = ComboBox1.ListIndex
    lstNo2 = ComboBox2.ListIndex
    lstNo3 = ComboBox3.ListIndex
    'lstNo4 = ListBox1.Selected(I)
    lstNo4 = Join(arrItems, ",")

    ' lstNo5 = ListBox2.ListIndex
    ' lstNo6 = ListBox3.ListIndex
    ' lstNo7 = ListBox4.ListIndex
    ' lstNo8 = ListBox5.ListIndex
    ' lstNo9 = ListBox6.ListIndex
    ' lstNo10 = ListBox7.ListIndex
    ' lstNo11 = ListBox8.ListIndex
    ' lstNo12 = ListBox9.ListIndex
    ' lstNo13 = ListBox10.ListIndex
    Unload Me
    End Sub


    Here is my line from macro

    If objContact.CompanyName <> "" Then
    objAppt.Subject = objContact.CompanyName & ", - INSP/PM - ," & inputdata & " , " & strBody1 & strBody2 & strBody3 & inputdata1 & "," & strBody
     
  4. Michael Bauer

    Michael Bauer

    Senior Member
    You do have the values of the array in the lstNo4 variable, donĀ“t you? Just add it to the subject as you already do with the other variables.
     
Loading...

Share This Page