This was my original question on the Excel discussion group:
"I have a couple of rental properties abroad and have a simple Excel
spreadsheet with details of the booking, client, deposit paid, date balance
due etc. Is there any way that this can be linked to Outlook as a task or in
the calendar to alert me on the due date to remind me to chase the client for
payment of their balance?"
It was suggested that I look for a VBA solution - is this something I can do
myself, learn quickly, where do I start, any suggestions? (I did some
programming briefly about 20 years ago!!!!)

If you have some programming experience, it should be a breeze. My
recommendation would be to look at some introductory material for
Visual Basic 6.0, as this will give you both the working basics of the
language, and also basics of programming. Another good choice would
be Excel 200x VBA Programmer's Reference or Excel 200x VBA Power
Programming, both of which start at the beginning and provide VBA-
specific material. Any of these references will also give you a
sufficient introduction to how classes work - critical for VBA,
because just about everything is a class object or a member of one.

Here's an example of a simple spreadsheet application working with
Outlook. Column A contains task names; column B contains due dates.
Placed inside a worksheet's code module, it processes input in cells
B2:B50 (leaving row 1 for headers), and creates an outlook task item.
I put comments to help you figure out what's going on. Try it out:


Private Sub Worksheet_Change(ByVal Target As Range)
' only using range B2:B50
If Not (Intersect(Target, Me.Range("B2:B50")) _
Is Nothing) Then

' these are the objects we're working with
Dim objOutlook As Object
Dim objItem As Object
Dim blnOutlookRunning As Boolean
Dim dtDueDate As Date

' make sure a date is entered
On Error Resume Next
dtDueDate = Target.Value
On Error GoTo 0

' if a date is not entered,
' alert user and clear input
If dtDueDate = 0 Then
Call MsgBox("Enter a date!", vbExclamation)
Application.EnableEvents = False
Target.Clear
Application.EnableEvents = True
Exit Sub
End If

' check to see if outlook is already running
On Error Resume Next
Set objOutlook = GetObject(, _
"Outlook.Application")
blnOutlookRunning = True
On Error GoTo 0

' if outlook is not running, start it
If objOutlook Is Nothing Then
blnOutlookRunning = False
Set objOutlook = _
CreateObject("Outlook.Application")
End If

' create a new item
Set objItem = _
objOutlook.CreateItem(olTaskItem)

' set item properties based on spreadsheet
With objItem
.DueDate = dtDueDate
.Subject = "Get " & Target.Offset(0, -1).Value _
& " done by " & Target.Value
.Save
End With

' quit outlook if it wasn't running
If Not blnOutlookRunning Then
objOutlook.Quit
End If
End If
End Sub



On Nov 7, 10:52 am, sue2uk wrote:
This was my original question on the Excel discussion group:
"I have a couple of rental properties abroad and have a simple Excel
spreadsheet with details of the booking, client, deposit paid, date balance
due etc. Is there any way that this can be linked to Outlook as a task or in
the calendar to alert me on the due date to remind me to chase the client for
payment of their balance?"
It was suggested that I look for a VBA solution - is this something I can do
myself, learn quickly, where do I start, any suggestions? (I did some
programming briefly about 20 years ago!!!!)

Oops! One correction. Replace this:

' create a new item
Set objItem = _
objOutlook.CreateItem(olTaskItem)


with this:

' create a new item
Set objItem = _
objOutlook.CreateItem(3)



On Nov 7, 3:37 pm, ilia wrote:
If you have some programming experience, it should be a breeze. My
recommendation would be to look at some introductory material for
Visual Basic 6.0, as this will give you both the working basics of the
language, and also basics of programming. Another good choice would
be Excel 200x VBA Programmer's Reference or Excel 200x VBA Power
Programming, both of which start at the beginning and provide VBA-
specific material. Any of these references will also give you a
sufficient introduction to how classes work - critical for VBA,
because just about everything is a class object or a member of one.

Here's an example of a simple spreadsheet application working with
Outlook. Column A contains task names; column B contains due dates.
Placed inside a worksheet's code module, it processes input in cells
B2:B50 (leaving row 1 for headers), and creates an outlook task item.
I put comments to help you figure out what's going on. Try it out:

Private Sub Worksheet_Change(ByVal Target As Range)
' only using range B2:B50
If Not (Intersect(Target, Me.Range("B2:B50")) _
Is Nothing) Then

' these are the objects we're working with
Dim objOutlook As Object
Dim objItem As Object
Dim blnOutlookRunning As Boolean
Dim dtDueDate As Date

' make sure a date is entered
On Error Resume Next
dtDueDate = Target.Value
On Error GoTo 0

' if a date is not entered,
' alert user and clear input
If dtDueDate = 0 Then
Call MsgBox("Enter a date!", vbExclamation)
Application.EnableEvents = False
Target.Clear
Application.EnableEvents = True
Exit Sub
End If

' check to see if outlook is already running
On Error Resume Next
Set objOutlook = GetObject(, _
"Outlook.Application")
blnOutlookRunning = True
On Error GoTo 0

' if outlook is not running, start it
If objOutlook Is Nothing Then
blnOutlookRunning = False
Set objOutlook = _
CreateObject("Outlook.Application")
End If

' create a new item
Set objItem = _
objOutlook.CreateItem(olTaskItem)

' set item properties based on spreadsheet
With objItem
.DueDate = dtDueDate
.Subject = "Get " & Target.Offset(0, -1).Value _
& " done by " & Target.Value
.Save
End With

' quit outlook if it wasn't running
If Not blnOutlookRunning Then
objOutlook.Quit
End If
End If
End Sub

On Nov 7, 10:52 am, sue2uk wrote:



This was my original question on the Excel discussion group:
"I have a couple of rental properties abroad and have a simple Excel
spreadsheet with details of the booking, client, deposit paid, date balance
due etc. Is there any way that this can be linked to Outlook as a task or in
the calendar to alert me on the due date to remind me to chase the client for
payment of their balance?"
It was suggested that I look for a VBA solution - is this something I can do
myself, learn quickly, where do I start, any suggestions? (I did some
programming briefly about 20 years ago!!!!)- Hide quoted text -

- Show quoted text -

Awesome! Thank you so much for your time. That's given me the confidence to
get started!

"ilia" wrote:

Oops! One correction. Replace this:

' create a new item
Set objItem = _
objOutlook.CreateItem(olTaskItem)


with this:

' create a new item
Set objItem = _
objOutlook.CreateItem(3)



On Nov 7, 3:37 pm, ilia wrote:
If you have some programming experience, it should be a breeze. My
recommendation would be to look at some introductory material for
Visual Basic 6.0, as this will give you both the working basics of the
language, and also basics of programming. Another good choice would
be Excel 200x VBA Programmer's Reference or Excel 200x VBA Power
Programming, both of which start at the beginning and provide VBA-
specific material. Any of these references will also give you a
sufficient introduction to how classes work - critical for VBA,
because just about everything is a class object or a member of one.

Here's an example of a simple spreadsheet application working with
Outlook. Column A contains task names; column B contains due dates.
Placed inside a worksheet's code module, it processes input in cells
B2:B50 (leaving row 1 for headers), and creates an outlook task item.
I put comments to help you figure out what's going on. Try it out:

Private Sub Worksheet_Change(ByVal Target As Range)
' only using range B2:B50
If Not (Intersect(Target, Me.Range("B2:B50")) _
Is Nothing) Then

' these are the objects we're working with
Dim objOutlook As Object
Dim objItem As Object
Dim blnOutlookRunning As Boolean
Dim dtDueDate As Date

' make sure a date is entered
On Error Resume Next
dtDueDate = Target.Value
On Error GoTo 0

' if a date is not entered,
' alert user and clear input
If dtDueDate = 0 Then
Call MsgBox("Enter a date!", vbExclamation)
Application.EnableEvents = False
Target.Clear
Application.EnableEvents = True
Exit Sub
End If

' check to see if outlook is already running
On Error Resume Next
Set objOutlook = GetObject(, _
"Outlook.Application")
blnOutlookRunning = True
On Error GoTo 0

' if outlook is not running, start it
If objOutlook Is Nothing Then
blnOutlookRunning = False
Set objOutlook = _
CreateObject("Outlook.Application")
End If

' create a new item
Set objItem = _
objOutlook.CreateItem(olTaskItem)

' set item properties based on spreadsheet
With objItem
.DueDate = dtDueDate
.Subject = "Get " & Target.Offset(0, -1).Value _
& " done by " & Target.Value
.Save
End With

' quit outlook if it wasn't running
If Not blnOutlookRunning Then
objOutlook.Quit
End If
End If
End Sub

On Nov 7, 10:52 am, sue2uk wrote:



This was my original question on the Excel discussion group:
"I have a couple of rental properties abroad and have a simple Excel
spreadsheet with details of the booking, client, deposit paid, date balance
due etc. Is there any way that this can be linked to Outlook as a task or in
the calendar to alert me on the due date to remind me to chase the client for
payment of their balance?"
It was suggested that I look for a VBA solution - is this something I can do
myself, learn quickly, where do I start, any suggestions? (I did some
programming briefly about 20 years ago!!!!)- Hide quoted text -

- Show quoted text -