Okay, so by now you should know that I like tinkering...and showing you how I do it. See the following for two examples:
Today's lesson will be in VBA for Outlook. The challenge? Schedule a meeting/appointment on a public calendar for all to see while simultaneously sending a specific person a one day reminder to take care of the newly scheduled event. Simply put, control and manipulate both public and private calendars.
Time for the pictures! Here's what I came up with using the Outlook VBE, referencing the Calendar control:
Simple right? Fill in the fields, pick a day, then Submit. Here's the result of hitting that li'l Submit button:
Public calendar also gets updated so others can schedule around what is going on.
Before I show you the code
If you do not know what VBA is or how to access it in Outlook, go figure that out first. The form (Article Scheduler) at the top of this page lives here in the Outlook VBE:
You'll need to create the form with the control names I have in the code below. Also, to run this from your Outlook toolbar, create a new Module (I have two above). In the new module, enter these three lines of code:
Once complete, you can drag the macro button to your toolbar.
This is not a tutorial. Rather, it is an example you can tailor to your own needs by examining the code and changing what you want to get the desired effect. A litlle VBA research on your part may be in order.
The Code (Put this in the code section for the Scheduler form):
Dim ola As Outlook.AddressList
Dim ole As Outlook.AddressEntry
Dim WriteDate As Object 'Date
Dim EmailAddy As String
Private Sub Calendar1_Click()
txtMsg.Text = ""
Private Sub CheckBox1_Click()
CheckBox1.Value = Not CheckBox1.Value
Private Sub ComboBox1_Change()
txtMsg.Text = ""
Private Sub CommandButton1_Click()
Dim myItem As Object
Dim myRequiredAttendee, myOptionalAttendee, myResourceAttendee As Outlook.Recipient
If ComboBox1.Text = "" Then MsgBox("Really? Step 1 is entering an author's name.")
If CheckBox1.Value = True Then
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
EmailAddy = ComboBox1.Value
WriteDate = Calendar1.Value & " 8:00 AM"
myItem = Application.CreateItem(olAppointmentItem)
' Add the To recipient(s) to the message.
myRequiredAttendee = .Recipients.Add(EmailAddy)
myRequiredAttendee.Type = olTo
' Resolve each Recipient's name.
For Each myRequiredAttendee In .Recipients
myItem.MeetingStatus = olMeeting
myItem.Subject = "Write an article for tomorrow, due at 8am."
If txtTitle.Text <> "" Then
myItem.Body = txtTitle.Text & " for " & txtForum.Text & "."
myItem.Body = "Write an article for tomorrow, due at 8am."
myItem.Location = "Your Desk."
myItem.Start = WriteDate
myItem.Duration = 90
myItem.ReminderMinutesBeforeStart = 1440
myItem.ReminderSet = True
myRequiredAttendee = myItem.Recipients.Add(EmailAddy)
myRequiredAttendee.Type = olRequired
ComboBox1.Value = ""
txtMsg.Text = "Reminder sent to " & EmailAddy & "."
Dim myNameSpace As Outlook.NameSpace
Dim myFolder As Outlook.folder
Dim myNewFolder As Outlook.AppointmentItem
myNameSpace = Application.GetNamespace("MAPI")
myFolder = myNameSpace.Folders.Item(3)
SubFolder = myFolder.Folders("All Public Folders").Folders("Your Public Sub Calendar").Items.Add(olAppointmentItem)
.Subject = EmailAddy
.Start = WriteDate
Private Sub UserForm_Initialize()
Calendar1.Value = Now
ola = Application.Session.AddressLists("Global Address List")
For Each ole In ola.AddressEntries
ola = Nothing
ole = Nothing