Send Task to Outlook from Excel

**aarbuckle** · 08-01-2009, 06:00 PM

Can a macro from Excel VBA create a Task in Outlook?

I would like to have Excel create a task populated with a date in a given cell in Excel.

Basically, click a button and a task is creaed in Outlook based on the date in a given cell.

Is this possible?

Sure could use help with this.

**Leith Ross** · 08-01-2009, 06:37 PM

Hello aarbuckle,

Do you want Excel to open the Outlook Task pane for you with the assigned date?

**DonkeyOte** · 08-01-2009, 06:41 PM

EDIT: Sorry Leith, was putting together below prior to your post...

Yes, this is possible... example code below:

Sub Example()
Dim OLApp As Outlook.Application
Dim OLTk As Outlook.TaskItem
Set OLApp = New Outlook.Application
Set OLTk = OLApp.CreateItem(olTaskItem)
With OLTk
    .Subject = Cells(1, "A")
    .StartDate = Cells(2, "A")
    .DueDate = Cells(2, "A") + 1
    .ReminderTime = .StartDate - 1
    .Body = "This is a Task"
    .Save
End With
Set OLTk = Nothing
Set OLApp = Nothing
End Sub

Where A1 holds Subject and A2 Start Date (& Time)

You need to set a reference to the Outlook Object Library in VBE - > Tools -> References -> scroll through to Microsoft Outlook Object Library... version will depend on your MS Office version.

Might be worth having a read through here for other snippets...

**DonkeyOte** · 08-02-2009, 02:51 AM

Originally Posted by PM from aarbuckle

This works. Can I change the reference cells by changing the (A)? Also per your note, what do I need to look up? You wrote - You need to set a reference to the Outlook Object Library in VBE - > Tools -> References -> scroll through to Microsoft Outlook Object Library... version will depend on your MS Office version.

If it is not set, will this macro not work?

Yes, you can alter the cell references, "A" in the code refers to Column A, the number preceding the letter dictates the row used.

Re: References, yes in this instance early binding is required so if you fail to reference the object library the code will fail.... if you intend to distribute the file to multiple clients, some of which may operate different Office versions to others then you can run into the odd headache on distribution... for ex. if you use 2007 your version is 12.0, if distributed to someone on 2003 then version 12.0 is not available and code will debug (library not found) ... there was a discussion on this some time ago - if interested (remotely) then it might be worth a read: http://www.excelforum.com/excel-prog...eferences.html

On a final note please direct any questions you have in relation to a thread to the thread itself by means of reply (ie not via PMs).

**aarbuckle** · 08-03-2009, 12:24 PM

DonkeyOte,

Question - I have pasted the code in a seperate workbook and it works. Fantastic! But when I pasted it to the workbook I need it in it gives me this error (Type Mismatch). I even copied the page with code into the workbook to make sure it would work, but no luck. Not sure why this is happening. Could you please share your expertise with solving this problem?

**DonkeyOte** · 08-03-2009, 01:21 PM

I'm afraid without seeing the real code / file it's hard to comment... I'm assuming you have referenced the OL Object Library in your main file (not that this would generate Mismatch, rather this would generate User Defined Type Not Defined error)

If you need to mail me the file (given confidentiality) PM me, else dummy the data in the file that is not working (keeping same data types etc) and attach the file here.

**aarbuckle** · 08-03-2009, 02:23 PM

DonkeyOte,

Per the code below;

Sub Example()
Dim OLApp As Outlook.Application
Dim OLTk As Outlook.TaskItem
Set OLApp = New Outlook.Application
Set OLTk = OLApp.CreateItem(olTaskItem)
With OLTk
.Subject = Cells(1, "A")
.StartDate = Cells(2, "A")
.DueDate = Cells(2, "A") + 1
.ReminderTime = .StartDate - 1
.Body = "This is a Task"
.Save
End With
Set OLTk = Nothing
Set OLApp = Nothing
End Sub

It always stops at the -Dim OLApp As Outlook.Application. It works in the workbook that I first pasted it in but when I try to paste it into another workbook it errors out at that line.

Sure could use the help to get this solved.

Thanks

**DonkeyOte** · 08-03-2009, 02:26 PM

First, please adhere to Forum Rules and add CODE tags to your last post:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Second, as mentioned previously, check that you have referenced the Outlook Object Library in the file that's failing - VBE - Tools - References ... Microsoft Outlook Library - version will vary pending Office version (12.0 for 2007, 11.0 for 2003 etc...)

**aarbuckle** · 08-03-2009, 06:43 PM

Sorry about that.

Not trying to break rules.

Let me figure that out and post my information again.

Thanks for the advice.

All,

Please accept my apologies.