Excel controlling Outlook

[Don Wells]

    The target cell on a worksheet (Excel 2003), contains a date. When the date is changed (manually), I want to create a new task in Outlook 2003. The task parameters are:
    Subject: Send Fax
    Message: The message goes here.
    Reminder: 1 day before the value in the target cell.

    There is no guarantee that Outlook will be running when the macro is fired, and there is no need to close it afterward.

    Any pointers or snippets of code will be appreciated.

T.I.A.

[HansV]

Here is an example. It should go into the worksheet module of the worksheet containing the target cell. In this example, the target cell is A1; this is specified in the contant strAddress. Change this constant as needed.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Const strAddress = "A1"
  Dim olApp As Object
  Dim olTask As Object
  On Error GoTo ErrHandler
  If Not Intersect(Range(strAddress), Target) Is Nothing Then
    If IsDate(Range(strAddress)) Then
      Set olApp = CreateObject("Outlook.Application")
      Set olTask = olApp.CreateItem(3) ' 3=olTaskItem
      With olTask
        .Subject = "Send Fax"
        .Body = "The message goes here"
        .ReminderTime = Range(strAddress) - 1
        .ReminderSet = True
        .Save
      End With
    End If
  End If

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

ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

The code uses late binding, so you don't have to set a reference.

[Goshute]

Don, setting and sending a Task is much like sending a message, except of course that there are different date fields and other fields. You will need to instance Excel, create the new task item, and fill out the Task fields you need. Start with Ron DeBruin's guide on sending messages from Excel and then modify the code as you need: http://www.rondebruin.nl/sendmail.htm" onclick="window.open(this.href);return false;

[Don Wells]

Thank you Hans
    I am certain that I will be able to tweak your code to my specifics.

[Don Wells]

Thank you Goshute
    I will bookmark Ron DeBruin's guide.

[Don Wells]

Code: Select all

      With olTask
        .Subject = "Send Fax"
        .Body = "The message goes here"
        .ReminderTime = Range(strAddress) - 1
        .ReminderSet = True
        .Save
      End With

Hi Hans
    The .Save command does not work unless Outlook is running. Do you have a snippet that will check to see if Outlook is running, and if not launch it?

T.I.A.

[HansV]

Are you sure? It works for me.

BTW, the code does start a hidden instance of Outlook if it isn't running. If Outlook is already running, it uses the running instance.

[Don Wells]

Are you sure? It works for me.

BTW, the code does start a hidden instance of Outlook if it isn't running. If Outlook is already running, it uses the running instance.

I get the following message;

[HansV]

But if you click Yes, does the task appear in the Tasks folder when you start Outlook?

[Don Wells]

But if you click Yes, does the task appear in the Tasks folder when you start Outlook?

Yes it does.