Excel dates to Outlook
Excel dates to Outlook
(OP)
I'm new to VBA. Is there a way to insert dates into an Excel spreadsheet and have the dates and other info (names, etc) get inserted into a shared Outlook calendar?
Thanks.
Thanks.
Chris
Systems Analyst, I.S.
SolidWorks 06 4.1/PDMWorks 06
AutoCAD 06
ctopher's home (updated 06-21-06)





RE: Excel dates to Outlook
http://www.dicks-clicks.com/excel/olAutomating.htm
You will have to consider the binding of the Outlook commands as described on that site.
I used this info as a model to set up a worksheet macro, see code below, that creates an Outlook appointment based on the values in an Excel worksheet, which I placed in Excel cells named "date_default" and "subject". Note my code always assigns the same time, but you could use named Excel references instead. Very basic, but it works for me.
Good luck!
CODE
Dim olApp As Object
' Dim olApp As Outlook.Application
Dim olApt As Object
Dim olNs As Object
' The following routine displays the calendar, opening OL if needed
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then
Set olApp = CreateObject("Outlook.application")
End If
On Error GoTo 0
Set olNs = olApp.GetNamespace("MAPI")
If olApp.ActiveExplorer Is Nothing Then
olApp.Explorers.Add _
(olNs.GetDefaultFolder(9), 0).Activate
Else
Set olApp.ActiveExplorer.CurrentFolder = _
olNs.GetDefaultFolder(9)
olApp.ActiveExplorer.Display
End If
' Set olApp = New Outlook.Application
Set olApt = olApp.CreateItem(olAppointmentItem)
' Gather the values to use in the appointment
usedate = Range("date_default").Value
usesubject = Range("subject").Value
With olApt
.Start = usedate + TimeValue("9:00:00")
.End = usedate + TimeValue("11:00:00")
.Subject = usesubject
.Location = usesubject & " location"
.Body = "enter the text of your appointment here"
.BusyStatus = olOutOfOffice
.ReminderMinutesBeforeStart = 30
.ReminderSet = True
.Save
End With
olApt.Display
Set olApt = Nothing
Set olApp = Nothing
Set olNs = Nothing
End Sub
RE: Excel dates to Outlook
Thank you much! I will check it out.
Chris
Systems Analyst, I.S.
SolidWorks 06 4.1/PDMWorks 06
AutoCAD 06
ctopher's home (updated 06-21-06)