How to send multiple appointments to Outlook

[MyWaterloo]

I am currently using this code to send an appointment to Outlook. It sends the info to a form called "frmAppointment s" and then to Outlook. This code works great to send the record that is in focus. I would like to be able to have the ability to send multiple records as appointments. How do I do more than one at a time?

Form_frmAppointments!ApptDate = [Form_Maintenance Subform3].[Next Maintenance]
'Form_frmAppointments!ApptTime = Me![Sample Run Time]
Form_frmAppointments!Appt = Me.Text80
Form_frmAppointments!ApptReminder = True



' Save record first to be sure required fields are filled.
DoCmd.RunCommand acCmdSaveRecord
' Exit the procedure if appointment has been added to Outlook.

Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
.Start = Form_frmAppointments!ApptDate & " " & Form_frmAppointments!ApptTime
.Duration = Form_frmAppointments!ApptLength
.Subject = Form_frmAppointments!Appt
.Categories = "Maintenance"
If Not IsNull(Form_frmAppointments!ApptNotes) Then .Body = Form_frmAppointments!ApptNotes
If Not IsNull(Form_frmAppointments!ApptLocation) Then .Location = _
Form_frmAppointments!ApptLocation
If Form_frmAppointments!ApptReminder Then
.ReminderMinutesBeforeStart = Form_frmAppointments!ReminderMinutes
.ReminderSet = True
End If
.Save
End With
' Release the Outlook object variable.
Set outobj = Nothing
' Set the AddedToOutlook flag, save the record, display a message.
Form_frmAppointments!AddedToOutlook = True
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Appointment Added!"
Exit Sub
AddAppt_Err:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Exit Sub

[ADezii]

This is just Theory, I haven't actually tested it.

1. Create a Recordset which consists of only those Records that you wish to Send to Outlook.
2. Loop through each Record, sending the appropriate Data by referencing the Fields in the Recordset.
3. If you are not sure how to do this, I can point you in the right direction.

[MyWaterloo]

I have a subform that has all the maintenance records on it filtered according to the Equipment item on the main form. Right now I can click a button on the main form and send the infocus maintenance record from the subform to Outlook as an appointment. How would I loop through the record set to tell Access to send all of the filtered records on the subform to Outlook? I don't know how to create a "loop".

[ADezii]

1. Create a Recordset based on the Current State of the Sub-Form, as in it's Recordset Property.
2. Loop through all the Records in the Recordset, performing some operation on each Record, in your case sending specific Data to Outlook.
3. I created a Demo for you based on the Orders Form of the Northwind Sample Database. This Form contains a Sub-Form named Orders Subform. For any given Record in the Main Form (Orders), the Code will loop through through all related Records in the Sub-Form and Print specific Field Values.

Dim rst As DAO.Recordset

Set rst = Me![Orders Subform].Form.RecordsetClone
    rst.MoveFirst

Debug.Print "[ProductID]", "[Quantity]", "[UnitPrice]", "[Discount]", "[ExtendedPrice]"

With rst
  Do While Not .EOF
    'Process Fields for Outlook here
    Debug.Print ![ProductID], ![Quantity], ![UnitPrice], ![Discount], ![ExtendedPrice]
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

OUTPUT:

[ProductID]   [Quantity]    [UnitPrice]   [Discount]    [ExtendedPrice]
 59            15            55            0.1           742.5 
 57            5             19.5          0.1           87.75 
 40            10            18.4          0.1           165.6 
 11            50            21            0.1           945

[MyWaterloo]

ADezii, thanks. I guess I am just not quite understanding what I am suppose to do with this code. Is it suppose to work in conjunction with the code to send an appointment to Outlook? I placed this code you gave me behind a button on the Orders form of the Northwind database, but nothing happens when I click it. Thanks for the puzzle piece I just don't know where to put it. =-)

[ADezii]

When I get a chance, I'll try to Merge this Code with your Outlook Code to give you some idea as to how you can process the Records in the Sub-Form. The General Idea is as follows:

Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olAppointmentItem)
Dim rst As DAO.Recordset

Set rst = Me![Orders Subform].Form.RecordsetClone       'Sub-Form's Recordset
    rst.MoveFirst


With outappt
  Do While Not rst.EOF      'Process Records in the Recordset (Sub-Form)
    .Start = rst!ApptDate & " " & rst!ApptTime
    .duration = rst!ApptLength
    .Subject = rst!Appt
    Categories = "Maintenance"
      If Not IsNull(rst!ApptNotes) Then .Body = rst!ApptNotes
      If Not IsNull(rst!ApptLocation) Then .Location = rst!ApptLocation
      If rst!ApptReminder Then
        .ReminderMinutesBeforeStart = rst!ReminderMinutes
        .ReminderSet = True
      End If
    .Save
    rst.MoveNext        'Move to the Next Record
  Loop
End With

' Release the Outlook object variable.
Set outobj = Nothing

rst.Close
Set rst = Nothing

[MyWaterloo]

"When I get a chance, I'll try to Merge this Code with your Outlook Code"... Thanks. I see on line #25 is where you put "loop". I don't believe I have ever used the loop function before. I am very interested in seeing how it would work with my actual project and then hopefully from that be able to know how to use it in other areas. Thank You.

[MyWaterloo]

I think I can better articulate what I would like to see happen. I have a maintenance database with a main form based on equipment and a subform showing all the maintenance done to the specific equipment. I am currently able to send an appointment to outlook for one record of the subform at a time by clicking a button on the main form with my send to outlook code behind it. The record is first passed to a form call frmAppointments and then to Outlook. Instead of having to select each record and then send it, I would like to be able to send all the records that are for the month with just one click. Hope this helps.