VBA Email notification not sending out when its a different case

[Windowtothewall]

Hey Guys,

I have inherited some code from an ex colleague that he didnt get to finish before he moved on, and I cant seem to get to work and would like some assistance on please.

The error I'm getting is:

Run Time error -2147467259 (80004005)

Outlook does not recognize one or more names


The code is for a Submit Form which sends emails out to selected addresses from a reference table if YES is displayed.

Problem is, it works only when selecting Severity = Critical. But not the other two options (Major and Minor)

When debugging it highlights the text in RED below

'Create array of email addresses
Dim checkrange As Range

Select Case cbSeverity.Value
Case "Critical"
Set checkrange = Range("C2", Range("C2").End(xlDown))
For Each cell In checkrange
If cell = "Yes" Then
Set objEmail = objOutlook.CreateItem(0)
With objEmail
.to = cell.Offset(0, -1).Value
.Subject = "New Equipment Maintanence Request - " & cbSeverity.Value
.body = "New equipment maintanence request from " & entName.Value & " on " & Date & ", EMR: " & newEMREnt & vbNewLine _
& "Category: " & cbCategory.Value & ", Equipment: " & entEquipment.Value & vbNewLine _
& "Location: " & entLocation & vbNewLine _
& "Effected material code/ product code/ workorder: " & entCode.Value & vbNewLine _
& "Description: " & entDescription.Value & vbNewLine _
& "Severity: " & cbSeverity.Value & vbNewLine _
& "Priority: " & cbPriority.Value & vbNewLine _
& "Target Date: " & entTargetDate.Value
.Send
Set objEmail = Nothing
End With
End If
Next cell
Case "Major"
Set checkrange = Range("D2", Range("D2").End(xlDown))
For Each cell In checkrange
If cell = "Yes" Then
Set objEmail = objOutlook.CreateItem(0)
With objEmail
.to = cell.Offset(0, -1).Value
.Subject = "New Equipment Maintanence Request - " & cbSeverity.Value
.body = "New equipment maintanence request from " & entName.Value & " on " & Date & ", EMR: " & newEMREnt & vbNewLine _
& "Category: " & cbCategory.Value & ", Equipment: " & entEquipment.Value & vbNewLine _
& "Location: " & entLocation & vbNewLine _
& "Effected material code/ product code/ workorder: " & entCode.Value & vbNewLine _
& "Description: " & entDescription.Value & vbNewLine _
& "Severity: " & cbSeverity.Value & vbNewLine _
& "Priority: " & cbPriority.Value & vbNewLine _
& "Target Date: " & entTargetDate.Value
.Send
Set objEmail = Nothing
End With
End If
Next cell
Case "Minor"
Set checkrange = Range("E2", Range("E2").End(xlDown))
For Each cell In checkrange
If cell = "Yes" Then
Set objEmail = objOutlook.CreateItem(0)
With objEmail
.to = cell.Offset(0, -1).Value
.Subject = "New Equipment Maintanence Request - " & cbSeverity.Value
.body = "New equipment maintanence request from " & entName.Value & " on " & Date & ", EMR: " & newEMREnt & vbNewLine _
& "Category: " & cbCategory.Value & ", Equipment: " & entEquipment.Value & vbNewLine _
& "Location: " & entLocation & vbNewLine _
& "Effected material code/ product code/ workorder: " & entCode.Value & vbNewLine _
& "Description: " & entDescription.Value & vbNewLine _
& "Severity: " & cbSeverity.Value & vbNewLine _
& "Priority: " & cbPriority.Value & vbNewLine _
& "Target Date: " & entTargetDate.Value
.Send
Set objEmail = Nothing
End With
End If
Next cell
End Select

 

[Michael M]

Re read post #7 regarding posting the workbook

 

[Windowtothewall]

Fixed.

Another colleague spotted

.to = cell.Offset(0, -1).

Value needed to be (0,-2) Major and (0,-3) for Minor.

Simple mistake. Solved.

Thank you for your input and time.