Access 2003 to Outlook Task

[vvariety]

I have a table that represents expiration of licenses and would like
to set up an outlook task for 5 days prior to the expiration date.
This table has multiple records, I am able to create the first record
as an outlook task but can't seem to create a task for the remining
records in the table. Following is the code that I am using;

Function RnlLtr2TaskProd() 'Sets outlook task for report contents to 5
business days prior to the Xdate of the Item.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim appOutLook As Outlook.Application
Dim taskOutlook As Outlook.TaskItem
Dim i As Long
Dim x As String

Set db = CurrentDb
Set rs = db.OpenRecordset("mtblProducerLetters")
Set appOutLook = CreateObject("outlook.application")
Set taskOutlook = appOutLook.CreateItem(olTaskItem)
x = "In Progress"

rs.MoveFirst

With taskOutlook
For i = 1 To rs.RecordCount
.Subject = rs![VendorName] & " - " & "Producer Renewal
Letter Sent"
.Body = rs![ldesc] & "# " & rs![lno] & " - " & rs![lxdate]
& Chr(13) & Chr(10) & rs![PDesc] & " " & rs![pno] & " - " & rs!
[Pxdate]
.duedate = DateAdd("d", 5, Date)
.Categories = "Producers"
.ReminderPlaySound = False
.Save
Next i
End With
rs.close
db.close

any suggestions would be great.

 

[GeoffG]

You need rs.MoveNext at end of loop.
Also, good practice to:

1. Check recordset isn't empty before running the loop.
2. Get an accurate record count by moving to last, then first
record.

Example:

' Check recordset isn't empty:
If Not rs.BOF and rs.EOF then
' Force accurate record count:
rs.MoveLast
rs.MoveFirst
With taskOutlook
For i = 1 To rs.RecordCount
...
rs.MoveNext
Next I
End With
End If

Alternatively, use Do Loop, similar to this:

Do Until rs.EOF

Loop



Geoff

 

[vvariety]

You need rs.MoveNext at end of loop.
Also, good practice to:

1.  Check recordset isn't empty before running the loop.
2.  Get an accurate record count by moving to last, then first
record.

Example:

'    Check recordset isn't empty:
If Not rs.BOF and rs.EOF then
    ' Force accurate record count:
    rs.MoveLast
    rs.MoveFirst
    With taskOutlook
        For i = 1 To rs.RecordCount
            ...
            rs.MoveNext
        Next I
    End With
End If

Alternatively, use Do Loop, similar to this:

Do Until rs.EOF

Loop

Geoff

I have a table that represents expiration of licenses and would
like
to set up an outlook task for 5 days prior to the expiration
date.
This table has multiple records, I am able to create the first
record
as an outlook task but can't seem to create a task for the
remining
records in the table.  Following is the code that I am using;

Function RnlLtr2TaskProd() 'Sets outlook task for report
contents to 5
business days prior to the Xdate of the Item.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim appOutLook As Outlook.Application
Dim taskOutlook As Outlook.TaskItem
Dim i As Long
Dim x As String

Set db = CurrentDb
Set rs = db.OpenRecordset("mtblProducerLetters")
Set appOutLook = CreateObject("outlook.application")
Set taskOutlook = appOutLook.CreateItem(olTaskItem)
x = "In Progress"

rs.MoveFirst

   With taskOutlook
       For i = 1 To rs.RecordCount
           .Subject = rs![VendorName] & " - " & "Producer
Renewal
Letter Sent"
           .Body = rs![ldesc] & "# " & rs![lno] & " - " &
rs![lxdate]
& Chr(13) & Chr(10) & rs![PDesc] & " " & rs![pno] & " - " & rs!
[Pxdate]
           .duedate = DateAdd("d", 5, Date)
           .Categories = "Producers"
           .ReminderPlaySound = False
           .Save
       Next i
   End With
rs.close
db.close

any suggestions would be great.- Hide quoted text -

- Show quoted text -

Geoff,

Thanks that worked out great using the do until statement

Eileen