By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,258 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

How to create a Outlook calendar entry from a date in Excel using VBA...

P: 2
Hello -

disclaimer - I'm a super-novice at VBA -

I have VBA code that I found on the internet to create a calendar appointment entry in Outlook. I'm trying to modify this code so that the date of the calendar entry is obtained from cell D3 in my Excel spreadsheet - instead of being hard coded into the VBA. Initiatlly cell D3 was formatted as a date, but since I couldn't get that to work, I converted it to text (#12/2/2012#)

Can anyone help me find a way to getthe date value for the calendar entry from Excel?

Here is my code:

Expand|Select|Wrap|Line Numbers
  1. Dim objOL 'As Outlook.Application
  2.         Dim objAppt 'As Outlook.AppointmentItem
  3.         Dim nDate As String
  4.         Const olAppointmentItem = 1
  5.         Const olMeeting = 1
  6.         Const olFree = 0
  7.                 Range("d3").Select
  8.                 nDate = ActiveCell
  9.         Set objOL = CreateObject("Outlook.Application")
  10.         Set objAppt = objOL.CreateItem(olAppointmentItem)
  11.         objAppt.Subject = "Holiday 1"
  12.         objAppt.Start = nDate
  13.         objAppt.AllDayEvent = True
  14.         objAppt.BusyStatus = 3
  15.         objAppt.Save
  16.         Set objAppt = Nothing
  17.         Set objOL = Nothing
Oct 23 '12 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 10K+
P: 12,366
Did you try using the CDate() function to convert the string to a date?
Oct 23 '12 #2

P: 2
Hi there, I didn't know to try that, but I just did and it worked! Thanks so much!
Oct 23 '12 #3

Expert Mod 10K+
P: 12,366
No problem, good luck with the rest of your project.
Oct 23 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.