VBA Code Problems with Sending Excel DB through Outlook

[Augustine]

I am having challenges with Line 3 and Line 24 of the following VBA macro depending on the version 2007/2010 that I am using:

Sub SubmitAndLogWorkOrder()
Dim c As Range
Dim olApp As Outlook.Application
Set olApp = New Outlook.Application
Dim Msg As Outlook.MailItem
Set Msg = olApp.CreateItem(Outlook.olMailItem)

'Switch to Work Order worksheet and save so this is the first page looked at when the document is opened by someone
Sheets("Work Order").Select
With ThisWorkbook
.SaveAs "S:\Maintenance Work Orders\Maintenance Work Order - " & Format(Date, "yyyy-mm-dd") & ".xls", FileFormat:=56
End With
'Switch back to the Distribution sheet for reading/sending emails
Sheets("Distribution").Select
'Send message to recipient email list
With Msg
For Each c In Range("B6", Range("B6").End(xlUp))
.Recipients.Add c.Value
Next c
.Subject = "West Campus Maintenance Work Order"
.Body = "Please note that the attached maintenance work order has been generated at " & Now() & " and submitted to _
West Campus maintenance personnel. This work should be completed within 24 hours. Please notify Jasmine Allen (978-8310, _
jasmine.allen@wichita.edu) if there will be expected delays in completion or if there are any questions relating to the attached _
work order. Thank you for ensuring timely completion of this task. -- West Campus Staff"
.Attachments.Add ("S:\Maintenance Work Orders\Maintenance Work Order - " & Format(Date, "yyyy-mm-dd") & ".xls")
.Send
End With
'Switch back to Work Order worksheet so that the content sent is removed for the next run of the macro

Sheets("Work Order").Select

Range("A3:D14").Select
Selection.ClearContents

End Sub
When Excel 2007 is run, Line 3 breaks the code:
Dim olApp As Outlook.Application
Compile error: Can't find project or library

However, when Excel 2010 is run, Line 24 breaks the code:
.Recipients.Add c.Value
Run-time error '287': Application-defined or object-defined error

Ideally, I would like this code to be able to successfully run on either 2007 or 2010 Office platforms. Any help is greatly appreciated!

Augustine

[geekgirlau]

Use late binding:


Dim olApp As Object
Dim Msg As Object

[Augustine]

Thank you for your assistance! I'm able to get, well, a line further in 2007 to line 4 where the code breaks with the following error:
Compile error: Can't find project or library
I do have the Mircrosoft Office 12.0 reference checked.

On 2010 on my PC I'm still hitting the error on line 24:
.Recipients.Add c.Value
Run-time error '287': Application-defined or object-defined error

Sub SubmitAndLogWorkOrder()
Dim c As Range
Dim olApp As Object
Set olApp = New Outlook.Application
Dim Msg As Oobject
Set Msg = olApp.CreateItem(Outlook.olMailItem)

'Switch to Work Order worksheet and save so this is the first page looked at when the document is opened by someone
Sheets("Work Order").Select
With ThisWorkbook
.SaveAs "S:\Maintenance Work Orders\Maintenance Work Order - " & Format(Date, "yyyy-mm-dd") & ".xls", FileFormat:=56
End With
'Switch back to the Distribution sheet for reading/sending emails
Sheets("Distribution").Select
'Send message to recipient email list
With Msg
For Each c In Range("B6", Range("B6").End(xlUp))
.Recipients.Add c.Value
Next c
.Subject = "West Campus Maintenance Work Order"
.Body = "Please note that the attached maintenance work order has been generated at " & Now() & " and submitted to _
West Campus maintenance personnel. This work should be completed within 24 hours. Please notify Jasmine Allen (978-8310, _
jasmine.allen@wichita.edu) if there will be expected delays in completion or if there are any questions relating to the attached _
work order. Thank you for ensuring timely completion of this task. -- West Campus Staff"
.Attachments.Add ("S:\Maintenance Work Orders\Maintenance Work Order - " & Format(Date, "yyyy-mm-dd") & ".xls")
.Send
End With
'Switch back to Work Order worksheet so that the content sent is removed for the next run of the macro

Sheets("Work Order").Select

Range("A3:14").Select
Selection.ClearContents

End Sub

Any other thoughts? Thank in advance for helping out this n00b :)

Augustine