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 <iasaf...@gmail.com> 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 <sue...@discussions.microsoft.com> 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 -
|
Similar Threads
