Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel dates to Outlook 1

Status
Not open for further replies.

ctopher

Mechanical
Jan 9, 2003
17,506
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.

Chris
Systems Analyst, I.S.
SolidWorks 06 4.1/PDMWorks 06
AutoCAD 06
ctopher's home (updated 06-21-06)
 
Replies continue below

Recommended for you

Check out the info at:

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:
Sub SetAppt()

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
 
bltseattle,
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)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor