×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel dates to Outlook

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.

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

Check out the info at:
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

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

RE: Excel dates to Outlook

(OP)
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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources