Question Linking To Outlook (Advanced) (1 Viewer)

[Scottigus]

Okay, so here is what I am trying to do.

My employer wants a program that gives him notifications. It is a Human Resources program I made for him. What he is wanting is this:

When they are 80 days from their hire date, Jason (the human resource employer) will get a notification that their 90 days is almost up and that he needs to get ready to review them. When they are exactly 90 from their hire date, he will get a notification that they need to be reviewed ASAP.

Also, he would like a notification that helps him know when they need a review. He is hoping to review them every 6 months, so we have a field for (Date of Last Review). So it would be 6 months after that date (Hoping to notify him 2 weeks before the 6 month mark, and on 6 months).

Now, is there anyway to Link Access to Outlook so when he is entering the dates into "Date of Last Review", it will automatically calculate 5 months and 2 weeks out, and then record that into his Outlook Calender, with an alert setup. So it wouldn't be Access alerting him, it would place the date into outlook and set up an alert. He always has outlook open, but would not always have this access program open.

Any help would be great!!!

Thanks,

Scott

 

[HiTechCoach]

Scott,

Yes, it is possible to create an appointment in Outlook from Access.

See:
How to Use Automation to Add Appointments to Microsoft Outlook

Hope this helps...

 

[Scottigus]

Okay, so I got the button to work and all, but now I have 1 problem. I followed everything it said, but when I enter all the information, it appears twice on 7/9/2003 and the next Thursday afterwards. It does not pull from the form boxes. Here is what I have.

Private Sub cmdAddAppt_Click()
On Error GoTo Add_Err

'Save record first to be sure required fields are filled.
DoCmd.RunCommand acCmdSaveRecord

'Exit the procedure if appointment has been added to Outlook.
If Me!AddedToOutlook = True Then
MsgBox "This appointment is already added to Microsoft Outlook"
Exit Sub
'Add a new appointment.
Else
Dim objOutlook As Outlook.Application
Dim objAppt As Outlook.AppointmentItem
Dim objRecurPattern As Outlook.RecurrencePattern

Set objOutlook = CreateObject("Outlook.Application")
Set objAppt = objOutlook.CreateItem(olAppointmentItem)

With objAppt
.Start = Me!ApptDate & " " & Me!ApptTime
.Duration = Me!ApptLength
.Subject = Me!Appt

If Not IsNull(Me!ApptNotes) Then .Body = Me!ApptNotes
If Not IsNull(Me!ApptLocation) Then .Location = Me!ApptLocation
If Me!ApptReminder Then
.ReminderMinutesBeforeStart = Me!ReminderMinutes
.ReminderSet = True
End If

Set objRecurPattern = .GetRecurrencePattern

With objRecurPattern
.RecurrenceType = olRecursWeekly
.Interval = 1
'Once per week
.PatternStartDate = #7/9/2003#
'You could get these values
'from new text boxes on the form.
.PatternEndDate = #7/23/2003#
End With

.Save
.Close (olSave)
End With
'Release the AppointmentItem object variable.
Set objAppt = Nothing
End If

'Release the Outlook object variable.
Set objOutlook = Nothing

'Set the AddedToOutlook flag, save the record, display a message.
Me!AddedToOutlook = True
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Appointment Added!"

Exit Sub

Add_Err:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
Exit Sub

End Sub

My guess is it would be the:

With objRecurPattern
.RecurrenceType = olRecursWeekly
.Interval = 1
'Once per week
.PatternStartDate = #7/9/2003#
'You could get these values
'from new text boxes on the form.
.PatternEndDate = #7/23/2003#
End With

How would I get that to pull from Access. Now...how much of a change is it since I'm using Access 2007. It this outdated for me since I'm on 2007?

Other than that...what you posted was EXACTLY what I was trying to figure out.

Please help!

-Scott

 

[HiTechCoach]

TIP: when posting code, it is better to click the # to insert the code tags and not use I

Now...how much of a change is it since I'm using Access 2007. It this outdated for me since I'm on 2007?

It will probably work just fine in 2007.

You will have to substitute you control names and data in the code. All the control reference start with Me!.

Example:

 .Duration = Me![B]ApptLength[/B]

You will need to change the control name of ApptLength to you control name on your form.


I would urge you to follow the example completely. That means you will need to create a new database, build the table, etc like the example explains. This way you can test the code and not do anything harmful to you database while you are learning. The more you do, the more you learn. If it were me, I would actually type all the code and not cut and paste it until I completely understand the code. You will learn a lot faster doing it that way.

TIP: Always make lots of backups before experimenting/testing. I would also try new stuff in a test database or a copy of your real database. This is also why I always split my app into a front end and back end. It sure makes backing up and testing easier.

 

[Scottigus]

I followed the example exactly. Made the table, followed the codes and everything and did it step by step, and the results where as I posted above, appearing in July of 2003...

I'll figure it out there. I'm sure I can play around with it and get it to work hopefully!! Thanks for all the help!!

 

[irish634]

I followed the example exactly. Made the table, followed the codes and everything and did it step by step, and the results where as I posted above, appearing in July of 2003...

I'll figure it out there. I'm sure I can play around with it and get it to work hopefully!! Thanks for all the help!!

            'Set objRecurPattern = .GetRecurrencePattern
            
            'With objRecurPattern
                '.RecurrenceType = olRecursWeekly
                '.Interval = 1
                'Once per week
                '.PatternStartDate = #7/9/2003#
                'You could get these values
                'from new text boxes on the form.
                '.PatternEndDate = #7/23/2003#
           ' End With

Make this section all comments or delete it all together. It'll work fine. I would suggest making them comments in case you want to set up a recurring appointment.

 

[Scottigus]

So now continuing with my problems...

I did the walkthrough from here...http://support.microsoft.com/default.aspx?scid=kb;EN-US;209963.

After doing the walkthrough I had some problems. It would always set it back in like 7/9/2003, even though I set the appt date for the next day like it says to do. Well, I deleted the access file and went to restart it, but now when I restart it, I get this error...

Compile Error:
          User-defined type not defined

When it gives that error, it highlights the

Dim objOutlook As Outlook.Application

part of the code.

Now it worked once before, but now does not. I have went up to tools, references and set it to not Microsoft Outlook 9.0 Object Library but Microsoft Outlook 12.0 Object Library because I have Outlook and Access 2007.

Would that make a difference in what it is trying to do?

Also, the reason I am needing to know is because of this.

I am trying to make a command button on a Form in Access for a Human Resource program. The only field I really care about is "Last Raise". Whatever date he enters in there, I would like it to schedule out an appointment in outlook 6 months out. But if the code isn't working, I guess I need to get it to work first before adjusting other controls.

Any help please?! Sorry to be such a bother...