Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Ms Access/Outlook Appointment system

Posted on 2010-08-20
10
Medium Priority
?
617 Views
Last Modified: 2013-11-27
Hey,

Using help on here i managed to make a script that when i clicked a button in Access it would add an appointment into someones calender, this is the coding ive been using,

Private Sub AddAppt_Click()

 
On Error GoTo ErrorHandler
 
   Dim appOutlook As Outlook.Application
   Dim nms As Outlook.NameSpace
   Dim Appt As Outlook.AppointmentItem
   Dim dteStartTime As Date
   Dim dteEndTime As Date
   
   dteStartTime = CDate(CStr(DateAdd("d", 2, Date)) & " 9:00 AM")
   dteEndTime = CDate(CStr(DateAdd("d", 2, Date)) & " 10:00 AM")
 
   Set appOutlook = GetObject(, "Outlook.Application")
   Set Appt = appOutlook.CreateItem(olAppointmentItem)
   With Appt
      .Subject = Me!Appt
      .Start = dteStartTime
      .End = dteEndTime
      If Not IsNull(Me!ApptLocation) Then .Location = _
      Me!ApptLocation
      If Not IsNull(Me!ApptNotes) Then .Body = Me!ApptNotes
      .Recipients.Add (" james.bestwick@example.co.uk")
      .Display
      .Send
   End With
   
ErrorHandlerExit:
   Set appOutlook = Nothing
   Exit Sub
 
ErrorHandler:
   
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in CreateApptWithRecipient procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If
   
End Sub

My only problem now is every time i click the button i get an error that comes up saying "Compile error user defined type not defined" it then highlights this part "Dim appOutlook As Outlook.Application" ...... I am using MS Outlook and Access 2003 on a windows 7 operating system.

Thanks
0
Comment
Question by:jdpipes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 33483136
Hi, jdpipes.

Change

Dim appOutlook As Outlook.Application

to

Dim appOutlook As Object
0
 

Author Comment

by:jdpipes
ID: 33483182
All 3 of these have the same error

   Dim appOutlook As Outlook.Application
   Dim nms As Outlook.NameSpace
   Dim Appt As Outlook.AppointmentItem

if i change all of them to object then i get this error

"Error no: 438 in CreateApptWithRecipient procedure; Description: object doesnt support property or methog"
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33483213
What line does the last error occur on?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 6

Assisted Solution

by:OxonDev
OxonDev earned 200 total points
ID: 33483247
Hi,

Although BlueDevilFan's suggestion will work I'd also check your references.  You should have one to C:\Program Files\Microsoft Office\Office12\MSOUTL.OLB or similar to use the Outlook Type Library.  

It's also possible that even with a valid reference you'll still get a misleading error message if the file is corrupted.  Try getting this code to work in a new file (using linked tables to access your records).  If the new file works then you've got an issue with the current db.
0
 

Author Comment

by:jdpipes
ID: 33483249
It doesnt give me a line just goes back to the form then shows the message
0
 
LVL 76

Expert Comment

by:David Lee
ID: 33483288
OxonDev is correct.  You can add a reference.  I didn't suggest that because in my opinion it's a better practice to use late binding and avoid reference issues, especially if the code is going to be distributed.  I don't see now the code can generate a "object doesn't support property or method" compile error when the objects are typed.  That implies that it knows what methods are defined for each object which it cannot know when the object type isn't specified.  
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 total points
ID: 33483349
You'll also have to define your constants. You use this one:

olAppointmentItem

If you have a reference to the Outlook library it'll work. If you remove the reference, it won't work. You can define them in the General Declarations section somewhere:

Public Const ac_olAppointmentItem = 100 '/or whatever value is needed

note the different spelling - I've added "ac_" to the beginning, therefore you'd have to change your code to use that value instead of olAppointmentItem

And I agree with BlueDevilFan - one of the points of using late binding is to NOT have references, therefore using OxenDev's advice to add a reference to the TypeLib would somewhat defeat that purpose.
0
 

Author Comment

by:jdpipes
ID: 33483363
ive changed it about and played with it and came up with this which works can anyone see any problems?

Private Sub AddAppt_Click()

Dim strMsg As String
Dim strName As String
On Error Resume Next


strName = Me!Recipients

Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set objDummy = objApp.CreateItem(olMailItem)
Set objRecip = objDummy.Recipients.Add(strName)
objRecip.Resolve
If objRecip.Resolved Then
On Error Resume Next
Set objFolder = objNS.GetSharedDefaultFolder(objRecip, 9)
If Not objFolder Is Nothing Then
Set objAppt = objFolder.Items.Add
If Not objAppt Is Nothing Then
With objAppt
.Subject = Me!Appt
.Start = Me!ApptDate & " " & Me!ApptTime
.Duration = Me!ApptLength
.Location = Me!ApptLocation
.Body = Me!ApptNotes
.ReminderMinutesBeforeStart = Me!ReminderMinutes
.ReminderSet = True
.Save
End With
End If
End If
Else
MsgBox "Could not find " & Chr(34) & strName & Chr(34), , _
"User not found"
End If

Set objApp = Nothing
Set objNS = Nothing
Set objFolder = Nothing
Set objDummy = Nothing
Set objRecip = Nothing
Set objAppt = Nothing
End Sub
0
 
LVL 76

Accepted Solution

by:
David Lee earned 1600 total points
ID: 33483370
Looks good to me.
0
 
LVL 85
ID: 33484498
I don't see where you've defined:

olMailItem

although you may have defined it in a separate module. If you have not, then be aware that you will need to maintain your reference to the Outlook library, which can cause issues when deploying if you must support multiple versions of Outlook.
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question