Send outlook task from Excel sheet (including the excelsheet)

**Jop_4444** · 05-08-2012, 01:22 PM

Dear excel programmers,

I have a question about programming excel/vba. I have tried several code combinations that I found while googling but I haven't found one that really does what I want.

How I would like my macro:

-Create and send a task by pressing the macro in the excelsheet
-The task has to be send to someone with an e-mail adres
-The task should contain the excelsheet where the macro is placed (sheet1 is okay)
-Value's of 4 cells should be in the subject
-I would like to put in a text in the task (a message)

At this moment I have a macro working but it's for e-mails, no tasks.
My e-mail macro:

Please Login or Register  to view this content.

I would really appreciate if someone could help me out on this one!

Thanks a lot in advance!

Jop

**jaslake** · 05-08-2012, 02:23 PM

Hi Jop

Take a look at this link...if you have questions, let me know.
http://www.excelforum.com/excel-prog...rom-excel.html

**Jop_4444** · 05-08-2012, 02:35 PM

Hey John,

Thanks for your quick reaction.

I just found out a working macro, I have 1 issue left.
I need a 5 cell values in the subject, perhaps you know how to

current macro:

Sub outlooktask2()
Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim task As String
Dim datedue As String
Dim Recipient As String
Dim Category As String
Dim strMailAddress As String
Dim aRecipient As TaskItem
datedue = ActiveSheet.Range("b2").Value
task = ActiveSheet.Range("a2").Value
Set olApp = CreateObject("Outlook.Application")
Set olTsk = olApp.CreateItem(olTaskItem)
With olTsk
.Assign
.Subject = " I need cell D2 A5 F23 F24 in here " <-------- please look at this row
.Body = "tester"
.Importance = olImportanceNormal
.Display
.Recipients.Add ("mail adres")
.Send
End With

Set olTsk = Nothing
Set olApp = Nothing
End Sub

Thanks in advance,

Jop

**jaslake** · 05-08-2012, 02:51 PM

Hi Jop
You need to add Code Tags to your code.

**Jop_4444** · 05-08-2012, 02:55 PM

Hi John,

I got this now: 1 cell value in subject... But I fail if I try to put more cell values in there.
Maybe you have a suggestion?

current macro:

Sub outlooktask2()
Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim task As String
Dim datedue As String
Dim Recipient As String
Dim Category As String
Dim strMailAddress As String
Dim aRecipient As TaskItem
datedue = ActiveSheet.Range("b2").Value
task = ActiveSheet.Range("a2").Value
Set olApp = CreateObject("Outlook.Application")
Set olTsk = olApp.CreateItem(olTaskItem)
With olTsk
.Assign
.Subject = Worksheets("partial credit debit").Range("D2") & " - "
.Body = "Dear Queryhandler, please handle this request. More information and approval in the excel sheet"
.Importance = olImportanceNormal
.Display
.Recipients.Add ("email")
.Send
End With

Set olTsk = Nothing
Set olApp = Nothing
End Sub

Effective subject (likeI want it, but lacking 4 others values): "D2 value" -

Thanks

**jaslake** · 05-08-2012, 03:04 PM

Hi Jop

Please add Code Tags.

**Jop_4444** · 05-08-2012, 03:06 PM

Hey John,

I got it tackled, thanks for your time.

Jop

**Jop_4444** · 05-08-2012, 05:17 PM

Hi again,

Another question;

I would like to add the excel sheet to the task before sending it out,

Current macro:

Sub outlooktask2()
Dim olApp As Outlook.Application
Dim olTsk As TaskItem
Dim task As String
Dim datedue As String
Dim Recipient As String
Dim Category As String
Dim strMailAddress As String
Dim aRecipient As TaskItem
Set olApp = CreateObject("Outlook.Application")
Set olTsk = olApp.CreateItem(olTaskItem)
With olTsk
.Assign
.Subject = "Subject"
.Body = "Bodytext"
.Importance = olImportanceNormal
.Display
.Recipients.Add ("E-mail")
.Send
End With

Set olTsk = Nothing
Set olApp = Nothing
End Sub

When I look at the e-mail macros they are different when it comes to adding a the excel file. Searched on forums and googled but I couldn't figure it out yet. Perhaps someone could give me hand?

Thanks

**jaslake** · 05-08-2012, 05:26 PM

Jop...in your first post a Moderator ask you to use Code Tags (and graciously added them since you're new on the Forum). I've asked you twice to add Code Tags and you've posted Code yet again WITHOUT Code Tags.

PLEASE ADD CODE TAGS...you can probably expect to get no response until you do.