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.
-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.
Create Tasks or Events from Excel
-
- 2StarLounger
- Posts: 128
- Joined: 08 Mar 2010, 13:53
- Location: Missouri
Create Tasks or Events from Excel
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
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
-
- 2StarLounger
- Posts: 128
- Joined: 08 Mar 2010, 13:53
- Location: Missouri
Re: Create Tasks or Events from Excel
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
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Create Tasks or Events from Excel
Let's say the first option button is named optTask.
The first part of the procedure could look like this:
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
Hans
-
- 2StarLounger
- Posts: 128
- Joined: 08 Mar 2010, 13:53
- Location: Missouri
Re: Create Tasks or Events from Excel
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.
**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.
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Create Tasks or Events from Excel
1) Instead of
use
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
Set xOutItem = xOutApp.CreateItem(xType)
Code: Select all
Set xOutItem = xOutApp.Session.Folders.Item("account address").Folders.Item("Calendar").Items.Add(xType)
2) Try
Code: Select all
xOutItem.Start = DateValue(datetb) + TimeValue(timetb)
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 128
- Joined: 08 Mar 2010, 13:53
- Location: Missouri
Re: Create Tasks or Events from Excel
Thank you!
Regards,
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison
Oliver
“Just because something doesn't do what you planned it to do doesn't mean it's useless.” -Thomas Edison