VBA Email notification not sending out when its a different case

Windowtothewall

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Re read post #7 regarding posting the workbook
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Windowtothewall

New Member
Joined
Sep 7, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Fixed.

Another colleague spotted
VBA Code:
.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.
 
Solution

Forum statistics

Threads
1,148,170
Messages
5,745,170
Members
423,930
Latest member
Simple77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top