By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

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

100+
P: 153
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
Dec 29 '06 #1
Share this Question
Share on Google+
16 Replies


100+
P: 153
**sorry meant to say...which sends appointments to myown computer but not to other computers....cut myself off there somehow...
Dec 29 '06 #2

100+
P: 153
Can I used the MeetingItem Object? I guess this couldn't be used in Access '97 but can it be used in Access 2003?
Dec 29 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Kosmos

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

Mary

Expand|Select|Wrap|Line Numbers
  1. ' Start Outlook. . . If it is already running, you'll use the same instance... 
  2. Dim olApp As Outlook.Application 
  3. Set olApp = CreateObject("Outlook.Application") 
  4.  
  5. ' Logon.  Doesn't hurt if you are already running and logged on... 
  6. Dim olNs As Outlook.NameSpace 
  7. Set olNs = olApp.GetNamespace("MAPI") 
  8. olNs.Logon 
  9.  
  10. ' Create and Open a new contact. 
  11. Dim olItem As Outlook.ContactItem 
  12. Set olItem = olApp.CreateItem(olContactItem) 
  13.  
  14. ' Setup Contact information... 
  15. With olItem 
  16.     .FullName = "James Smith" 
  17.     .Birthday = "9/15/1975" 
  18.     .CompanyName = "Microsoft" 
  19.     .HomeTelephoneNumber = "704-555-8888" 
  20.     .Email1Address = "someone@microsoft.com" 
  21.     .JobTitle = "Developer" 
  22.     .HomeAddress = "111 Main St." & vbCr & "Charlotte, NC 28226" 
  23. End With 
  24.  
  25. ' Save Contact... 
  26. olItem.Save 
  27.  
  28. ' Create a new appointment. 
  29. Dim olAppt As Outlook.AppointmentItem 
  30. Set olAppt = olApp.CreateItem(olAppointmentItem) 
  31.  
  32. ' Set start time for 2-minutes from now... 
  33. olAppt.Start = Now() + (2# / 24# / 60#) 
  34.  
  35. ' Setup other appointment information... 
  36. With olAppt 
  37.     .Duration = 60 
  38.     .Subject = "Meeting to discuss plans..." 
  39.     .Body = "Meeting with " & olItem.FullName & " to discuss plans." 
  40.     .Location = "Home Office" 
  41.     .ReminderMinutesBeforeStart = 1 
  42.     .ReminderSet = True 
  43. End With 
  44.  
  45. ' Save Appointment... 
  46. olAppt.Save 
  47.  
  48. ' Send a message to your new contact. 
  49. Dim olMail As Outlook.MailItem 
  50. Set olMail = olApp.CreateItem(olMailItem) 
  51.  
  52. ' Fill out & send message... 
  53. olMail.To = olItem.Email1Address 
  54. olMail.Subject = "About our meeting..." 
  55. olMail.Body = _ 
  56.     "Dear " & olItem.FirstName & ", " & vbCr & vbCr & vbTab & _ 
  57.     "I'll see you in 2 minutes for our meeting!" & vbCr & vbCr & _ 
  58.     "Btw: I've added you to my contact list." 
  59. olMail.Send 
  60.  
  61. ' Clean up... 
  62. MsgBox "All done...", vbMsgBoxSetForeground 
  63. olNS.Logoff 
  64. Set olNs = Nothing 
  65. Set olMail = Nothing 
  66. Set olAppt = Nothing 
  67. Set olItem = Nothing 
  68. Set olApp = Nothing 
  69.  
Jan 1 '07 #4

100+
P: 153
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.
Jan 2 '07 #5

100+
P: 153
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)
Jan 2 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for the clarification Kosmos. Glad you got it to work out.

Mary
Jan 3 '07 #7

P: 28
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
May 8 '07 #8

P: 28
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
May 16 '07 #9

100+
P: 153
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.
Jun 25 '07 #10

100+
P: 153
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
Jun 25 '07 #11

P: 28
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
Jun 25 '07 #12

100+
P: 153
You're saying if you just do an appointment without an email?
Jun 25 '07 #13

P: 28
Yep, works fine for us here.
Jun 26 '07 #14

100+
P: 153
Oh, okay....yes I realize that, but I have to send e-mails with the appointments. Thanks!
Jun 27 '07 #15

dbm
P: 1
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.
Jul 3 '07 #16

100+
P: 116
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
Aug 10 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.