VBA and sending emails

[superneilk]

Hi, I have inherited a nice excel sheet that captures any issues identified on our production site and assigns an owner who need to action the issue. The code is supposed to email the owner everytime the sheet is closed down. However this email function isnt working. i have absolutely no idea on VBA (as yet) and am wondering is someone can help me. Attached the bit of the code that looks up the name and email address and sends the email out.

If Cells(entry_count, 16) = "Yes" Then
If Cells(entry_count, 17) = "" Then
On Error GoTo jump
days_allowed = 0
days_taken = 0

'Worksheets("Output").Cells(entry_count, 17) = Worksheets("Input").Cells(3, 7)

' file ref
export(1) = Worksheets("Output").Cells(entry_count, 1)
' raised by and briefed by
export(2) = Worksheets("Output").Cells(entry_count, 9)
'date raised
export(3) = Worksheets("Output").Cells(entry_count, 2)
' Assigned to
export(10) = Worksheets("Output").Cells(entry_count, 10)
' closure date
export(11) = Worksheets("Output").Cells(entry_count, 13)

If Worksheets("Output").Cells(entry_count, 5) = "Critical" Then days_allowed = Worksheets("Input").Cells(1, 6)
If Worksheets("Output").Cells(entry_count, 5) = "Major" Then days_allowed = Worksheets("Input").Cells(1, 7)
If Worksheets("Output").Cells(entry_count, 5) = "Minor" Then days_allowed = Worksheets("Input").Cells(1, 8)


' Have modified, not taking date anymore??? if works then days_taken2 can be removed and use this
days_taken = DateValue(Worksheets("Input").Cells(3, 7)) - DateValue(Worksheets("Output").Cells(entry_count, 12))
If days_taken = 0 Then days_taken = 1
overdue = "Thank you, the designated closure deadline for this incident was achieved."
If days_taken > days_allowed Then overdue = "Regrettably the required closure response timescale was not met"

'date_taken2 takes the date value to compensate for strng use in the TIT form to capture true days taken as an integer
days_taken2 = DateValue(Worksheets("Input").Cells(3, 7)) - DateValue(Worksheets("Output").Cells(entry_count, 12))


Worksheets("Output").Cells(entry_count, 12) = Worksheets("Input").Cells(12, 3)

'pass admin and owner variables
admin = Worksheets("Output").Cells(entry_count, 9)
owner = Worksheets("Output").Cells(entry_count, 10)

'grab mail
Workbooks(tracker).Activate
Worksheets("Input").Activate

admin_mail = Application.WorksheetFunction.Lookup(admin, Range("w8:w19"), Range("x8:x19"))
admin_first = Application.WorksheetFunction.Lookup(admin, Range("w8:w19"), Range("y8:y19"))

For count1 = 26 To 31
If Worksheets("Input").Cells(count1, 23) <> "" Then admin_mail = admin_mail & "; " & Worksheets("Input").Cells(count1, 23)
Next count1

owner_mail = Application.WorksheetFunction.Lookup(owner, Range("Z8:Z28"), Range("AA8:AA28"))
owner_first = Application.WorksheetFunction.Lookup(owner, Range("Z8:Z28"), Range("AB8:AB28"))

' message string construct

title = "Thanks! Technical Incident: " & export(1) & " has been closed."

msg = "Dear " & owner_first & "," & Chr(10)
msg = msg & Worksheets("Output").Cells(entry_count, 5) & " Technical Incident (Ref) " & export(1) & " has been closed." & Chr(10)
msg = msg & overdue & Chr(10) & Chr(10)
msg = msg & "Performance Stats:" & Chr(10)
msg = msg & "Closure was due by: " & export(11) & ". Closure was recorded on: " & Worksheets("Input").Cells(3, 7) & Chr(10)
msg = msg & days_allowed & " days were allowed with " & days_taken & " being used to investigate and close."


' mail construct

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = owner_mail
.CC = admin_mail
'.BCC = ""
.Subject = title
.body = msg
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Application.DisplayAlerts = False

Application.DisplayAlerts = True

Worksheets("Output").Cells(entry_count, 16) = "Yes"
Worksheets("Output").Cells(entry_count, 17) = Worksheets("Input").Cells(3, 7)
Worksheets("Output").Cells(entry_count, 18) = Application.UserName
Worksheets("Output").Cells(entry_count, 21) = days_taken2



End If
End If