Hey guys
new to Access so bear with me please.
I want to add in a feature that will send an email or mesasage to outlook to remind us to do a follow up on clients after a period of time.
ie. client makes contact and one month later we have to call them back...
we need to be reminded of this
How do i set this up so that it goes to the 3 of us?
thanks for your help as i have no idea that is why i am posting it here.
Albert
An email is easy; you can use SendObject (more info in VBA help). For a task or calendar item, you'd have to use automation, and I'm not sure offhand if you can set someone else's. I guess you could set a calendar item on yours and invite them.
I have just done something similar to his.
You could create a query that is set to let you know which clients need contacting inline with your set criteria. You then generate a report on this query.
You can then email this query to all your required colleagues - this can all be linked to a button.
Your email code will be something along the lines of this:
Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String
Dim strReport As String
strSubject = "Your Subject here"
strToWhom = "me@mail.com; me2@mail.com"
strMsgBody = " The body of your text goes here; ie: The attached report is a list of the clients that we need to follow up on"
This will create an appointment using VBA with the settings in GetAppDetails routine.
Set a reference to this file- Microsoft CDO 1.21 Library
if you cant find it in your references, use the browse option and find it here-
C:\Program Files\Common Files\system\MSMAPI\2057\cdo.dll
create a new module and call it modOutlookPublic
Add this code to the top directly under "Option Explicit"
Code:
Public mobjOLA As Outlook.Application
Public mobjNS As Outlook.Namespace
Public mobjFLDR As Outlook.MAPIFolder
Public mobjAPPT As Outlook.AppointmentItem
'##CDO (COLLABORATION DATA OBJECTS) VARIABLES##
Public mobjCDOSession As MAPI.Session
Public mobjMAPIAppt As MAPI.Message
Public BolOutlookIS As Boolean
'##PUBLIC VARIABLES FOR SETTING & DELETING APPOINTMENTS##
Public intDateNote As Integer
Public strAppSubject As String
Public dteReccDate As Date
Public tmeSetTime As Date
Public strAppRecr As String
Public strAppLoc As String
Public strAppBody As String
Public intMinutes As Integer
Public strImportance As String
Public bolAllDay As Boolean
Public strBusyStats As String
Add this to the modOutlookPublic module and use it to create your appointment settings.
Code:
Public Sub GetAppDetails()
'--------------------------------------------------------------
'####SET THESE DETAILS TO BE YOUR APPOINTMENT REQUIREMENTS####
'#############################################################
'--------------------------------------------------------------
'##SET THE MEETING LOCATION HERE##
strAppLoc = "Office"
'--------------------------------------------------------------
'##SET THE RECCURING PERIOD HERE WHICH IS OPTIONAL IN WHICH CASE,
'COMMENT OUT IN AddAppClosedOutlk##
'strAppRecr = olRecursDaily
'strAppRecr = olRecursWeekly
strAppRecr = olRecursMonthly
'strAppRecr = olRecursYearly
'--------------------------------------------------------------
'##SET IF YOU WANT AN ALL DAY APPOINTMENT##
bolAllDay = True
'bolAllDay = False
'--------------------------------------------------------------
'##SET THE AMOUNT OF MINUTES TO ALERT ON THIS APPOINTMENT##
intMinutes = "30"
'--------------------------------------------------------------
'##SET THE IMPORTANCE HERE##
'strImportance = olImportanceLow
'strImportance = olImportanceNormal
strImportance = olImportanceHigh
'--------------------------------------------------------------
'##SET THE BUSY STATUS HERE##
strBusyStats = olBusy
'strBusyStats = olFree
'strBusyStats = olOutOfOffice
'strBusyStats = olTentative
'--------------------------------------------------------------
'##SET THE APPOINTMENT SUBJECT##
strAppSubject = "My custom subject"
'--------------------------------------------------------------
'##SET THE BODY TEXT HERE## ( YOU CAN USE & VbCrlf & _ TO ADD NEW LINES
strAppBody = vbCrLf & _
"This is line 1" & vbCrLf & _
vbCrLf & _
"this is line 2" & vbCrLf & _
"this is line 3" & vbCrLf & _
"this is line 4" & vbCrLf & _
"this is line 5" & vbCrLf & _
vbCrLf & _
"this is line 6" & vbCrLf & _
vbCrLf & _
"this is line 7"
'--------------------------------------------------------------
'##############################################################
'--------------------------------------------------------------
'###DO NOT CHANGE ANYTHING BELOW THIS LINE!###
'##DO NOT CHANGE THIS DATE##
dteReccDate = Format(Date, "dd mm yyyy")
'##DO NOT CHANGE THIS DATE##
tmeSetTime = Format(Now, "hh:mm")
tmeSetTime = "09:30"
'--------------------------------------------------------------
End Sub
create a new module called modOutlookAppts.
Add this code to the new module
Code:
Public Sub AddAppClosedOutlk()
'THIS WILL CREATE AN APPOINTMENT USING THE SETTINGS CREATED IN GetAppDetails
Dim outMail As Outlook.AppointmentItem
Dim strAppStrt As String
Dim strAppEnd As String
'get the user preferences
GetAppDetails
'set the outlook connection
Set mobjOLA = CreateObject("Outlook.Application")
Set mobjNS = mobjOLA.GetNamespace("MAPI")
mobjNS.Logon , , False, False
Set mobjFLDR = mobjNS.GetDefaultFolder(olFolderCalendar)
On Error Resume Next
Set mobjAPPT = mobjFLDR.Items(strAppSubject)
'check for appointment set. if it is, bail and go to bottom
If err.Number = 0 Then GoTo FoundObject
'add the start & end time to the set date
strAppStrt = dteReccDate & " " & tmeSetTime
strAppEnd = dteReccDate & " " & (tmeSetTime + 1)
'collect the user preferences and add to the appointment
Set outMail = Outlook.CreateItem(olAppointmentItem)
With outMail
.Subject = strAppSubject
.Location = strAppLoc
.start = strAppStrt
.End = strAppEnd
.GetRecurrencePattern = strAppRecr
.Body = strAppBody
.Importance = strImportance
.AllDayEvent = bolAllDay
.ReminderMinutesBeforeStart = intMinutes
.Save
End With
'show message if appointment is created
MsgBox ("Success!! Your appointment has been placed")
Bye:
'close the connection
Set mobjAPPT = Nothing
Set mobjFLDR = Nothing
Set mobjNS = Nothing
Set mobjOLA = Nothing
Exit Sub
'arrive here if the appointment is already set
FoundObject:
MsgBox "Halt!! Appointment Already Placed!", _
vbOKOnly, "Information"
GoTo Bye
End Sub
add this to the new module
Code:
Public Sub ExampleCall_DeleteBySubject()
'do not change anything in this routine
'THIS WILL DELETE THE APPOINTMENT USING THE SETTINGS IN GetAppDetails
'Call the Appointment details to find
GetAppDetails
Call DeleteAppointmentItemBySubject(strAppSubject)
End Sub
Public Sub DeleteAppointmentItemBySubject(strAppSubject As String)
'do not change anything in this routine
' Use this method ONLY if the subject of the
' Appointment Item is known and is UNIQUE.
Set mobjOLA = CreateObject("Outlook.Application")
Set mobjNS = mobjOLA.GetNamespace("MAPI")
mobjNS.Logon , , False, False
Set mobjFLDR = mobjNS.GetDefaultFolder(olFolderCalendar)
On Error Resume Next
Set mobjAPPT = mobjFLDR.Items(strAppSubject)
If err.Number <> 0 Then GoTo CannotFindObject
mobjAPPT.Delete
MsgBox ("Success!! Your appointment has been deleted")
Bye:
Set mobjAPPT = Nothing
Set mobjFLDR = Nothing
Set mobjNS = Nothing
Set mobjOLA = Nothing
Exit Sub
CannotFindObject:
MsgBox "Cannot find Appointment Item to delete.", _
vbOKOnly, "Information"
GoTo Bye
End Sub
to add the appointment, call this routine
Code:
AddAppClosedOutlk
to delete the appointment, call this routine
Code:
ExampleCall_DeleteBySubject
you can either create a table and keep the settings there and alter the GetAppDetails to
collect the settings or hard code the settings.
in your case, alter the settings to collect the date in which your appointment is set via and use the same settings to delete.
the routine looks for the subject to delete.
give me a shout if you struggle and i'll set up a demo
Is there a way of grabbing certain content from a form? I want to add the appointment by clicking on a command button (or maybe running on an afterupdate feature for a text box - haven't decided yet), and want it to grab certain features from the form and incorporate these into the reminder - specifically:
* Date of reminder
* Record ID
* Two specific fields to identify which record the reminder is for
Basically I want it to automagically populate calendar items but use some of the info on the record being displayed to customise each reminder.
Ahar, yes I hadn't noticed the outlook reference wasn't ticked also! Thanks Now trying to figure out how to use the data from different forms within a text entry (as in, within the appointment title or within the text for the reminder) as simply using the Forms!formname.control just prints this and doesn't bring anything in.
Now trying to figure out how to use the data from different forms within a text entry (as in, within the appointment title or within the text for the reminder) as simply using the Forms!formname.control just prints this and doesn't bring anything in.
When a date is entered into a control source (this is the date of an incident occurring), 9 other control sources are populated with dates via VBA - these are deadlines by which a task must be completed (such as a report sent, a meeting held, and so forth).
What I want to happen is that when the first date is populated, an outlook appointment is made for each of the 9 deadline dates above automatically. The subject line for each should be "Reminder for Record <RecordID> Deadline for <control source name>" and the date should be taken from the control source.
If possible a nicety would be that if the incident date were changed (it shouldn't be) or any of the other dates manually overwritten, the calendar appointment should be amended (but this is very much a "would like" rather than a "need").
OK I've figured out how to get this working I think - I'm going to make a separate module for each field that I want the appointments made for (9 modules) which are a copy of the original modOutlookAppts but with the different information for each field.
I'll then create individual functions for each one:
GetAppDetailsField1
GetAppDetailsField2
and so forth.
My next dilemma is how do I have it fire off when the field is changed? I tried using afterupdate but as the field itself is not being updated (it is changing as a result of some VBA code which populates them based on the date of the initial incident) the function is not running. Not sure what "change" does but it didn't run when I tried this one either.
Personally, rather than 9 modules with 1 function in each, I'd have 1 module with 9 functions in it. In fact, if they're all always running together anyway, I might do it all with 1 function.
It sounds like it's all triggered by the user entering a date into the first control, so it sounds like you want to fire off your function(s) from the after update event of that control. You also may need to account for the user entering a date and then changing it, which would create 2 sets of appointments. Perhaps doing it behind a button they click when they're done would work better.
I have a routine that will delete an appointment based on a search criteria. I always set my settings in one public routine and called it with either the add or delete routine to save me adding lots of stuff and losing track. This way, my delete would always be using and searching for the same data as the add routine.
If a user updated a date ( if this is the trigger ) , you could search the appointments and if it exists, delete it and re add a new one
alternatively, you could use tasks instead of appointments to track your progress.
If you need anything, give a simple definition and maybe a sample db ( pm if you prefer ) and I'll put one together to look at
