Can I get Outlook to prompt when dates in Excel files become curre

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can I get Outlook to prompt when dates in Excel files become current? 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 open the workbook to enter or change data then I'd probably do the
programming within Excel, not Outlook. It's easier then to start the code.

This gives you a reference to a single cell (A1) in the active worksheet:

Dim Ws as Excel.Worksheet
Dim Rn as Excel.Range

Set Ws=ActiveSheet
Set Rn=Ws.Range("a1")

Rn.Value gives you the cell's content.

Look at the Range.Offset function for how to move to another cell.

if you are able to read the data, you're ready to create an Outlook item.
Add a reference to Outlook via Tools/References.

Assuming that Outlook is already running, this creates a new appointment:

Dim OL as Outlook.Application
Dim Appt as Outlook.AppointmentItem

Set OL=GetObject(,"Outlook.Application")
Set Appt=OL.CreateItem(olAppointmentItem)

Please use the object brwoser again and see what properties are exposed by
the AppointmentItem. Probably you'll need at least the Subject, StartDate
and Reminder* properties.

Don't forget to call Appt.Save eventually.

These are just a few basics. In excel, you will need at least a new column
that tells you what entries are already added to Outlook. Additionally, if
it's possible that a date changes then you will also need more code to find
the corresponding appointment in Outlook.

So, no - that's not done quickly.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Synchronize Color Categories & Ensure that Every Item Gets Categorized:
<http://www.vboffice.net/product.html?id=2006063&cmd=detail&lang=en&pub=6>

Am Wed, 7 Nov 2007 07:56:07 -0800 schrieb sue2uk:
 
Back
Top