sending a MS Outlook task item from Excel

[Solar]

I am designing a database with VBA user forms to input the data. This is all designed in an Excel spreadsheet. One of the options is to place a corrective action item in the database. THis item is paired with a responsible party and a due date.

I have not been able to figure out the syntax to create and send a MS Outlook task to the responsible party. I have code working that can send an email (thanks to TommyGun), but I cannot create the task or attach it to the email.

Any help would be appreciated.

Thanks!!

 

[TommyGun]

Try this and see if it works. It will send a task request to the recipient, and wait for their response. If this is not what you needed, then let me know.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SendTask()

<SPAN style="color:#00007F">Dim</SPAN> objOut <SPAN style="color:#00007F">As</SPAN> Outlook.Application
<SPAN style="color:#00007F">Dim</SPAN> objTask <SPAN style="color:#00007F">As</SPAN> Outlook.TaskItem
<SPAN style="color:#00007F">Dim</SPAN> blnCrt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> CreateOutlook
<SPAN style="color:#00007F">Set</SPAN> objOut = GetObject(, "Outlook.Application")

CreateItem:
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

<SPAN style="color:#00007F">Set</SPAN> objTask = objOut.CreateItem(olTaskItem)

<SPAN style="color:#00007F">With</SPAN> objTask
    .Assign
    .Subject = "You need to fix this!"
    .Body = "Please fix this problem by " & Format(Now + 10, "mm/dd/yy")
    .DueDate = <SPAN style="color:#00007F">CDate</SPAN>(Now + 10)
    .Recipients.Add ("youremail@domain.com")
    .Send
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

<SPAN style="color:#00007F">If</SPAN> blnCrt = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> objOut.Quit

<SPAN style="color:#00007F">Set</SPAN> objTask = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Set</SPAN> objOut = <SPAN style="color:#00007F">Nothing</SPAN>

<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

CreateOutlook:
<SPAN style="color:#00007F">Set</SPAN> objOut = CreateObject("Outlook.Application")
blnCrt = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Resume</SPAN> CreateItem

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Andrew Poulsom]

Something like this (reference to Outlook object model required):

Sub Test()

'declare and instanciate Outlook
Dim objOutlook As New Outlook.Application
'declare a task item
Dim objTask As Outlook.TaskItem

'create new task item
Set objTask = objOutlook.Session.GetDefaultFolder(olFolderTasks).Items.Add

'add reciptient name
objTask.Recipients.Add "First Last"

'Add subject
objTask.Subject = "Test"

'Add body
objTask.Body = "Test"

'set due date of tomorrow
objTask.DueDate = Now() + 1

'i don't know why but this step is necessary
objTask.Assign

'send the task
objTask.Send

'save it
objTask.Close olSave
End Sub

 

[TommyGun]

Andrew, Assign is necessary to "give" the task to someone else. Otherwise, you will take ownership of the Task, and you cannot send it to another person. The Assign command allows you to watch the status of the task, but gives the ownership to the recipient.

 

[Andrew Poulsom]

Andrew, Assign is necessary to "give" the task to someone else. Otherwise, you will take ownership of the Task, and you cannot send it to another person. The Assign command allows you to watch the status of the task, but gives the ownership to the recipient.

Oh, that wasn't me wondering. :wink: I just copied the code from a Google search.

 

[TommyGun]

Oh, I see, you do a google search to get all your code! J/K

 

[Andrew Poulsom]

Oh, I see, you do a google search to get all your code! J/K

****, I've been rumbled!

 

[TommyGun]

It's okay, I won't tell anyone...

 

[Solar]

Thanks TommyGun!

One question though, what exactly do you meant that it will wait for the receipient's response?

Thanks

 

[TommyGun]

Lookup tasks, assigning in your Outlook help file, and read the topic "About Task Assignments". It should answer your questions.