Set Reminder in Outlook (2007) from Excel VBA (2007)

  • Thread starter Thread starter Rick S.
  • Start date Start date
R

Rick S.

Can a macro from Excel VBA create a Reminder in Outlook?
If yes, can some one help getting me started on this?

I am pretty good with Excel VBA but not with Outlook VBA commands and I do
not know interoperability between programs.
 
I found this code in an MS help but I do not understand how to make it work.
'======
Sub AddAppointment()
Dim apti As Outlook.AppointmentItem 'This line errors from within Excel
2007

Set apti = Application.CreateItem(olAppointmentItem)
apti.Subject = "Car Servicing"
apti.Start = DateAdd("n", 16, Now)
apti.End = DateAdd("n", 60, apti.Start)
apti.ReminderSet = True
apti.ReminderMinutesBeforeStart = 60
apti.Save
End Sub
'======

Reading the code this appears to be in the direction I want, I just dont
know how to apply it.
 
After reading all kinds of posts and using bits and pieces, I managed to get
something working to set a "Task".

But like others have posted, I can not get the "Reminder" to set in the task.
This code sends an email and a Task.

'======
Sub SendEmail()
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim sRecipient As String
Dim oNameSpace As Object
Dim dDate As Date

dDate = TextBox8.Value

sRecipient = InputBox("Enter Your Email Address Here! (Example:
""richard.stanich"". " _
& "Do not add ""@aerodeisgnmfg.com"", it is automatically appended.")
sRecipient = LCase(sRecipient) & "@aerodesignmfg.com"

Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNamespace("MAPI")
oNameSpace.Logon , , True

Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add(sRecipient)
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "This is an automatic email notification: CORRECTIVE
ACTION REQUEST (CAR)"
.Body = "You have been assigned CAR " & TextBox2.Value & ". " _
& "Please complete the CAR by " & TextBox8.Value & ". " _
& "The following link will take you to the folder containg
this CAR record." _
& " Q:\Qadocs\CORRECTIVE ACTIONS\" & sVar & " Correctice
Actions\" '& _
TextBox2.Value & ".xls"

'Add hyperlink above?

' .Attachments.Add ("filename") 'you only need this if
'you are sending attachments?
.Display 'use .Send when all testing done

'add outlook appointment 10.28.08
Dim myOlApp As Outlook.Application 'ERROR: user defined type not defined?
Dim myOlTask As Outlook.TaskItem

Set myOlApp = CreateObject("Outlook.Application")
Set myOlTask = myOlApp.CreateItem(olTaskItem)

With myOlTask
.Subject = "A CAR " & TextBox2.Value & " has been assigned to you,
due before " & TextBox8.Value
.Body = "You have been assigned CAR " & TextBox2.Value & ". " _
& "Please complete the CAR by " & TextBox8.Value & ". " _
& "The following link will take you to the folder containg
this CAR record." _
& " Q:\Qadocs\CORRECTIVE ACTIONS\" & sVar & " Correctice
Actions\"
.DueDate = dDate
.Importance = olImportanceHigh
.ReminderSet = True 'Not Setting Reminder?
.ReminderTime = dDate - 1 'Not being set?
.Recipients.Add (sRecipient)
.Assign
.Send
End With
Set myOlTask = Nothing
Set myOlApp = Nothing
'end 10.28.08

End With

Set oRecipient = Nothing
Set oMailItem = Nothing
Set oNameSpace = Nothing
Set oOutlook = Nothing
End Sub
'======
--
Regards

XP Pro
Office 2007
 
As you already have seen, the reminders are set differently:
ReminderMinutesBeforeStart for an appointment, and ReminderTime for the
taskitem. For both you can't get a reminder in the past (that's why the
second sample doesn't work).

BTW: For programming issues there's another newsgroup:
microsoft.public.outlook.program_vba

--
Best regards
Michael Bauer - MVP Outlook

: Outlook Categories? Category Manager Is Your Tool
: VBOffice Reporter for Data Analysis & Reporting
: <http://www.vboffice.net/product.html?pub=6&lang=en>


Am Tue, 28 Oct 2008 06:22:16 -0700 schrieb Rick S.:
 
After reading all kinds of posts and using bits and pieces, I managed to
get
something working to set a "Task".

But like others have posted, I can not get the "Reminder" to set in the
task.
This code sends an email and a Task.
Click to expand...

Coding posts belong in microsoft.public.outlook.program_vba or
..program_addins, not in .general.
 
Brian, Micheal
I realized I posted in the wrong forum obviously too late, I dont see a move
option either?

On to my post.
I have learned you can set a reminder but once it has been received the
value is set to False.

I have changed my plans to send a "delayed" email.
--
Regards

XP Pro
Office 2007
 
Back
Top