Sending Email in Excel

[Blackghost]

HI All

Im New, Need so much needed help with VB Code.

Just a introduction to my work sheet

Now I have a spreed sheet that will calculate the days it takes to finish a project, so in Column B is how many days i have to complete the project, in C is day i start the project and in Column O is the day I end the project.
So in column S is were the formula works out how many days it took me to complete the project, and Column U is the formula that works out how many days overdue the project is.

Now once I update the date complete in Column O it must work out if my project is overdue, if it is it needs to FIRE off the email to the project leaders. Im using office 2010 and 2007, now my code is giving me a Compile error User-Defined type not defined, I have checked all the library references but still not working?

please see code below

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 15 Then Exit Sub 'only fire when you change col O

If CLng(Target.Offset(0, 6).Value) > 0 Then
Dim strBody As String
strBody = "Commercial FNB " & "'" & Target.Offset(0, -14).Value & "'" & " took " & Target.Offset(0, 6) _
& " extra days to complete"
Dim newApp As Outlook.Application
Set newApp = New Outlook.Application
Dim objMail As Outlook.MailItem

Set objMail = newApp.CreateItem(olMailItem)
objMail.To = "helen@doc.co.za ; leone@doc.co.za " 'change to suit
objMail.CC = "osziej@doc.co.za"
objMail.Subject = "Late batch"
objMail.Body = strBody
objMail.Send 'change to .Send if you want it to auto send without reviewing first

End If

End Sub

[Kenneth Hobs]

Welcome to the forum! Please use VBA code tags when posting code.

You have not set the reference to Microsoft Outlook 14.0 Object Library.

[Blackghost]

Welcome to the forum! Please use VBA code tags when posting code.

You have not set the reference to Microsoft Outlook 14.0 Object Library.

HI Kenneth

Will remember to do that in the future.

Regards to the Microsoft Outlook 14.0 Object Library not being set, it is set, you can see from the screen shots I took?

[snb]

To avoid referencing problems:

Private Sub Worksheet_Change(ByVal Target As Range)
if target.count>1 then exit sub
If Target.Column <> 15 Then Exit Sub

If Target.Offset(, 6).Value > 0 Then
with createobject("outlook.Application").createitem(1)
.To = "helen@doc.co.za ; leone@doc.co.za "
.CC = "osziej@doc.co.za"
.Subject = "Late batch"
.Body = "Commercial FNB " & "'" & Target.Offset(0, -14).Value & "'" & " took " & Target.Offset(, 6).value & " extra days to complete"
.Send
end with
End If
end sub

[Kenneth Hobs]

No, you have Office and Excel set but not Outlook.

[snb]

I made a typo


Private Sub Worksheet_Change(ByVal Target As Range)
If target.count>1 Then exit Sub
If Target.Column <> 15 Then Exit Sub

If Target.Offset(, 6).Value > 0 Then
With createobject("outlook.Application").createitem(0)
.To = "helen@doc.co.za ; leone@doc.co.za "
.CC = "osziej@doc.co.za"
.Subject = "Late batch"
.Body = "Commercial FNB " & "'" & Target.Offset(0, -14).Value & "'" & " took " & Target.Offset(, 6).value & " extra days to complete"
.Send
End With
End If
End Sub