Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 25th, 2004, 11:30 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default automate access date to outlook appointment

Hello,

We have several clinics and we take patient appointments
from a central location. I have created a access2000 database with
a main form and users input the patient appoint date and
time in one section of it. There is a hyperlink button to
bringup that clinics shared outlook calender and then
users update the appointment with patients name,appointment date and time there as well, then they
forward the appointment from calendar as e-mail to the
clinic notify them. Can I automate this? can from the
date and time of the appointment on the main form create
a button that will input date and time into the correct
clinic calendar,correct date and time and send e-mail
automatically? This can really cut down the amount of
time and steps we now must do..

Thank you,
Keith Bemis
  #2 (permalink)  
Old August 2nd, 2004, 08:05 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

This information may be help to you.
How to use Automation to send a Microsoft Outlook message using Access 2000
View products that this article applies to.
This article was previously published under Q209948
For a Microsoft Access 97 version of this article, see 161088 . Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).


IN THIS TASK
SUMMARY

Sending a Microsoft Outlook Mail Message Programmatically
REFERENCES
SUMMARY
This article shows you how to use Automation to create and send a Microsoft Outlook message in Microsoft Access 2000.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default...EN-US;CNTACTMS

NOTE: The following code may not work properly if you have installed the Outlook E-mail Security Update. For additional information about this update, please see one of the following articles in the Microsoft Knowledge Base, depending on which version of Outlook you have:
262631 OL2000: Information About the Outlook E-mail Security Update

262617 OL98: Information About the Outlook E-mail Security Update

You can use the SendObject method to send a MAPI mail message programmatically in Microsoft Access. However, the SendObject method does not give you access to complete mail functionality, such as the ability to attach an external file or set message importance. The example that follows uses Automation to create and send a mail message that you can use to take advantage of many features in Microsoft Outlook that are not available with the SendObject method.

There are six main steps to sending a Microsoft Outlook mail message by using Automation, as follows:


Initialize the Outlook session.
Create a new message.
Add the recipients (To, CC, and BCC) and resolve their names.
Set valid properties, such as the Subject, Body, and Importance.
Add attachments (if any).
Display/Send the message.
back to the top
Sending a Microsoft Outlook Mail Message Programmatically
Create a sample text file named Customers.txt in the C:\My Documents folder.
Start Microsoft Access, and open the sample database Northwind.mdb.
Create a module and type the following line in the Declarations section if it is not already there:Option Explicit

On the Tools menu, click References.
In the References box, click to select the Microsoft Outlook 9.0 Object Library, and then click OK.

NOTE: If the Microsoft Outlook 9.0 Object Library does not appear in the Available References box, browse your hard disk for the file, Msoutl9.olb. If you cannot locate this file, you must run the Microsoft Outlook Setup program to install it before you proceed with this example.
Type the following procedure in the new module:Sub SendMessage(Optional AttachmentPath)
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment

   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add("Nancy Davolio")
      objOutlookRecip.Type = olTo

      ' Add the CC recipient(s) to the message.
      Set objOutlookRecip = .Recipients.Add("Andrew Fuller")
      objOutlookRecip.Type = olCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "This is an Automation test with Microsoft Outlook"
      .Body = "Last test - I promise." & vbCrLf & vbCrLf
      .Importance = olImportanceHigh 'High importance

      ' Add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
         Set objOutlookAttach = .Attachments.Add(AttachmentPath)
      End If

      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .Send

   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

To test this procedure, type the following line in the Immediate window, and then press ENTER: SendMessage "C:\My Documents\Customers.txt"

To send the message without specifying an attachment, omit the argument when calling the procedure, as follows:SendMessage

back to the top



REFERENCES
For more information about using Automation in Microsoft Access, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type "Automation" in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
209963 ACC2000: How to Use Automation to Add Appointments to Microsoft Outlook

209955 ACC2000: How to Use Automation to Create a New Contact Item in Microsoft Outlook




  #3 (permalink)  
Old August 2nd, 2004, 08:15 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

Here is the other part

ACC2000: How to Use Automation to Add Appointments to Microsoft Outlook
View products that this article applies to.
This article was previously published under Q209963
This article applies only to a Microsoft Access database (.mdb).

Advanced: Requires expert coding, interoperability, and multiuser skills.


SUMMARY
This article shows you how to create appointments in a Microsoft Access database and how to use Automation to add the appointments to a Microsoft Outlook calendar.
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:
http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
http://support.microsoft.com/default...EN-US;CNTACTMS

The following example demonstrates how to create a table and a form to enter and store appointment information in a Microsoft Access database. It then provides a sample Visual Basic for Applications procedure that uses Automation to add the appointments to Microsoft Outlook.
Start Microsoft Access, and then create a new database named Appt.mdb.
Use the following information to create a new table named tblAppointments:

Field Name Data Type Field Size Format Default Required
Appt Text 50 Yes
ApptDate Date/Time Short Date Yes
ApptTime Date/Time Medium Time Yes
ApptLength Number Long Integer 15 Yes
ApptNotes Memo No
ApptLocation Text 50 No
ApptReminder Yes/No No
ReminderMinutes Number Long Integer 15 No
AddedToOutlook Yes/No No

Set the ApptDate and ApptTime fields as the composite primary key. Close and save the table as tblAppointments.

NOTE: In this example, the primary key in the appointment table is made up of the appointment date and time fields. You can remove or change the primary key if you want to be able to add multiple appointments for the same date and time.
Use the AutoForm: Columnar Wizard to create a new form that is based on tblAppointments table, and then save the form as frmAppointments.
Open the frmAppointments form in Design view, and then change the following form properties: Form property
-------------------------
Caption: Appointment Form

Form Header
-----------
Height: .5"

AddedToOutlook Checkbox
-----------------------
Enabled: No

Add a command button to the form header section, and then set the following properties: Name: cmdAddAppt
Caption: Send to Outlook
Width: 2"
OnClick: [Event Procedure]

Set the OnClick property of the command button to the following event procedure: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

On the Tools menu, click References.
In the References dialog box, click to select the Microsoft Outlook 9.0 Object Library check box. If a reference for this library does not appear in the list, click Browse to locate the Msoutl9.olb file. This file is installed by default in the C:\Program Files\Microsoft Office\Office folder. Click OK to close the References dialog box.
Save the form as frmAppointments, open it in Form view, and then add the following information as a new appointment record: Appt: Budget Meeting
    ApptDate: <enter tomorrow's date>
    ApptTime: 2:00 PM
    ApptLength: 120
    ApptNotes: To begin discussion of next year's budget.
    ApptLocation: Conference Room
    ApptReminder: <check the box>
    ReminderMinutes: 15

NOTE: Enter ApptLength in minutes, not in hours. Note that in this example, ApptLength is set to 120 minutes instead of to 2 hours.
Click Send To Outlook, start Microsoft Outlook, and view your calendar for tomorrow's appointments


Similar Threads
Thread Thread Starter Forum Replies Last Post
Outlook Automation in Access arielalevy Access VBA 1 June 27th, 2006 07:00 PM
outlook style appointment control help, thanks! raybristol C# 0 January 17th, 2006 11:39 PM
Outlook email through Access desireemm Access 1 August 26th, 2005 11:31 AM
Automate Access 2000 in vb.net SupaHoopsa General .NET 0 June 22nd, 2004 05:35 AM
Automate Export of Comma-Delineated file > Access? darinsee Access 1 February 10th, 2004 09:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.