Windowtothewall
New Member
- Joined
- Sep 7, 2021
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
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
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