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.
Excel controlling Outlook
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Excel controlling Outlook
Regards
Don
Don
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel controlling Outlook
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.
The code uses late binding, so you don't have to set a reference.
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 397
- Joined: 24 Jan 2010, 19:43
- Location: Salt Lake City, Utah, USA
Re: Excel controlling Outlook
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;
Goshute
I float in liquid gardens
I float in liquid gardens
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Excel controlling Outlook
Thank you Hans
I am certain that I will be able to tweak your code to my specifics.
I am certain that I will be able to tweak your code to my specifics.
Regards
Don
Don
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Excel controlling Outlook
Hi HansHansV wrote:Code: Select all
With olTask .Subject = "Send Fax" .Body = "The message goes here" .ReminderTime = Range(strAddress) - 1 .ReminderSet = True .Save End With
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.
Regards
Don
Don
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel controlling Outlook
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.
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Excel controlling Outlook
I get the following message;HansV wrote: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.
You do not have the required permissions to view the files attached to this post.
Regards
Don
Don
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Excel controlling Outlook
But if you click Yes, does the task appear in the Tasks folder when you start Outlook?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 689
- Joined: 27 Jan 2010, 16:45
- Location: Ottawa, Ontario, Canada
Re: Excel controlling Outlook
Yes it does.HansV wrote:But if you click Yes, does the task appear in the Tasks folder when you start Outlook?
Regards
Don
Don