Create Tasks or Events from Excel

User avatar
Oliver
2StarLounger
Posts: 128
Joined: 08 Mar 2010, 13:53
Location: Missouri

Create Tasks or Events from Excel

Post by Oliver »

Hello everyone,
I’m currently working on a code to create events in Excel and send it to my outlook calendar or Tasks/Microsoft To Do. And at the same time paste the input into a Sheet for tracking purposes.
userform.PNG

-What I want to do is be able to use the OptionButton to define what I want to use and do with the input data. If 1 is selected create a Task and if 2 Create an event.

Thank you in advance for the guidance.
You do not have the required permissions to view the files attached to this post.
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison

User avatar
Oliver
2StarLounger
Posts: 128
Joined: 08 Mar 2010, 13:53
Location: Missouri

Re: Create Tasks or Events from Excel

Post by Oliver »

This is what I got so far.

Code: Select all

Private Sub createevents_Click()
    Dim i As Long
    Dim xOutApp As Object
    Dim xOutItem As Object
    
    Set xOutApp = CreateObject("Outlook.Application")
    Set xOutItem = xOutApp.createitem(1)
        Debug.Print subjecttb.Value
        xOutItem.Subject = subjecttb.Value
        xOutItem.Location = locationtb.Value
        xOutItem.Start = datetb & timetb.Value
        xOutItem.Duration = durationtb.Value
        If Trim(busytb.Value) = "" Then
            xOutItem.BusyStatus = 2
        Else
            xOutItem.BusyStatus = busytb.Value
        End If
        If remindertb.Value > 0 Then
            xOutItem.ReminderSet = True
            xOutItem.ReminderMinutesBeforeStart = remindertb.Value
        Else
            xOutItem.ReminderSet = False
        End If
        xOutItem.Body = bodytb.Value
        xOutItem.Save
        Set xOutItem = Nothing

       Set xOutApp = Nothing
    
    
    
Dim trow As Long
Dim wsX As Worksheet

Set wsX = ThisWorkbook.Worksheets("Tracker")

trow = wsX.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
wsX.Cells(trow, 1) = subjecttb.Text
wsX.Cells(trow, 2) = locationtb.Text
wsX.Cells(trow, 3) = datetb.Text
wsX.Cells(trow, 4) = timetb.Text
wsX.Cells(trow, 5) = durationtb.Text
wsX.Cells(trow, 6) = busytb.Text
wsX.Cells(trow, 7) = remindertb.Text
wsX.Cells(trow, 8) = bodytb.Text
wsX.Cells(trow, 8) = "Added on " & Date & " " & Time & " by " & UCase(Environ("username"))


End Sub
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create Tasks or Events from Excel

Post by HansV »

Let's say the first option button is named optTask.
The first part of the procedure could look like this:

Code: Select all

Private Sub createevents_Click()
    Dim i As Long
    Dim xOutApp As Object
    Dim xOutItem As Object
    dim xType As Long
    Set xOutApp = CreateObject("Outlook.Application")
    If Me.optTask = True Then
        xType = 3 ' olTaskItem
    Else
        xType = 1 ' olAppointmentItem
    End If
    Set xOutItem = xOutApp.CreateItem(xType)
    ...
Best wishes,
Hans

User avatar
Oliver
2StarLounger
Posts: 128
Joined: 08 Mar 2010, 13:53
Location: Missouri

Re: Create Tasks or Events from Excel

Post by Oliver »

Thank you for that Hans that worked!

**Two more questions, whenever you get a chance.
-What if I wanted to specify which account the event/task goes to. I currently have my personal and my work account.
-Can you look at my code, and see what’s wrong with line 10? I’ve tried multiple variations but an error persists. :hairout:
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create Tasks or Events from Excel

Post by HansV »

1) Instead of

Code: Select all

    Set xOutItem = xOutApp.CreateItem(xType)
use

Code: Select all

    Set xOutItem = xOutApp.Session.Folders.Item("account address").Folders.Item("Calendar").Items.Add(xType)
where "account address" is the name of the account in the navigation pane on the left. It can be a variable if you want.

2) Try

Code: Select all

        xOutItem.Start = DateValue(datetb) + TimeValue(timetb)
Best wishes,
Hans

User avatar
Oliver
2StarLounger
Posts: 128
Joined: 08 Mar 2010, 13:53
Location: Missouri

Re: Create Tasks or Events from Excel

Post by Oliver »

Thank you!
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison