Set variable to cell of current row, column e

[aspiringnerd]

I'm not sure of the syntax, but I have a cell selected. I want to use information in other cells of the same row to create an outlook event, see below:

sub new event()
Dim ol as Outlook.Application
Dik olAp as Outlook.AppointmentItem

Set ol as New Outlook.Application
Set olAp = ok Create item(olAppointmentItem)

With olAp
.subject = (current selected row, 5th column) 'this is the line I'm not sure of
.start = (current selected row, 6th column) 'and this one
.end =  (current selected row, 7th column) 'and this one
.display 

End with
End sub

 

[HongRu]

try.

Sub new_event()
    Dim ol As Outlook.Application
    Dim olAp As Outlook.AppointmentItem
    
    Set ol = New Outlook.Application
    Set olAp = CreateItem(olAppointmentItem)
    
    With olAp
        .Subject = Selection.Columns(5).Value '(current selected row, 5th column) 'this is the line I'm not sure of
        .Start = Selection.Columns(6).Value '(current selected row, 6th column) 'and this one
        .End = Selection.Columns(7).Value '(current selected row, 7th column) 'and this one
        .display
    End With
End Sub

 

[Alex Blakenburg]

It depends a bit on what you have selected when you say "current selection", and whether by say 5th column you mean 5th column relative to what is selected or whether you mean column "E".
If you have a cell in column A selected or the entire row selected then the above code should work.

This would be safer.

    With olAp
        .Subject = Cells(ActiveCell.Row, 5).Value
        .Start = Cells(ActiveCell.Row, 6).Value
        .End = Cells(ActiveCell.Row, 7).Value
        .display
    End With

If you find it clearer and are not using a counter for the Column positions, you can use Columns letters
.Subject = Cells(ActiveCell.Row, "E").Value

 

[aspiringnerd]

It depends a bit on what you have selected when you say "current selection", and whether by say 5th column you mean 5th column relative to what is selected or whether you mean column "E".
If you have a cell in column A selected or the entire row selected then the above code should work.

This would be safer.

    With olAp
        .Subject = Cells(ActiveCell.Row, 5).Value
        .Start = Cells(ActiveCell.Row, 6).Value
        .End = Cells(ActiveCell.Row, 7).Value
        .display
    End With

If you find it clearer and are not using a counter for the Column positions, you can use Columns letters
.Subject = Cells(ActiveCell.Row, "E").Value

This worked perfectly. Do you know how to write to change the calendar path, it currently writes it to my personal but I'd rather write to a shared one.

 

[Alex Blakenburg]

Thanks for letting us know. I'm afraid that don't have much experience working with the Calendar. You might want to start a separate thread so that those with experience in that area can offer their solution.