More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

share code 15-Nov-2018 09:38

Looking for help with Outlook programming projects — VSTO, add-ins, VBA, custom Outlook forms, etc.? You′ve come to the right place!

NEW! >> Subscribe to this site via RSS. For more RSS options, see the complete list of feeds on our main news page.

Code level: beginner    Code area: Outlook Expert Techniques Printer Friendly Version
Title: Outlook tasks from Excel
Description: I have a problem with the subject. When creating the task in Outlook everything seems fine, except that it does not pass on the Subject to the task, can anybody help? Thanks in advance
Date: 18-Jun-2007  16:05
Code level: beginner
Code area: Outlook Expert Techniques
Posted by: Tini
This message is displayed as VB.NET
 Sub Create_Task_and_email_it_to_recipient()
    'You must set a reference to the Microsoft Outlook Object Library x.x via
    'the Tools | Reference...in the VB-editor.
    

    Dim olApp As Outlook.Application
    Dim olTask As Outlook.TaskItem
    Dim Email As String
    Dim Recipient_Email As String
    Dim URL As String
    Dim x As Double
    Dim Delegate As Recipient
    Dim Subject As String
    Dim Recipient As String
    Dim Sender As String
    Dim Body As String
    Dim wbBook As Workbook
    Dim wsSheet2 As Worksheet
    Dim info_row As Integer
    
    Set wbBook = ThisWorkbook
    Set wsMain = wbBook.Worksheets("Sheet2")

    With wsSheet2
    
        info_row = 0
        
        For line = ActiveCell.Row To 0 Step -1
            If Not IsEmpty(Cells(line, 6).Value) Then
                info_row = line
                Exit For
            End If
        Next line

        
        Subject = "Your tasks for project " & Cells(info_row, 4)
        Body = Cells(info_row, 14) & " Please contact " & Cells(info_row, 6) & " if you have any questions."
    
        Owner = Cells(info_row, 6)
        ' get the email address
        ' Status = Cells(info_row, 3)
        Recipient = Cells(info_row, 15)
        Recipient_Email = Cells(info_row, 16)
        
        Sender = Cells(info_row, 6)
        DueDate = Cells(info_row, 17)
    
        If IsEmpty(Body) Or IsEmpty(Email) Or IsEmpty(Recipient) Then
            MsgBox Body + Email
            Exit Sub
        End If
    
    End With

    'On Error GoTo Error_Handling
    Set olApp = GetObject(, "Outlook.Application")
    Set olTask = olApp.CreateItem(olTaskItem)

    Application.ScreenUpdating = False
    
    With olTask
        .Body = Body
        .StartDate = Now
        .DueDate = DueDate
        .Status = olTaskNotStarted
        .Subject = Subject
        .Importance = olImportanceHigh
        .ReminderPlaySound = True
        .Assign = Recepient
        .Owner = Owner
        .ReminderSet = True
        .ReminderTime = DateAdd("h", 1, Now)
        Set Delegate = .Recipients.Add(Recipient_Email)
        Delegate.Resolve
        If Delegate.Resolved Then
            .Save
            .Display
            .Send
        End If
        
       
    End With

    
    ' Application.ScreenUpdating = True
    ' MsgBox "The task added into Outlook Tasklist and addressed to Recipient via email successfully.", vbInformation


Exit_Here:
    Set olTask = Nothing
    Set olApp = Nothing
    Exit Sub

Error_Handling:
    If Err.Number = 429 And olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: "
        Resume Exit_Here
    End If

End Sub
All 10comments
Page [ 1  
  19-Jun-2007  10:53   
I don't see any obvious problems. At the point where this statement executes:

    .Subject = Subject

does the Subject variable have the desired value?
  19-Jun-2007  20:10   
no it seems that the assignment does not work, the desired value is not passed on to the variable
meaning that the second part = Subject receives the correct values from the Excel sheet but the .Subject just does not receive this value
  19-Jun-2007  22:33   
So you're saying that the Subject variable *does* have the correct value? You've checked?

Is there some other statement that's setting the Subject property of the message somewhere?
  20-Jun-2007  22:20   
Subject does have the correct value. If I run the script in the debugger, when I hover the mouse on the variable, I can see that the value of Subject is set, but the assignment to .Subject doesn't work. If I do the same check to the other assigned variable eg. DueDate, everything is working correctly - the DueDate is passed on correctly to the Outlook task.

I also tried to replace the Subject variable with a string to make sure that it gets passed on to the .subject, but still, the .subject stayed blank and equally the subject line in the Outlook task.
  21-Jun-2007  06:44   
If you check olTask.Subject right after you assign it, is it blank? I'm really quite baffled by this behavior.
  21-Jun-2007  14:28   
yes it is blank right after I assign it, I tried removing and reinserting it several times, it still does not work :-(
  21-Jun-2007  14:38   
Maybe the problem is related to this statemtn:

        .Assign = Recepient

Assign is a method, not a property.
  27-Jun-2007  11:26   
Thanks a lot that solved the problem!!!
  12-Dec-2007  09:43   
Why not simply name the Excel range and Import it to tasks in Outlook? This seems a rather convoluted way to do something you can just do with the menu commands
  12-Dec-2007  12:11   
Esperanza, there are many reasons to import programmatically either as part of a larger application or because the menu command does not support importing to custom fields or a custom form. In the current code sample, the goal is to create task requests and send them to individuals, something not possible at all with the menu's Import and Export command.
Page [ 1