add multiple records in one outlook reminder (from microsoft access database)

[deamond]

is it possible to add multiple records in one Outlook Reminder? I need to set an Outlook task reminder containing list of workers who need to extend their working contracts three months before it reach its limit date.

This reminder will collectively pick the workers who need to extend their contracts and be appear in the 1st day of certain month (approximately three months before their contracts end).

I was about to set one reminder for each worker, but it turned out confusing as we have so many workers in the record.

What should I do with this? I'm thinking about using Record-set, but I have no idea how to put it on my code.

Thanks!

[zmbd]

Sorry,
Records in Outlook? Although it is basically a front-end to a database, one doesn't usually mess with the underlying database without risking corruption of either the exchange-server or the PST.

Normally one would create an meeting, or a task and add the users to that object or one can create one item in the calendar and forward that item to multiple recipients.

I personally have set up groups in my contacts and then create a task or a meeting and add the group to the item or forward the calendar item to the group.

Can you be a lot more detailed about what you are attempting to do and how you are attempting to do this?

[deamond]

@zmbd hello, thanks for your reply.
I'm working on an access database, creating an application with reminder function. I use outlook as the reminder tools, so I put some code on Access to set outlook task reminder and it will pop up when the schedule meets its due date.
Basically, I have a table containing these fields : Employee_Number, Employee_Name, and Task_Due.
At first, I set one reminder for each record. But it turns out confusing since there are so many records in the table. My supervisor suggested me to group the reminder per month, so the outlook reminder only appear in the first day of each month. My idea is to make a single outlook reminder containing list of employees who have task due in the same month, so there will be no excessive load of reminders on the first day of the month.

Can I do it?

*I'm sorry for my broken English*

[jforbes]

That sounds reasonable. If you would post a copy of your code, we might be able to tell you what changes might make it work for you.

[zmbd]

First thing to do, is read thru our Application Automation Insights Article to get a general feel about how to work with Outlook.

Now take a look here:
How to: Create an Appointment as a Meeting on the Calendar

You will do this from within access using your recordset. If you early-bind as given in the fist link (add the reference to Outlook) then you should have the constants such a olAppointmentItem; however, you might find it better in the long run to late-bind your code which requires a bit more effort but can save you some headaches when distributing the application.

See how you progress and post your code ... please read thru the > Before Posting (VBA or SQL) Code and our own VBA Insights articles for some tips on troubleshooting VBA.

[deamond]

here's some sneak-peek of the code I wrote before :

Private Sub Expired_AfterUpdate()
Dim outLookApp As Outlook.Application
Dim outLookTask As Outlook.TaskItem
 
Set outLookApp = CreateObject("outlook.application")
Set outLookTask = outLookApp.CreateItem(olTaskItem)
countMonth = MonthName(Month(Task_Due))
countYear = Year(Task_Due)
 
With outLookTask
    .Subject = "Task Alert!!"
    .Body = "Task alert for : " & Me.Employee_Name & ". Please submit your task prior to this following due date : " & countMonth & " " & countYear & ". Thank You!"
    .ReminderSet = True
    .Duedate = Me.Task_Due
    .Assign
    .ReminderTime = DateSerial(Year(Task_Due), (Month(Task_Due)) - 6, 1)
    .Save
 
End With
 MsgBox "Task has been set in Outlook succesfully", vbInformation, "Set Task Confirmed"
End Sub

it works well and the reminder appeared.. is there anything I have to improve?
can I have more information about using recordset in this kind of case? I never use recordset before..
thank you...

[zmbd]

Ok,
You would open the recordset and then move thru using a loop adding the field value... Now using the information from the link on creating an appointment:

So using the information from the link on creating an appointment:

Set myRequiredAttendee = myItem.Recipients.Add("Nate Sun") 
 myRequiredAttendee.Type = olRequired 
 

so the standard method I use is to build the query in the code; however, if you have a stored query that is just fine.

so something like this (warning air code - I've not ran this against outlook and the end user will need to add the outlook object and other code)

Now this would add (should) multiple attendees to a single task item because you create the task item first and then add the attendees.

Sub exampleloop()
    Dim zdb As DAO.Database
    Dim zrs As DAO.Recordset
    Dim zSQL As String
    Dim zAttende As String
    '
    'you'll need other variables
    'for your Task object ofcourse
    '
    On Error GoTo zerrortrap
    '
    zSQL = "SELECT tbl1.pk, tbl1.somefield FROM tbl1"
    'this is just placeholder you could use the name of a query you made and saved from the query editor
    'such as zSQL= "qry_appointmentsto"
    ' 
    Set zdb = CurrentDb
    Set zrs = zdb.OpenRecordset(Name:=zSQL, Type:=dbOpenForwardOnly)
    'Note here, I've only opend for foward only, we're starting at the first record and not editing so this works just fine.
    'you could open dbOpenDynamic if you needed to update a record
    '
    'build your outlooktask object first!
    'Once bult then we can use the recordset we just opened
    If zrs.RecordCount Then
    'If there are any records then the result will be greater than 0 which evaluates to true
        Do
            zAttende = zrs![FieldWithTheReceipentsEmailorName]
            'you may need to do something like
            ' "'" & zrs![fldname] & "'"
            '
            'cut and paste from the above example
            Set myRequiredAttendee = myItem.Recipients.Add(zAttende)
            myRequiredAttendee.Type = olRequired
            zrs.MoveNext
            '
        Loop Until zrs.EOF
    '
    'other code
    'cleanup - if you set it, clear it, if you open it, close it
    If Not myRequiredAttendee Is Nothing Then Set myRequiredAttendee = Nothing
    If Not myItem Is Nothing Then Set myItem = Nothing
    If Not zrs Is Nothing Then
        zrs.Close
        Set zrs = Nothing
    End If
    If Not zdb Is Nothing Then Set zdb = Nothing
'
Exit Sub
zerrortrap:
'I use much more detailed error traps; however this would be the most basic.
'In production, I would have at least a table to store date/time, module, event, etc...
    Debug.Print ">>" & Err.Number & " - " & Err.Description
    Resume Next
End Sub

so... incorporate your code into the above... let us know what happens. :)