Blackghost
07-17-2012, 01:54 AM
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
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