Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
I am using the following to succefully create tasks within Outlook but
only to the default Task folder. How can I specify to save the task to a particular task folder? Sub AddTasksToOutlook() '!! Reference to Outlook object library required !! Dim olTask As Outlook.TaskItem Dim olApp As Outlook.Application Dim lngRow As Long, shtSource As Worksheet 'Get reference to MS Outlook On Error Resume Next Set olApp = GetObject(, "Outlook.Application") If Err.Number < 0 Then Set olApp = CreateObject("Outlook.Application") End If On Error GoTo 0 Set shtSource = ActiveSheet 'Enter number of orders to process in A1 For lngRow = 2 To shtSource.Cells(1, 1).Value + 1 Set olTask = olApp.CreateItem(olTaskItem) With olTask .Subject = shtSource.Cells(lngRow, 2) + " " + shtSource.Cells(1, 3) .DueDate = CDate(DateValue(shtSource.Cells(lngRow, 3))) ' + shtSource.Cells(lngRow, 6)) .Categories = "Purchase Orders" .Save End With Next lngRow End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
On Oct 3, 12:55 pm, Finny388 wrote:
I am using the following to succefully create tasks within Outlook but only to the default Task folder. How can I specify to save the task to a particular task folder? Sub AddTasksToOutlook() '!! Reference to Outlook object library required !! Dim olTask As Outlook.TaskItem Dim olApp As Outlook.Application Dim lngRow As Long, shtSource As Worksheet 'Get reference to MS Outlook On Error Resume Next Set olApp = GetObject(, "Outlook.Application") If Err.Number < 0 Then Set olApp = CreateObject("Outlook.Application") End If On Error GoTo 0 Set shtSource = ActiveSheet 'Enter number of orders to process in A1 For lngRow = 2 To shtSource.Cells(1, 1).Value + 1 Set olTask = olApp.CreateItem(olTaskItem) With olTask .Subject = shtSource.Cells(lngRow, 2) + " " + shtSource.Cells(1, 3) .DueDate = CDate(DateValue(shtSource.Cells(lngRow, 3))) ' + shtSource.Cells(lngRow, 6)) .Categories = "Purchase Orders" .Save End With Next lngRow End Sub ^ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
On Oct 3, 3:45 pm, Finny388 wrote:
On Oct 3, 12:55 pm, Finny388 wrote: I am using the following to succefully create tasks within Outlook but only to the default Task folder. How can I specify to save the task to a particular task folder? Sub AddTasksToOutlook() '!! Reference to Outlook object library required !! Dim olTask As Outlook.TaskItem Dim olApp As Outlook.Application Dim lngRow As Long, shtSource As Worksheet 'Get reference to MS Outlook On Error Resume Next Set olApp = GetObject(, "Outlook.Application") If Err.Number < 0 Then Set olApp = CreateObject("Outlook.Application") End If On Error GoTo 0 Set shtSource = ActiveSheet 'Enter number of orders to process in A1 For lngRow = 2 To shtSource.Cells(1, 1).Value + 1 Set olTask = olApp.CreateItem(olTaskItem) With olTask .Subject = shtSource.Cells(lngRow, 2) + " " + shtSource.Cells(1, 3) .DueDate = CDate(DateValue(shtSource.Cells(lngRow, 3))) ' + shtSource.Cells(lngRow, 6)) .Categories = "Purchase Orders" .Save End With Next lngRow End Sub ^ anyone know how to do this? It would be a great help. tia |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
Rather than use
Set olTask = olApp.CreateItem(olTaskItem) you want to get the folder and then objFolders.Items.Add Below is an example of a sub that you should be able to modify. This is adding tasks to a subfolder of my 'Tasks' folder that I've named "Very Important" ________________________________ Sub LoadTasks() Const olFolderTasks = 13 Dim r As Integer Dim x As Integer r = Range("A65536").End(xlUp).Row Set ol = CreateObject("Outlook.Application") Set ns = ol.GetNamespace("MAPI") Set olFolderDF = ns.GetDefaultFolder(olFolderTasks) Set olFolder = olFolderDF.Folders("Very Important") For x = 1 To r Set taskItem = olFolder.Items.Add With taskItem .Subject = Sheets("Sheet1").Cells(x, 1).Value .DueDate = Sheets("Sheet1").Cells(x, 2).Value .Save End With Next x Set taskItem = Nothing Set ns = Nothing Set ol = Nothing End Sub _________________________________ Steve "Finny388" wrote in message oups.com... On Oct 3, 3:45 pm, Finny388 wrote: On Oct 3, 12:55 pm, Finny388 wrote: I am using the following to succefully create tasks within Outlook but only to the default Task folder. How can I specify to save the task to a particular task folder? Sub AddTasksToOutlook() '!! Reference to Outlook object library required !! Dim olTask As Outlook.TaskItem Dim olApp As Outlook.Application Dim lngRow As Long, shtSource As Worksheet 'Get reference to MS Outlook On Error Resume Next Set olApp = GetObject(, "Outlook.Application") If Err.Number < 0 Then Set olApp = CreateObject("Outlook.Application") End If On Error GoTo 0 Set shtSource = ActiveSheet 'Enter number of orders to process in A1 For lngRow = 2 To shtSource.Cells(1, 1).Value + 1 Set olTask = olApp.CreateItem(olTaskItem) With olTask .Subject = shtSource.Cells(lngRow, 2) + " " + shtSource.Cells(1, 3) .DueDate = CDate(DateValue(shtSource.Cells(lngRow, 3))) ' + shtSource.Cells(lngRow, 6)) .Categories = "Purchase Orders" .Save End With Next lngRow End Sub ^ anyone know how to do this? It would be a great help. tia |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
On Oct 3, 5:55 pm, "Steve Yandl" wrote:
Rather than use Set olTask = olApp.CreateItem(olTaskItem) you want to get the folder and then objFolders.Items.Add Below is an example of a sub that you should be able to modify. This is adding tasks to a subfolder of my 'Tasks' folder that I've named "Very Important" ________________________________ Sub LoadTasks() Const olFolderTasks = 13 Dim r As Integer Dim x As Integer r = Range("A65536").End(xlUp).Row Set ol = CreateObject("Outlook.Application") Set ns = ol.GetNamespace("MAPI") Set olFolderDF = ns.GetDefaultFolder(olFolderTasks) Set olFolder = olFolderDF.Folders("Very Important") For x = 1 To r Set taskItem = olFolder.Items.Add With taskItem .Subject = Sheets("Sheet1").Cells(x, 1).Value .DueDate = Sheets("Sheet1").Cells(x, 2).Value .Save End With Next x Set taskItem = Nothing Set ns = Nothing Set ol = Nothing End Sub _________________________________ Steve "Finny388" wrote in message oups.com... On Oct 3, 3:45 pm, Finny388 wrote: On Oct 3, 12:55 pm, Finny388 wrote: I am using the following to succefully create tasks within Outlook but only to the default Task folder. How can I specify to save the task to a particular task folder? Sub AddTasksToOutlook() '!! Reference to Outlook object library required !! Dim olTask As Outlook.TaskItem Dim olApp As Outlook.Application Dim lngRow As Long, shtSource As Worksheet 'Get reference to MS Outlook On Error Resume Next Set olApp = GetObject(, "Outlook.Application") If Err.Number < 0 Then Set olApp = CreateObject("Outlook.Application") End If On Error GoTo 0 Set shtSource = ActiveSheet 'Enter number of orders to process in A1 For lngRow = 2 To shtSource.Cells(1, 1).Value + 1 Set olTask = olApp.CreateItem(olTaskItem) With olTask .Subject = shtSource.Cells(lngRow, 2) + " " + shtSource.Cells(1, 3) .DueDate = CDate(DateValue(shtSource.Cells(lngRow, 3))) ' + shtSource.Cells(lngRow, 6)) .Categories = "Purchase Orders" .Save End With Next lngRow End Sub ^ anyone know how to do this? It would be a great help. tia Thanks Steve, It wouldn't quite work. For some reason it is treating olFolder in Set olFolder = olFolderDF.Folders("Order Tracking") as constant saying: Assignment to Constant Not Permitted. Why would it treat this as a constant? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
On Oct 3, 6:42 pm, Finny388 wrote:
On Oct 3, 5:55 pm, "Steve Yandl" wrote: Rather than use Set olTask = olApp.CreateItem(olTaskItem) you want to get the folder and then objFolders.Items.Add Below is an example of a sub that you should be able to modify. This is adding tasks to a subfolder of my 'Tasks' folder that I've named "Very Important" ________________________________ Sub LoadTasks() Const olFolderTasks = 13 Dim r As Integer Dim x As Integer r = Range("A65536").End(xlUp).Row Set ol = CreateObject("Outlook.Application") Set ns = ol.GetNamespace("MAPI") Set olFolderDF = ns.GetDefaultFolder(olFolderTasks) Set olFolder = olFolderDF.Folders("Very Important") For x = 1 To r Set taskItem = olFolder.Items.Add With taskItem .Subject = Sheets("Sheet1").Cells(x, 1).Value .DueDate = Sheets("Sheet1").Cells(x, 2).Value .Save End With Next x Set taskItem = Nothing Set ns = Nothing Set ol = Nothing End Sub _________________________________ Steve "Finny388" wrote in message roups.com... On Oct 3, 3:45 pm, Finny388 wrote: On Oct 3, 12:55 pm, Finny388 wrote: I am using the following to succefully create tasks within Outlook but only to the default Task folder. How can I specify to save the task to a particular task folder? Sub AddTasksToOutlook() '!! Reference to Outlook object library required !! Dim olTask As Outlook.TaskItem Dim olApp As Outlook.Application Dim lngRow As Long, shtSource As Worksheet 'Get reference to MS Outlook On Error Resume Next Set olApp = GetObject(, "Outlook.Application") If Err.Number < 0 Then Set olApp = CreateObject("Outlook.Application") End If On Error GoTo 0 Set shtSource = ActiveSheet 'Enter number of orders to process in A1 For lngRow = 2 To shtSource.Cells(1, 1).Value + 1 Set olTask = olApp.CreateItem(olTaskItem) With olTask .Subject = shtSource.Cells(lngRow, 2) + " " + shtSource.Cells(1, 3) .DueDate = CDate(DateValue(shtSource.Cells(lngRow, 3))) ' + shtSource.Cells(lngRow, 6)) .Categories = "Purchase Orders" .Save End With Next lngRow End Sub ^ anyone know how to do this? It would be a great help. tia Thanks Steve, It wouldn't quite work. For some reason it is treating olFolder in Set olFolder = olFolderDF.Folders("Order Tracking") as constant saying: Assignment to Constant Not Permitted. Why would it treat this as a constant? I declared it as a mapifolder and the error went away. Then I declared the taskitem b/c it had the same error. No my error is 424 Object Required at - Set NamSp = ol.GetNamespace("MAPI") hmm... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
On Oct 3, 6:51 pm, Finny388 wrote:
On Oct 3, 6:42 pm, Finny388 wrote: On Oct 3, 5:55 pm, "Steve Yandl" wrote: Rather than use Set olTask = olApp.CreateItem(olTaskItem) you want to get the folder and then objFolders.Items.Add Below is an example of a sub that you should be able to modify. This is adding tasks to a subfolder of my 'Tasks' folder that I've named "Very Important" ________________________________ Sub LoadTasks() Const olFolderTasks = 13 Dim r As Integer Dim x As Integer r = Range("A65536").End(xlUp).Row Set ol = CreateObject("Outlook.Application") Set ns = ol.GetNamespace("MAPI") Set olFolderDF = ns.GetDefaultFolder(olFolderTasks) Set olFolder = olFolderDF.Folders("Very Important") For x = 1 To r Set taskItem = olFolder.Items.Add With taskItem .Subject = Sheets("Sheet1").Cells(x, 1).Value .DueDate = Sheets("Sheet1").Cells(x, 2).Value .Save End With Next x Set taskItem = Nothing Set ns = Nothing Set ol = Nothing End Sub _________________________________ Steve "Finny388" wrote in message roups.com... On Oct 3, 3:45 pm, Finny388 wrote: On Oct 3, 12:55 pm, Finny388 wrote: I am using the following to succefully create tasks within Outlook but only to the default Task folder. How can I specify to save the task to a particular task folder? Sub AddTasksToOutlook() '!! Reference to Outlook object library required !! Dim olTask As Outlook.TaskItem Dim olApp As Outlook.Application Dim lngRow As Long, shtSource As Worksheet 'Get reference to MS Outlook On Error Resume Next Set olApp = GetObject(, "Outlook.Application") If Err.Number < 0 Then Set olApp = CreateObject("Outlook.Application") End If On Error GoTo 0 Set shtSource = ActiveSheet 'Enter number of orders to process in A1 For lngRow = 2 To shtSource.Cells(1, 1).Value + 1 Set olTask = olApp.CreateItem(olTaskItem) With olTask .Subject = shtSource.Cells(lngRow, 2) + " " + shtSource.Cells(1, 3) .DueDate = CDate(DateValue(shtSource.Cells(lngRow, 3))) ' + shtSource.Cells(lngRow, 6)) .Categories = "Purchase Orders" .Save End With Next lngRow End Sub ^ anyone know how to do this? It would be a great help. tia Thanks Steve, It wouldn't quite work. For some reason it is treating olFolder in Set olFolder = olFolderDF.Folders("Order Tracking") as constant saying: Assignment to Constant Not Permitted. Why would it treat this as a constant? I declared it as a mapifolder and the error went away. Then I declared the taskitem b/c it had the same error. No my error is 424 Object Required at - Set NamSp = ol.GetNamespace("MAPI") hmm... IT WORKED! IT WORKED! tHANK You Steve! I just had a naming conflict with the 424 thing. phew. One other thing if you happen to know: How can I check that there isn't already a task with the exact same subject or subject and category in that folder already so that duplicates aren't created? thanks again |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
Poor choice of object names on my part but the subroutine worked on my
system without error. I just modified an old sub to handle the subfolder assignment. Try declaring the TaskItem as a varient and see if that works. Steve "Finny388" wrote in message ps.com... On Oct 3, 6:42 pm, Finny388 wrote: On Oct 3, 5:55 pm, "Steve Yandl" wrote: Rather than use Set olTask = olApp.CreateItem(olTaskItem) you want to get the folder and then objFolders.Items.Add Below is an example of a sub that you should be able to modify. This is adding tasks to a subfolder of my 'Tasks' folder that I've named "Very Important" ________________________________ Sub LoadTasks() Const olFolderTasks = 13 Dim r As Integer Dim x As Integer r = Range("A65536").End(xlUp).Row Set ol = CreateObject("Outlook.Application") Set ns = ol.GetNamespace("MAPI") Set olFolderDF = ns.GetDefaultFolder(olFolderTasks) Set olFolder = olFolderDF.Folders("Very Important") For x = 1 To r Set taskItem = olFolder.Items.Add With taskItem .Subject = Sheets("Sheet1").Cells(x, 1).Value .DueDate = Sheets("Sheet1").Cells(x, 2).Value .Save End With Next x Set taskItem = Nothing Set ns = Nothing Set ol = Nothing End Sub _________________________________ Steve "Finny388" wrote in message roups.com... On Oct 3, 3:45 pm, Finny388 wrote: On Oct 3, 12:55 pm, Finny388 wrote: I am using the following to succefully create tasks within Outlook but only to the default Task folder. How can I specify to save the task to a particular task folder? Sub AddTasksToOutlook() '!! Reference to Outlook object library required !! Dim olTask As Outlook.TaskItem Dim olApp As Outlook.Application Dim lngRow As Long, shtSource As Worksheet 'Get reference to MS Outlook On Error Resume Next Set olApp = GetObject(, "Outlook.Application") If Err.Number < 0 Then Set olApp = CreateObject("Outlook.Application") End If On Error GoTo 0 Set shtSource = ActiveSheet 'Enter number of orders to process in A1 For lngRow = 2 To shtSource.Cells(1, 1).Value + 1 Set olTask = olApp.CreateItem(olTaskItem) With olTask .Subject = shtSource.Cells(lngRow, 2) + " " + shtSource.Cells(1, 3) .DueDate = CDate(DateValue(shtSource.Cells(lngRow, 3))) ' + shtSource.Cells(lngRow, 6)) .Categories = "Purchase Orders" .Save End With Next lngRow End Sub ^ anyone know how to do this? It would be a great help. tia Thanks Steve, It wouldn't quite work. For some reason it is treating olFolder in Set olFolder = olFolderDF.Folders("Order Tracking") as constant saying: Assignment to Constant Not Permitted. Why would it treat this as a constant? I declared it as a mapifolder and the error went away. Then I declared the taskitem b/c it had the same error. No my error is 424 Object Required at - Set NamSp = ol.GetNamespace("MAPI") hmm... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
You can do a
For Each itemToCheck In objFolderTarget.Items If TypeName(itemToCheck) = "TaskItem" Then ' Check itemToCheck.Subject etc. here. End If Next itemToCheck You might look in help for information on the "Scripting.Dictionary" object which will let you create a special type collection with what gets returned in the loop above and then rapidly check new additions against the existing set. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
On Oct 3, 7:15 pm, "Steve Yandl" wrote:
You can do a For Each itemToCheck In objFolderTarget.Items If TypeName(itemToCheck) = "TaskItem" Then ' Check itemToCheck.Subject etc. here. End If Next itemToCheck You might look in help for information on the "Scripting.Dictionary" object which will let you create a special type collection with what gets returned in the loop above and then rapidly check new additions against the existing set. Steve, you've just made my goal of tracking mini tasks without pulling my hair out an absolute breeze. Since MS refuses to create composite or hierarchical tasks available, this allows such management. Thank you so much. It works like a charm. btw, I couldn't find Scripting.Dictionary in the help. is it part of Outlook, Excel, or Office? I'll check MSDN Cheers. thanks again |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Specify an Outlook folder for already successful Task creation procedure
Scripting.Dictionary is part of the scripting runtimes. It is similar to an
array but has some characteristics that make it a lot simpler to use than an array when you want to avoid duplicate values. This link will probably break because of its length but this short article describes a vbs that uses the scripting dictionary object. I think it will give you enough info to be able to incorporate the object into your VBA projects. http://www.microsoft.com/technet/scr...6/hey1027.mspx Steve "Finny388" wrote in message ups.com... On Oct 3, 7:15 pm, "Steve Yandl" wrote: You can do a For Each itemToCheck In objFolderTarget.Items If TypeName(itemToCheck) = "TaskItem" Then ' Check itemToCheck.Subject etc. here. End If Next itemToCheck You might look in help for information on the "Scripting.Dictionary" object which will let you create a special type collection with what gets returned in the loop above and then rapidly check new additions against the existing set. Steve, you've just made my goal of tracking mini tasks without pulling my hair out an absolute breeze. Since MS refuses to create composite or hierarchical tasks available, this allows such management. Thank you so much. It works like a charm. btw, I couldn't find Scripting.Dictionary in the help. is it part of Outlook, Excel, or Office? I'll check MSDN Cheers. thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Perform task on multiple sheets in a folder | Excel Programming | |||
Sending a task from Excel to Outlook | Excel Programming | |||
Creating a task in outlook | Excel Programming | |||
OutLook Task and Appointment | Excel Programming | |||
linking an excel document to my task folder in outlook | Excel Discussion (Misc queries) |