Can you send meetings or appointments by means of outlook emails through access?

[Kosmos]

Good afternoon everyone, just wondering if anyone knew if it's possible to send meetings or appointments through email when you run VBA or SQL code in Access 2003? The following is the code I've been using which sends

stSQL = "SELECT * FROM AddedToOutlook WHERE DatabaseReferenceNumber = " _
& recSet1("DatabaseReferenceNumber2")

recSet6.Open stSQL, con6, adOpenKeyset, adLockOptimistic
If recSet6.EOF Then
recSet6.AddNew
Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
.Start = recSet1.Fields("NotificationDate2") _
& " " & recSet1.Fields("ApptTime2")
.Duration = 15
.Subject = "Contract Notification/End" & " " _
& recSet1.Fields("DatabaseReferenceNumber2") _
& " " & recSet1.Fields("Vendor2")
.Body = "Contract Notification/End" & " " _
& recSet1.Fields("DatabaseReferenceNumber2") _
& " " & recSet1.Fields("Vendor2")
.ReminderMinutesBeforeStart = recSet1.Fields("ReminderMinutes2")
.ReminderSet = True
.Save
End With
recSet6.Fields("AddedToOutlook") = True
recSet6.Fields("DatabaseReferenceNumber") = recSet1.Fields("DatabaseReferenceNumber2")
Set outobj = Nothing
'DoCmd.RunCommand acCmdSaveRecord
recSet6.Update
End If
recSet6.Close
End If

If you want to see the above code in its full context you can see it here:

http://www.thescripts.com/forum/thread581521.html

Willakawill helped me out a bit with getting access to remember if it had already added the appointments and in doing so he cleaned up ALL my code for that function lol so you will be able to read his version of the code a bit better probably.

Anyways....in terms of sending emails through outlook for MS Access 2003, Microsoft recommends using the following code:

Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

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

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

(from http://support.microsoft.com/kb/318881/en-us )

So is there some command I can add into here that will allow me to add a meeting or appointment with this message? I want to add the new meetings and appointments but then add a list of all pertinent contracts (which the program checks for already) but then again since it will be burried in an IF statement maybe it will not be worth it to send a list of all contracts so maybe I'll just send a list of the new ones....

can I just combine the code? Once you set the recipients will it send the appointments and the emails?

anyways...any help would be much appreciated.

Cheers

[Kosmos]

**sorry meant to say...which sends appointments to myown computer but not to other computers....cut myself off there somehow...

[Kosmos]

Can I used the MeetingItem Object? I guess this couldn't be used in Access '97 but can it be used in Access 2003?

[MMcCarthy]

Kosmos

I robbed this code from somewhere but I can't remember where.

Mary

' Start Outlook. . . If it is already running, you'll use the same instance... 
Dim olApp As Outlook.Application 
Set olApp = CreateObject("Outlook.Application") 
 
' Logon.  Doesn't hurt if you are already running and logged on... 
Dim olNs As Outlook.NameSpace 
Set olNs = olApp.GetNamespace("MAPI") 
olNs.Logon 
 
' Create and Open a new contact. 
Dim olItem As Outlook.ContactItem 
Set olItem = olApp.CreateItem(olContactItem) 
 
' Setup Contact information... 
With olItem 
    .FullName = "James Smith" 
    .Birthday = "9/15/1975" 
    .CompanyName = "Microsoft" 
    .HomeTelephoneNumber = "704-555-8888" 
    .Email1Address = "[e-mail]" 
    .JobTitle = "Developer" 
    .HomeAddress = "111 Main St." & vbCr & "Charlotte, NC 28226" 
End With 
 
' Save Contact... 
olItem.Save 
 
' Create a new appointment. 
Dim olAppt As Outlook.AppointmentItem 
Set olAppt = olApp.CreateItem(olAppointmentItem) 
 
' Set start time for 2-minutes from now... 
olAppt.Start = Now() + (2# / 24# / 60#) 
 
' Setup other appointment information... 
With olAppt 
    .Duration = 60 
    .Subject = "Meeting to discuss plans..." 
    .Body = "Meeting with " & olItem.FullName & " to discuss plans." 
    .Location = "Home Office" 
    .ReminderMinutesBeforeStart = 1 
    .ReminderSet = True 
End With 
 
' Save Appointment... 
olAppt.Save 
 
' Send a message to your new contact. 
Dim olMail As Outlook.MailItem 
Set olMail = olApp.CreateItem(olMailItem) 
 
' Fill out & send message... 
olMail.To = olItem.Email1Address 
olMail.Subject = "About our meeting..." 
olMail.Body = _ 
    "Dear " & olItem.FirstName & ", " & vbCr & vbCr & vbTab & _ 
    "I'll see you in 2 minutes for our meeting!" & vbCr & vbCr & _ 
    "Btw: I've added you to my contact list." 
olMail.Send 
 
' Clean up... 
MsgBox "All done...", vbMsgBoxSetForeground 
olNS.Logoff 
Set olNs = Nothing 
Set olMail = Nothing 
Set olAppt = Nothing 
Set olItem = Nothing 
Set olApp = Nothing 
 

[Kosmos]

Thanks Mary, I'll let you know if this works...it seems like it sends an appointment to my computer an an email to my contact? Well....I hope I am wrong.

[Kosmos]

This did not work but I figured it out. Sorry...I may not have made it clear what I was trying to do. Anyways to send an email with an appointment you do the following

Declare an attachment object like outOutlookAttach by:
Dim outOutlookAttach As Outlook.Attachment

Then do your whole setting up appt thing (call it something such as outAppt in this case) and then set it as your attachment:

Set outOutlookAttach = outMail.Attachments.Add(outAppt)

[MMcCarthy]

Thanks for the clarification Kosmos. Glad you got it to work out.

Mary

[mattkorguk]

Kosmos,
Hi, I'm trying to do exactly the same thing as you!
You mentioned that you got it to work by adding the appointment itme as an attachment, I've been trying this for ages now it all I get is "undefined object".

It's driving me bonkers!
I've added my code below; If you or anyone else could shed some light, it would be much appreciated.

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim objAppt As Outlook.AppointmentItem
Set objAppt = olApp.CreateItem(olAppointmentItem)
With objAppt
.Start = Me!ApptStartDate & " " & 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 = yes Then
.ReminderMinutesBeforeStart = Me!ReminderMinutes
.ReminderSet = True
End If
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
Set objOutlookRecip = .Recipients.Add(Me.Email)
objOutlookRecip.Type = olTo
'Set objOutlookRecip = .Recipients.Add(Me.EmailCC)
'objOutlookRecip.Type = olCC
.Subject = "Meeting booking."
.Body = Me.ApptNotes
.Importance = olImportanceHigh
'If Not IsMissing(objAppt) Then
Set objOutlookAttach = outmail.Attachments.Add(objAppt)
'End If

[mattkorguk]

Hi,
Just to send a meeting I got this to work fine...

Dim outMail As Outlook.AppointmentItem
Me.Refresh
Set outMail = Outlook.CreateItem(olAppointmentItem)
outMail.Subject = "A new appointment has been booked for " & _ Me.Case_Id & "."
outMail.Location = Me.Purpose
outMail.MeetingStatus = olMeeting
outMail.Start = Me!ApptDate & " " & Me!ApptTime
outMail.RequiredAttendees = Me.SalesRep.Column(2)
outMail.Body = Me.Purpose
outMail.Send

My only issue now is, how do you stop the 'Outlook has detected another program is trying to send an e-mail' message appearing each time!
Thanks
Matt

[Kosmos]

Hey Matt,
Good to see you got that all worked out. I have been out of the loop (pun intended) for a while. In terms of that error message, I have no clue how to get rid of it. I have error messages turned off, but I still get that error message. I think the error comes from Outlook and not from Access...you would have to find some way to either turn off the error through code...which means you have to somehow change a preference in Outlook through Access, or turn it off manually, which means each person that uses your access database will have to do the same. However, if you are sending out large amounts of emails and that message keeps on coming up, there is a way around that...you can tell it to allow access for a few minutes with the option at the bottom of that message that another program is trying to send an email...this will allow everything to run smoothly after that and it may be your best bet.

[Kosmos]

Hey Matt,
Good to see you got that all worked out. I have been out of the loop (pun intended) for a while. In terms of that error message, I have no clue how to get rid of it. I have error messages turned off, but I still get that error message. I think the error comes from Outlook and not from Access...you would have to find some way to either turn off the error through code...which means you have to somehow change a preference in Outlook through Access, or turn it off manually, which means each person that uses your access database will have to do the same. However, if you are sending out large amounts of emails and that message keeps on coming up, there is a way around that...you can tell it to allow access for a few minutes with the option at the bottom of that message that another program is trying to send an email...this will allow everything to run smoothly after that and it may be your best bet.
Cheers,
Kosmös

[mattkorguk]

Hi Kosmos,

Just to keep you updated I found a way round the Outlook message, by just opening up the appointment for the user add notes if required :)
I think this looks much neater then having the message keep popping up!

On Error GoTo cmdEmail_Err
Me.Refresh
If (Not IsNull([SalesRep])) Then
If (IsNull(DLookup("[Email Id]", "Parties", "[Email Id] ='" & Me.EMail_Id & "'"))) Then GoTo cmdNoEmail
Dim outMail As Outlook.AppointmentItem
Set outMail = Outlook.CreateItem(olAppointmentItem)
outMail.Subject = "A new appointment has been booked for " & Me.Case_Id & "."
outMail.Location = Me.Purpose
outMail.MeetingStatus = olMeeting
outMail.Start = Me!ApptDate & " " & Me!ApptTime
outMail.RequiredAttendees = Me.SalesRep.Column(2)
outMail.Body = Me.Purpose
outMail.Display
End If
Exit Sub

cmdNoEmail:
MsgBox "No E-mail address is available for this Advisor, please update.", vbCritical, "E-mail Address Error"
Exit Sub
cmdEmail_Err:
MsgBox "You cancelled and did not send the email", vbExclamation
DoCmd.SetWarnings False
Me.Refresh
DoCmd.SetWarnings True
Resume cmdEmail_Exit
DoCmd.SetWarnings True
Me.Refresh
cmdEmail_Exit:
Exit Sub


Cheers
Matt

[Kosmos]

You're saying if you just do an appointment without an email?

[mattkorguk]

Yep, works fine for us here.

[Kosmos]

Oh, okay....yes I realize that, but I have to send e-mails with the appointments. Thanks!

[dbm]

Microsoft have implemented a security check in order to avoid obvious attempts of hijacking the Outlook client. This check can however be overridden in some ways. There is for example something called "Redemption" which I unfortunately don't know much of. Implementing this object can give you access to the mail (Outlook) client without raising those warnings.

This link (http://www.dimastr.com/redemption/home.htm) will give you a fast insight what it's all about and Googleing on "Outlook Redemption" gives you further a manageable amount of informative hits on how to use it and so on.

[jacc14]

Hi all

I have been looking at the suggested codes and they work well for what I need. However I have added a recipient to the appointment but it doesnt appear in their calendar..only mine. Can this be done??

Many thanks
jacc14