CODEWITHSUNDEEP
vbaexcelruntime-errortype-mismatch
James
James

Reputation: 1

Run-time error 13 on excel 2010 but works on excel 2016

Private Sub CommandButton64_Click() 
Dim cell As Range
Dim strto As String
For Each cell In ThisWorkbook.Sheets("Sheet2").Range("C3:L197")
If cell.Value Like "?*@?*.?*" Then
strto = strto & cell.Value & ";"
End If
Next cell
If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

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

On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.BCC = strto
.Subject = "Enter subject here"
.Body = "" ' EMPTY FOR NOW
'USE THIS FOR ENTERING NAMES OF RECIPIENTS IN BODY TEXT "here"
'"Dear" & Cells(cell.Row, "A").Value _
& vbNewLine & vbNewLine & _
"Enter body text " & _
"here"
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
'.Send 'Or use Display
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

The code is intended to take emails that are in cells as a formula, and output them as addresses in the bcc box when the command button is clicked.

This function works on Excel 2016 when i click the button, but doesn't work when I forward the file to colleagues using Excel 2010, instead I receive the error

Run-time error '13': type mismatch

Highlighting the text line 'If cell.Value Like "?@?.?*" Then'

Can anyone assist me with this?

Thanks

Upvotes: 0

Views: 125

Answers (1)

ProfoundlyOblivious
ProfoundlyOblivious

Reputation: 1485

I just worked through a similar problem with that error. It is very likely there's a worksheet calculation error on the 2010 version.

If you can't clear the error off the sheet a recomendation from @Vityata is to use

Not IsError

So for your code, make the loop more like:

For Each cell In ThisWorkbook.Sheets("Sheet2").Range("C3:L197")
    If Not IsError(cell) Then
        If cell.Value Like "?*@?*.?*" Then strto = strto & cell.Value & ";"
    End If
Next cell

Upvotes: 1

Related Questions