"Run-time error '13': Type Mismatch." when there is no email address

Question

I am having trouble solving an error that occurs when sending an email through excel. At the moment it is set so that when a drop down list is titled "Open" in column "N" it send an email to a specific person who's email address appears in column "M" (I select the name for emailing in "J" and it creates the address in "M"). I think the problem could be that it checks every row that I have "Open" in "N" and so when there is no email address it throws up the "Run-time error '13': Type Mismatch." I currently have the following code:

In Sheet 1 (Issue Sheet):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("N3")) Is Nothing Then
Select Case Range("N3")
    Case "Open": Macro1
End Select
End If
End Sub

and my Module being:

Option Explicit

Sub Macro1() Dim OutApp As Object Dim OutMail As Object Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("M").Cells
    If cell.Value Like "?*@xyz.com" And _
       LCase(Cells(cell.Row, 14).Value) = "open" Then

        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .To = cell.Value
            .Subject = "Open Issue"
            .Body = "Dear " & Cells(cell.Row, "J").Value _
                    & vbNewLine & _
                    "Issue raised: " & Cells(cell.Row, "C").Value _
                    & vbNewLine & _
                    "Regards"
            'You can add files also like this
            '.Attachments.Add ("C:\test.txt")
            .Display  'Or use Display
        End With
        On Error GoTo 0
        Set OutMail = Nothing
    End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

Essentially what I want it to do is be able to send an email only to the address, in the row that the "Open" drop down is selected from i.e "Open" chosen in column "N" and then will send email only to the address in "M" of that row. I need this to be expandable too so I can go down several rows and only send email from that one.

If cell.Value Like "?*@xyz.com" And _ LCase(Cells(cell.Row, "N").Value) = "open" Then — md393

neuralgroove neuralgroove · Accepted Answer · 2015-11-04T17:06:16

I am addressing only the error in the title, I haven't looked at the rest of the code or whether it does what you want. Your problem is in this line

LCase(Cells(cell.Row, "N").Value) = "open" Then

Cells() is expecting a row number and a column number but you are using a string ("N") as the column number, thats a type mismatch, if you want column "N" then use the number 14 like this

LCase(Cells(cell.Row, 14).Value) = "open" Then

"Run-time error '13': Type Mismatch." when there is no email address

1 Answers