Hello,
I case control sheet in Excel, I need to follow-up on cases I work on so I
use Outlook to create follow-up reminders after I mail out documents to
individuals.
I run Outlook in the background all the time.
I figured how to add a cmd button to excel to launch Outlook
(Application.ActivateMicrosoftApp xlMicrosoftMail). I would like to take it a
step farther and be able to Launch Outlook and display a new blank
appointment for todays date so that I may create a new appointment and add a
reminder.

Thank you.

Try this

Public Sub MakeOutlookAppointment()
Dim Outlook As Object
Dim Appointment As Object
Const Item = 1

Set Outlook = CreateObject("Outlook.Application")
Set Appointment = Outlook.CreateItem(Item)

Appointment.Subject = Range("A2").Value
Appointment.Start = Format(Range("B2").Value, "hh:mm AMPM") + _
Format(Range("C2").Value, "mm/dd/yyyy")

Appointment.End = Format(Range("D2").Value, "hh:mm AMPM") + _
Format(Range("E2").Value, "mm/dd/yyyy")

Appointment.ReminderPlaySound = True
Appointment.Save

Outlook.Quit
Set Outlook = Nothing
End Sub



"Memphis" wrote:

Hello,
I case control sheet in Excel, I need to follow-up on cases I work on so I
use Outlook to create follow-up reminders after I mail out documents to
individuals.
I run Outlook in the background all the time.
I figured how to add a cmd button to excel to launch Outlook
(Application.ActivateMicrosoftApp xlMicrosoftMail). I would like to take it a
step farther and be able to Launch Outlook and display a new blank
appointment for todays date so that I may create a new appointment and add a
reminder.

Thank you.

This Procedure will allow you to Create a table of appointments on a
spreadsheet
Using the folloing format;

Subject, Start time, Start Date, End Time, End Date

This will create an appointment for each item on the table
Hope that helps.

Public Sub MakeOutlookAppointment()
Dim Outlook As Object
Dim Appointment As Object
Dim tableEnd As Long
Dim i As Long
Const Item = 1

tableEnd = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set Outlook = CreateObject("Outlook.Application")

For i = 1 To tableEnd
Set Appointment = Outlook.CreateItem(Item)

Appointment.Subject = Range("A" & i).Value
Appointment.Start = Format(Range("B" & i).Value, "hh:mm AMPM") + _
Format(Range("C" & i).Value, "mm/dd/yyyy")

Appointment.End = Format(Range("D" & i).Value, "hh:mm AMPM") + _
Format(Range("E" & i).Value, "mm/dd/yyyy")

Appointment.ReminderPlaySound = True
Appointment.Save
Next
Outlook.Quit
Set Outlook = Nothing
End Sub

"Memphis" wrote:

Hello,
I case control sheet in Excel, I need to follow-up on cases I work on so I
use Outlook to create follow-up reminders after I mail out documents to
individuals.
I run Outlook in the background all the time.
I figured how to add a cmd button to excel to launch Outlook
(Application.ActivateMicrosoftApp xlMicrosoftMail). I would like to take it a
step farther and be able to Launch Outlook and display a new blank
appointment for todays date so that I may create a new appointment and add a
reminder.

Thank you.

This is definitely more than I asked for. Thank you so much.

I am using Office 2003. I copied your first code and after running it I get
a "Type mismatch (Error 13)"
I do not know what to make of it. I will try to work out the error later,
unless you can shed some light on this.

For the time being, how can I launch Outlook and automatically have it pop
up a new blank appointment on the screen for me to manually fill out with the
subject, date and time?

Thank you

"Office_Novice" wrote:

Try this

Public Sub MakeOutlookAppointment()
Dim Outlook As Object
Dim Appointment As Object
Const Item = 1

Set Outlook = CreateObject("Outlook.Application")
Set Appointment = Outlook.CreateItem(Item)

Appointment.Subject = Range("A2").Value
Appointment.Start = Format(Range("B2").Value, "hh:mm AMPM") + _
Format(Range("C2").Value, "mm/dd/yyyy")

Appointment.End = Format(Range("D2").Value, "hh:mm AMPM") + _
Format(Range("E2").Value, "mm/dd/yyyy")

Appointment.ReminderPlaySound = True
Appointment.Save

Outlook.Quit
Set Outlook = Nothing
End Sub



"Memphis" wrote:

Hello,
I case control sheet in Excel, I need to follow-up on cases I work on so I
use Outlook to create follow-up reminders after I mail out documents to
individuals.
I run Outlook in the background all the time.
I figured how to add a cmd button to excel to launch Outlook
(Application.ActivateMicrosoftApp xlMicrosoftMail). I would like to take it a
step farther and be able to Launch Outlook and display a new blank
appointment for todays date so that I may create a new appointment and add a
reminder.

Thank you.

Maybe somthing like this is more acceptable =o)

Option Explicit

Public Sub NewOutlookAppointment()
Dim Outlook As Object
Dim Appointment As Object

Set Outlook = CreateObject("Outlook.Application")


Set Appointment = Outlook.CreateItem(1)

Appointment.Display
End Sub


"Memphis" wrote:

This is definitely more than I asked for. Thank you so much.

I am using Office 2003. I copied your first code and after running it I get
a "Type mismatch (Error 13)"
I do not know what to make of it. I will try to work out the error later,
unless you can shed some light on this.

For the time being, how can I launch Outlook and automatically have it pop
up a new blank appointment on the screen for me to manually fill out with the
subject, date and time?

Thank you

"Office_Novice" wrote:

Try this

Public Sub MakeOutlookAppointment()
Dim Outlook As Object
Dim Appointment As Object
Const Item = 1

Set Outlook = CreateObject("Outlook.Application")
Set Appointment = Outlook.CreateItem(Item)

Appointment.Subject = Range("A2").Value
Appointment.Start = Format(Range("B2").Value, "hh:mm AMPM") + _
Format(Range("C2").Value, "mm/dd/yyyy")

Appointment.End = Format(Range("D2").Value, "hh:mm AMPM") + _
Format(Range("E2").Value, "mm/dd/yyyy")

Appointment.ReminderPlaySound = True
Appointment.Save

Outlook.Quit
Set Outlook = Nothing
End Sub



"Memphis" wrote:

Hello,
I case control sheet in Excel, I need to follow-up on cases I work on so I
use Outlook to create follow-up reminders after I mail out documents to
individuals.
I run Outlook in the background all the time.
I figured how to add a cmd button to excel to launch Outlook
(Application.ActivateMicrosoftApp xlMicrosoftMail). I would like to take it a
step farther and be able to Launch Outlook and display a new blank
appointment for todays date so that I may create a new appointment and add a
reminder.

Thank you.

You are the "Man"!
This is exactly what i was looking for.

Thank you so much.


"Office_Novice" wrote:

Maybe somthing like this is more acceptable =o)

Option Explicit

Public Sub NewOutlookAppointment()
Dim Outlook As Object
Dim Appointment As Object

Set Outlook = CreateObject("Outlook.Application")


Set Appointment = Outlook.CreateItem(1)

Appointment.Display
End Sub


"Memphis" wrote:

This is definitely more than I asked for. Thank you so much.

I am using Office 2003. I copied your first code and after running it I get
a "Type mismatch (Error 13)"
I do not know what to make of it. I will try to work out the error later,
unless you can shed some light on this.

For the time being, how can I launch Outlook and automatically have it pop
up a new blank appointment on the screen for me to manually fill out with the
subject, date and time?

Thank you

"Office_Novice" wrote:

Try this

Public Sub MakeOutlookAppointment()
Dim Outlook As Object
Dim Appointment As Object
Const Item = 1

Set Outlook = CreateObject("Outlook.Application")
Set Appointment = Outlook.CreateItem(Item)

Appointment.Subject = Range("A2").Value
Appointment.Start = Format(Range("B2").Value, "hh:mm AMPM") + _
Format(Range("C2").Value, "mm/dd/yyyy")

Appointment.End = Format(Range("D2").Value, "hh:mm AMPM") + _
Format(Range("E2").Value, "mm/dd/yyyy")

Appointment.ReminderPlaySound = True
Appointment.Save

Outlook.Quit
Set Outlook = Nothing
End Sub



"Memphis" wrote:

Hello,
I case control sheet in Excel, I need to follow-up on cases I work on so I
use Outlook to create follow-up reminders after I mail out documents to
individuals.
I run Outlook in the background all the time.
I figured how to add a cmd button to excel to launch Outlook
(Application.ActivateMicrosoftApp xlMicrosoftMail). I would like to take it a
step farther and be able to Launch Outlook and display a new blank
appointment for todays date so that I may create a new appointment and add a
reminder.

Thank you.

With your help, I was able to put this together. I just can't figure out how
to activate the reminder feature in outlook to alert me of the "follow-up"
the day of the appointment. Any suggestions? I also posted this question on
the outlook forum.
Thanks

Private Sub CommandButton1_Click()
Dim Outlook As Object
Dim Appointment As Object
Dim Category As String
Dim Time As Variant

Const Item = 1


Set Outlook = CreateObject("Outlook.Application")
Set Appointment = Outlook.CreateItem(1)
Category = "Business, Competition, Favorites"
Time = "08:30AM"

Appointment.Subject = Range("A1")

Appointment.Start = DateAdd("d", 15, Range("C1")) & " " & Time

Appointment.Body = Range("I1")
Appointment.Categories = Category

Appointment.ReminderPlaySound = True
Appointment.Display

'Outlook.quit
'Set Outlook = Nothing
End Sub