Set appointment time

G

GLHEC-BLS

I have created a macro that schedules an appointment for a follow up based on
the current date and time. The follow up has to be scheduled for the next day
at 8 a.m. I'm not sure about the best way to do that. I can only get it to
except a specific date and time and not a variable date (one dat from the
current date) and a specific time. Here is what I have that schedules a
follow up one day from the current date two hours ahead.

olAppt.Start = Now() + (1#) + (2# / 24#)

But if it is Thursday then I need it to schedule it one day from the current
date at 8 a.m. I've tried something that looks like this, and slight
variations, but it doesn't work. The only part that doesn't work is the
#8:00:00# AM part.

If Weekday(dteNextDate) = 5 Then
olAppt.Start = Now() + (1#) & #8:00:00 AM#

Any suggestions? Is it even possible?
 
S

Sue Mosher [MVP]

Now() returns the current date/time. Date() returns the current date, with a
time of midnight. Use Date() with DateAdd() to return tomorrow's date with a
time of 8 a.m.

DateAdd("h", 8, Date+1)

You're on the right track with Weekday() if you need to adjust for weekends.
 
G

GLHEC-BLS

That is awesome! Works great thanks a lot. I can't believe I couldn't find
anything in the help section about Date() or Dateadd(). I still can't. Oh
well, that's what this discussions are for I guess.

Just one more question for my own curiosity. What does the "h" do? It
wouldn't work wihtout it, but I'm not sure what purpose it serves.

P.S. The weekday() works great for skipping weekends if it is Friday. It's
also what I use for the code you helped me with because if it is Thursday I
need to schedule the folow up for the next day (friday) at 8 a.m.

Thanks again!
 
S

Sue Mosher [MVP]

The easiest way to access Help in VBA is to put the cursor on the function,
method, or property you're interested in and then press F1. What "h" means
will be spelled out once you look at the Help topic for DateAdd(), but see
if you can figure it out for yourself: What might "h" and 8 have in common
with your desired time?
--
Sue Mosher, Outlook MVP
Author of Microsoft Outlook 2007 Programming:
Jumpstart for Power Users and Administrators
http://www.outlookcode.com/article.aspx?id=54
 
G

GLHEC-BLS

Yeah, I figured out that 'h" meant hour right after I posted the question.
Sort of felt silly. I did find it in the help section too. I have used
modified versions of it already in other areas as well. I was able to make a
combobox where the user could select the time they wanted to make the
follow-up, which is ten times better than my original idea. Here is what it
looks like, out of context:

ElseIf ComboBox2.Value = "9 a.m." Then
If Weekday(Now) = 6 Then
olAppt.Start = DateAdd("h", 9, Date + 3)
Else: olAppt.Start = DateAdd("h", 9, Date + 1)
End If


Thanks for the tip on the help feature. That should save me some time
searching fro solutions. You have been extremley helpful! Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top