Creating Outlook Appointment

[Guest]

Hi again -
I am trying to write a macro that when a button is clicked on a spreadsheet,
it will use the data entered on the new row to create an appointment in
Outlook...

The area that I am experiencing difficulty in is the appointment time,
duration and body text.

Here is my code:
Sub SetAppt()

Dim olApp As Outlook.Application
Dim Appt As Outlook.AppointmentItem

Set olApp = CreateObject("Outlook.Application")
Set Appt = olApp.CreateItem(olAppointmentItem)

With Appt
.Start = Range("L7").Value
.End = Range("L7").Value
.Subject = Range("I7").Value & Range("a7").Value & Range("d7").Value
.Body = Range("e7").Value
.ReminderSet = False
End With
Set Appt = Nothing
Set olApp = Nothing

End Sub

Any help is appreciated.

 

[Steve Yandl]

Here is an example of a sub that makes a set of appointments where start
times are in column A and location in Column B. I set a reference to the
Microsoft Outlook object library from 'Tools > References'

Sub ScheduleAppts()
Dim ol As New Outlook.Application
Dim ns As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim appt As Outlook.AppointmentItem
Dim R As Integer
Dim X As Integer

R = Range("A65536").End(xlUp).Row

Set ns = ol.GetNamespace("MAPI")
Set olFolder = ns.GetDefaultFolder(olFolderCalendar)

For X = 1 To R
Set appt = olFolder.Items.Add
With appt
.Start = Sheets("Sheet1").Cells(X, 1).Value
.Location = Sheets("Sheet1").Cells(X, 2).Value
.Save
End With
Next X

Set ol = Nothing
Set ns = Nothing
Set appt = Nothing
End Sub


Steve

 

[Guest]

Hi,

With start/end date of 24/09/2006 15:00 in L7 I found that this is fine
(with reference selected to MS Outlook Object library in Tools, References)

Try App.Display or App.Save after your End With statement ?

Anthony

 

[Guest]

Sorry, that should be Appt.Display or Appt.Save,
not App.Display or App.Save (my first post got 'lost'!)

Anthony

 

[Guest]

Thanks,
I did have the .Save statement, just didn't in the portion I cut and pasted
here. I ended up changing my data some and it works now. My Date and Time
were in separate fields, now they in one cell it works.

Now I am trying to add the user name to the body text with:

Dim olApp As Outlook.Application
Dim Appt As Outlook.AppointmentItem

Set olApp = CreateObject("Outlook.Application")
Set Appt = olApp.CreateItem(olAppointmentItem)

With Appt
.Start = Range("K7").Value
.End = Range("K7").Value
.Subject = Range("H7").Value & "/ " & Range("A7").Value & " / " &
Range("D7").Value
.Body = "Contact: " & Range("e7").Value & vbNewLine & "Contact
Phone: " & Range("F7").Value & vbNewLine & "Notified: " & Range("G7").Value
& vbNewLine & "PO Verified & Entered by: " & Environ(Username)
.ReminderSet = False
.Save
End With

Set Appt = Nothing
Set olApp = Nothing


But I am getting an Invalid Procedure call (error 5)

Any ideas?

 

[Guest]

Hi Steve -
Thanks for the response!!

Two questions though:
Can you explain what you are doing with the X and R commands and I see you
are setting the Mapi folder, to the default one, but how can I set it to a
Public folder? The folder I actually want the appointments in is like a 3rd
level Public folder (Public\Division1\Shipping Calendar).

 

[Guest]

Hi,
Thanks for your feedback.
Just a minor modification in the Environ call required,
to supply username as a string.
Environ("Username")

Hth
Anthony

 

[Guest]

so just add a

set username as string

statement?

 

[Anthony D]

Indeed yes.

Dim username As String
username = "Username"

and

Environ(username)

or

Environ("Username")

both work fine.

Anthony