Access to Outlook Link

[Mitchy]

I currently use Access to track information regarding quotations for my company. I would like to know if I can have Access create an appointment in Outlook as a reminder for me to follow up with a customer call or visit on a set date. This database is used by several people and I want everyone to know the status of these open quotes. If the operation of adding the info to a calendar is seamless thru Access, I think people will tend to use it more.

Thanks,

 

[mp9]

Since you use Outlook as your mail client, you could try some code like this to send an appointment from within Access - note that you need to set a reference to the Outlook Object Library from the VBA code editor window for this to work (menu parth is Tools, References):

Function fctnSendAppt(Optional Addr, Optional CC, Optional BCC, _
    Optional Subject, Optional MessageText, Optional StartTs, Optional LengthOf, Optional LocationOf, _
    Optional Urgency As Byte = 1, Optional EditMessage As Boolean = True)

Dim objOutlook As Outlook.Application
Dim objOutlookAppt As Outlook.AppointmentItem
Dim objOutlookRecip As Outlook.Recipient

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

With objOutlookAppt
    If Not IsMissing(Addr) Then
        Set objOutlookRecip = .Recipients.Add(Addr)
        objOutlookRecip.Type = olTo
    End If
    
    If Not IsMissing(CC) Then
        Set objOutlookRecip = .Recipients.Add(CC)
        objOutlookRecip.Type = olCC
    End If

    If Not IsMissing(BCC) Then
        Set objOutlookRecip = .Recipients.Add(BCC)
        objOutlookRecip.Type = olBCC
    End If

    If Not IsMissing(Subject) Then
        .Subject = Subject
    End If

    If Not IsMissing(MessageText) Then
        .Body = MessageText
    End If

    Select Case Urgency
    Case 2
        .Importance = olImportanceHigh
    Case 0
        .Importance = olImportanceLow
    Case Else
        .Importance = olImportanceNormal
    End Select
    
    If Not IsMissing(StartTs) Then
        .Start = StartTs
    End If
    
    If Not IsMissing(LengthOf) Then
        .Duration = LengthOf
    End If
    
    If Not IsMissing(LocationOf) Then
        .Location = LocationOf
    End If
    
    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next

    If EditMessage Then
        .Display
    Else
        .Save
        .Send
    End If
End With
Set objOutlook = Nothing

End Function

This could then be called in you VBA something like:

fctnSendAppt("a@b.com",,,"The subject","Meeting arrangements",#01/11/2005 11:00#,"60","Your office",1,True)

Note the duration parameter is the length of the meeting in minutes.

Also, you'll see that when you use this code you get prompted to confirm that it's okay for your program to send an e-mail using Outlook. This is a virus-protection method introduced by Microsoft to prevent script-kiddies writing malicious code to send self-propagating mails to everyone in your address book. Using straight Outlook this is largely unavoidable, so you'll have to get used to clicking the "Yes" button (unless you want to consider Outlook Redemption as a freeware solution to this inconvenience, although that means getting away from straight Outlook). At least you get an option to allow your program access to Outlook for up to 10 minutes, which can be handy if you're about to generate a whole load of e-mails.

This whole post is based on code from

http://www22.brinkster.com/accessory/modules/005.shtml

where this subject is covered in more detail.