×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-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

*Tek-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

Import Outlook calendar entries from Excel

Import Outlook calendar entries from Excel

Import Outlook calendar entries from Excel

(OP)
Hi,

I am pretty new to VBscripts. I am trying to use a VBscript to copy calendar entries from an excel spreadsheet into an Outlook Calendar. I have found on forums this script below:

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Open("C:\VBATest\Dates.xlsx")
objExcel.Application.Visible = False
'objExcel.ActiveWorkbook.Save'
Set objOutlook = CreateObject("Outlook.Application")
Set objNameSpace = objOutlook.GetNameSpace("MAPI")
Set objFolder = objNameSpace.GetDefaultFolder(olFolderCalendar)
Dim i
Dim j
For i = 1 To 3
Set j = 1
While j > 0
strFilter = "[Start] >= 'objWorkbook.Worksheets(i).Cells(4,2)' AND [Start] <= 'objWorkbook.Worksheets(i).Cells(4,3)' AND [Subject = 'objWorkbook.Worsheets(i).Cells(6,j)'"
Set foundItems = ojbFolder.Items.Restrict(strFilter)
If foundItems.count = 1 Then foundItems.Item.Delete
Set objAppt = objFolder.Items.Add
With objAppt
.Subject = "objWorkbook.Worsheets(i).Cells(6,j)"
.Body = "objWorkbook.Worsheets(i).Cells(6,j)"
.Start = "objWorkbook.Worsheets(i).Cells(7,j)"
.AllDayEvent = True
.ReminderMinutesBeforeStart = 1440
.Save
End With
Set j = j + 1
If objWorkbook.Worksheets(i).Cells(6,j) = "stop" Then Set j = 0
Wend
Next
objWorkbook.Close False
Set objExcel = Nothing
Set objWorkbook = Nothing
Set objOutlook = Nothing
Set objNameSpace = Nothing
Set objFolder = Nothing
Set objAppt = Nothing

but I get the below error:

Invalid procedure call or argument: ‘objNameSpace.GetDefaultFolder’
Code: 800A00005
Source: MSVBScript runtime error

Any ideas how I can resolve this?

RE: Import Outlook calendar entries from Excel

Probably because olFolderCalendar is not defined. Add this to the top:
Const olFolderCalendar = 9

RE: Import Outlook calendar entries from Excel

...and you have some other anomalies:

Do not Set non-object variables like j
ojbFolder is misspelled

CODE

'
    Dim objExcel, objWorkbook
    Dim objOutlook, objNameSpace, objFolder, foundItems, objAppt
    Dim i, j, strFilter
    
    Const olFolderCalendar = 9
    
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Application.DisplayAlerts = False
    Set objWorkbook = objExcel.Workbooks.Open("C:\VBATest\Dates.xlsx")
    objExcel.Application.Visible = False
    'objExcel.ActiveWorkbook.Save'
    Set objOutlook = CreateObject("Outlook.Application")
    Set objNameSpace = objOutlook.GetNameSpace("MAPI")
    Set objFolder = objNameSpace.GetDefaultFolder(olFolderCalendar)
    
    For i = 1 To 3
    '    Set j = 1   '
        j = 1
        While j > 0
            strFilter = "[Start] >= 'objWorkbook.Worksheets(i).Cells(4,2)' AND [Start] <= 'objWorkbook.Worksheets(i).Cells(4,3)' AND [Subject = 'objWorkbook.Worsheets(i).Cells(6,j)'"
        '    Set foundItems = ojbFolder.Items.Restrict(strFilter)
            Set foundItems = objFolder.Items.Restrict(strFilter)
            If foundItems.Count = 1 Then foundItems.Item.Delete
            Set objAppt = objFolder.Items.Add
            With objAppt
                .Subject = "objWorkbook.Worsheets(i).Cells(6,j)"
                .Body = "objWorkbook.Worsheets(i).Cells(6,j)"
                .Start = "objWorkbook.Worsheets(i).Cells(7,j)"
                .AllDayEvent = True
                .ReminderMinutesBeforeStart = 1440
                .Save
            End With
            Set j = j + 1
        '    If objWorkbook.Worksheets(i).Cells(6, j) = "stop" Then Set j = 0
            If objWorkbook.Worksheets(i).Cells(6, j) = "stop" Then j = 0
        Wend
    Next
    objWorkbook.Close False
    Set objExcel = Nothing
    Set objWorkbook = Nothing
    Set objOutlook = Nothing
    Set objNameSpace = Nothing
    Set objFolder = Nothing
    Set objAppt = Nothing 




Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Import Outlook calendar entries from Excel

(OP)
Hi,

Thanks guys.

SkipVought, I have made the changes you suggested. However, I get a different error now:

Error on line 20: Cannot parse condition. Error at "[Subject = 'objWorkbook.Worksheets(i).Ce...".
Code 80020009.

Any ideas?

RE: Import Outlook calendar entries from Excel

You probably need to use the contents of worksheet, so in Subject, Body and Start setting remove quotation marks (.Subject = objWorkbook.Worsheets(i).Cells(6,j)).
Test if strFilter returns what you intend to have in your code its always:
[Start] >= 'objWorkbook.Worksheets(i).Cells(4,2)' AND [Start] <= 'objWorkbook.Worksheets(i).Cells(4,3)' AND [Subject = 'objWorkbook.Worsheets(i).Cells(6,j)

combo

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 Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

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

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

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close