Calender Issues

[littlegreenmen1]

I'm a new VBA user and have been trying to learn how to expor
information from a particular worksheet to Outlook calender. i hav
read the tutorial on www.dicks-clicks.com and have gotten that to wor
just fine. What I'm running right now is (i'm want to use late bindin
as opposed to early binding):

Private Sub CommandButton2_Click()

Dim olApp As Object
Dim olApt As Object

Set olApp = CreateObject("Outlook.application")
Set olApt = olApp.CreateItem(1)

With olApt
.Start = Date + 1 + TimeValue("19:00:00")
.End = .Start + TimeValue("00:30:00")
.Subject = "Piano lesson"
.Location = "The teachers house"
.Body = "Don't forget to take an apple for the teacher"
.BusyStatus = 2
.ReminderMinutesBeforeStart = 120
.ReminderSet = True
.Save
End With

Set olApt = Nothing
Set olApp = Nothing

End Sub

How to I use information from particular cells as opposed to typing i
in VBA. for example, instead of ".Start = Date + 1
TimeValue("19:00:00")" how would i have it pull the data from, say
cell E1 (which could be simply be "6/6/05" for example). so, basicall
i'm looking for .Start=E1. I also want the other information to b
pulled from other cells. so instead of typing ".Body = "Don't forget t
take an apple for the teacher'" how do I get .Body to pull and displa
the text from cell E2 for example. Much thanks to those that help m
solve my quandary

 

[Robin Hammond]

It's quite simple. You just reference the cell as follows

.Start = cdate(sheets(1).Range(e1).value) + 1 + TimeValue("19:00:00")
.End = .Start + TimeValue("00:30:00")
.Subject = "Piano lesson"
.Location = "The teachers house"
.Body = sheets(1).range(e2).value

I have added what appear to be missing dots at the start of these lines and
qualified the sheet explicitly in case the sheet is not active.

You should also watch out with Outlook when using CreateObject since Outlook
doesn't run in multiple instances. First use (untested, just writing this on
the fly):

On error resume next
Set olApp = GetObject("Outlook.Application")
on error goto 0
if olApp is nothing then
set OlApp = CreateObject("Outlook.Application")
End if

Robin Hammond
www.enhanceddatasystems.com

"littlegreenmen1"

 

[littlegreenmen1]

I appreciate the help. When i run the program now I get a run-time
error on the .Start line and on the .Body line. Below is a copy of my
finished code. Any suggestions are most appreciated.

Private Sub CommandButton2_Click()

Dim olApp As Object
Dim olApt As Object

Set olApp = CreateObject("Outlook.application")
Set olApt = olApp.CreateItem(1)

With olApt
..Start = CDate(Sheets(1).Range(e1).Value) + 1 +
TimeValue("19:00:00")
..End = .Start + TimeValue("00:30:00")
..Subject = "Piano lesson"
..Location = "The teachers house"
..Body = Sheets(1).Range(e2).Value
..BusyStatus = 2
..ReminderMinutesBeforeStart = 120
..ReminderSet = True
..Save
End With

Set olApt = Nothing
Set olApp = Nothing

End Sub

 

[littlegreenmen1]

I solved the run-time error. it needed to be .Range("E1"). once
included the " " it worked fine. thank you very much for your help

 

[littlegreenmen1]

I have modified the Calendar program to do what I need with on
exception. I have schedules that I need to export from severa
different Excel files. I need to run the exporter from each fil
everyday to update my Calendar. How can I have the macro erase al
items from the Outlook Calendar with a given address before it runs b
schedule exporting program? In other words: I run the Macro; it scan
the outlook calendar and erases all entries with an address of "12
Anywhere St"; then it scans my list of dates and copies them to th
Calendar. I already have the part written that scans my list of date
and copies them to Excel, it's just the erasing part I need help with.
Thank you very much