Trimming a cell from user selection to retrieve time & Format it for Outlook Automation

[JMANTN]

Hello,

I've been working on a macro that will create a meeting maker from a roster in excel 2010. I have a workbook where each sheet is a roster for training that multiple people use, for this reason and for learning reasons I'm doing this in VBA.

In my roster I have several columns for different data for each row but for this example this is what I'm having issues with:

Column E Column F Column G Column H
Emails Class Name Date Time
John.Doe TrainingA 11-APR-12 0830-0930
Jane.Doe TrainingA 11-APR-12 0930-1030
Karen.Doe TrainingA 12-APR-12 1330-1430
Lisa.Doe TrainingA 12-APR-12 1330-1430
Meg.Doe TrainingA 12-APR-12 1330-1430
Jack.Doe TrainingA 12-APR-12 1330-1430

As we update the roster by entering the training into their schedule (seperate 3rd party app) we update our roster with the date and time we scheduled them for. Since we typically schedule people in groups of 8 to 16 at a time I've setup a macro that will create an outlook appointment and will populate the class name, course number, location, and names from highlighted cells.

I've set it up so that we would highlight a range of recently entered start/end times (column H) for a group that would share the same session as that's the last thing entered when setting up a group. I have an offset code to collect their names off of the initial selection and insert into my outlook appointment which works perfectly. Example, user selects range of eight 1330-1430 times. I need vba to see just one cell out of that and trim it to be 1330 and set the start time for the appointment.

Dim USelect As Range
  Dim AgentName As Range
  Dim l As Range
  Dim k As String
   
  ‘this takes the selected range and offsets and gets the emails for all class participants
Set USelect = Selection
  For Each AgentName In USelect
      strbody6 = strbody6 & AgentName.Offset(0, -3).Value & vbNewLine
      Next

I'm having trouble taking a single cell from that initial selected range. Once I get one cell trimmed I need to format it and set it to the appointment time.

I've tried the following with no luck:

k = Left(USelect, 4)

&

l = Range(ActiveCell, Selection.End(xlDown)).Value
k = l.Value
  k = Trim(Left(l, 4))

I've got most of this working on my own but need a little help if possible, I can upload a spreadsheet if need be via dropbox as I don't have access to a file sharing service otherwise.

 

[Andrew Poulsom]

Like this?

k = Left(USelect.Cells(1, 1).Value, 4)

 

[JMANTN]

Absolutely brilliant!!! that worked like a charm I've spent 3 hours trying to figure that out!

now I've got to format it for my outlook appointment time like:
"13:30:00" if I understand the documents I've read.

I'll keep at it but will keep this thread open until I or someone else solves this since it was in my initial description.

 

[JMANTN]

didn't know if editing my most recent post was possible as I didn't see a way so sorry for double post.

I'm currently trying:

k = Left(USelect.Cells(1, 1).Value, 4)
k = Format(k, "hh:mm:ss")

however that's setting the time to 00:00:00.

I'm still working on this but seems like I'm close

 

[Andrew Poulsom]

Here's an example of conversion:

Sub Test()
    Const x As String = "1330-1430"
    Dim k As Variant
    Dim t As Date
    k = Val(Left(x, 4))
    t = Evaluate("=TEXT(1330,""00\:00"")+0")
    MsgBox t
End Sub

 

[JMANTN]

Thanks for the help again.

I'm working with your solution however since the 1330-1430 time frame is random I can't set it as a constant. I'm working on adjusting your code to see what I can come up with. Will post back if I find a solution

 

[JMANTN]

Once again thanks for your help! I've got my code working by adjusting the code you posted.

    Dim j As String
    Dim k As Variant
    Dim t As Date
    j = Left(USelect.Cells(1, 1).Value, 4)
    k = Val(Left(j, 4))
    t = Format(k, "00\:00")

for anyone following this I had to change my declarations around a little to follow along with posted examples but this solved my issue. Good thing is there's a lot of things I can use this for besides Outlook automation so thanks again.