Send Task to Outlook from Excel

[aarbuckle]

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]

Hello aarbuckle,

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

[DonkeyOte]

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

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

Please Login or Register  to view this content.

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]

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]

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]

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]

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]

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]

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.

[bhofsetz]

I realize this is an old thread but hopefully someone will see this post and have a solution.

I am using the above code to create a task automatically and it works great.

Where I'm having trouble is setting the 'Priority' or Importance of the task.

I can see the Importance property of the task once I make it but think I am getting the syntax wrong to set the importance to either Low, Normal or High.

Any suggestions would be appreciated.

Thank you

[bhofsetz]

Answered my own question:
when setting the importance I was trying to set the value to task.importance = olImportanceHigh (or olImportanceLow or olImportnaceNormal) which wasn't working.

If I set the task.importance = 0 (zero for Low, 1 for Normal, 2 for High) it works perfectly.

[phenixwars]

Hi I realize this is a VERY old thread but it's still open so here's my question. I used the above code from DokeyOte and it worked great with Outlook 2010. Now I'm editing it at home where I have Outlook 2013 and as soon as I try to run the macro it stops at .subject and says Method 'Subject' of object '_TaskItem' failed I have already gone into Tools - References and added the Microsoft Outlook version.

Below is the code exactly as I have it. And also while I'm here just another quick dumb question: How can I add more than one cell from different columns to the subject line? I need several Headings to be included in the subject like Name, Report, etc. Thanks!!!

Please Login or Register  to view this content.

[phenixwars]

Ok I just realized I figured out how to add more cells to a subject because I did it with the body. Just add &. If I'm wrong please correct me. It's been months since I created this file and I forgot how it works.

But still need help with the debug message please! Thanks!!