"Type mismatch"-error with a short test-version of mail macro

Hi all,

I have made a mail macro for my work, but my collegues would like to see a short testversion of the macro, to see that it gets up all the good information from my sheet and that there are not too few/to many spaces. My intention is to create a macro that opens two "new e-mail" screens in Outlook, with one Dutch and one English version of the mail. But when I run the test version, it says "type mismatch". What am I doing wrong? Below my mail macro and my test version. Thanks.

Full mail macro

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Sub SendEMail()

    Dim Email As String, Subj As String
    Dim Msg As String, URL As String
    Dim R As Integer, x As Double

    For R = 5 To 4 + Application.WorksheetFunction.CountIf(Range("E5", Cells(Rows.Count, 5).End(xlUp)), "*@*")
        '       Get the email address
        If Cells(R, 7) = Empty Then
    Email = Cells(R, 5)

    If LCase(Cells(R, 6).Value) = "nl" Then
        '       Message subject
        Subj = Cells(5, 22)

        '       Compose the message
            Msg = ""
        Msg = Msg & Cells(7, 20) & Cells(R, 3) & "," & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(8, 20) & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(9, 20) & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(11, 20) & Cells(R, 10) & "." & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(13, 20) & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(15, 20) & vbCrLf
        
        Msg = Msg & Cells(16, 20) & vbCrLf & vbCrLf
        Else
    '   Message subject
        Subj = Cells(18, 22)

        '       Compose the message
            Msg = ""
        Msg = Msg & Cells(20, 20) & Cells(R, 3) & "," & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(21, 20) & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(22, 20) & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(24, 20) & Cells(R, 10) & "." & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(26, 20) & vbCrLf & vbCrLf
        
        Msg = Msg & Cells(28, 20) & vbCrLf
        
        Msg = Msg & Cells(29, 20) & vbCrLf & vbCrLf
        End If
        '       Replace spaces with %20 (hex)
        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

        '       Replace carriage returns with %0D%0A (hex)
        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
        '       Create the URL
        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

        '       Execute the URL (start the email client)
        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

        '       Wait two seconds before sending keystrokes
        Application.Wait (Now + TimeValue("0:00:02"))
        Application.SendKeys "%s"
        End If
    Next R
End Sub

Test version

Sub TestSendEMail()
  For Each cl In Columns(5)
    If InStr(cl, "@") < 0 Then
      With CreateObject("Outlook.Application").createitem(0)
        .Subject = Cells(18, 22)
        .To = cl
        .Body = Join(WorksheetFunction.Transpose([U7:U16].Offset(IIf(cl.Offset(, 1) = "nl", 0, 13))), vbCr)
        .Display
      End With
   End If
  Next
End Sub

im not sure but i think
Dim cl As Range
is needed then again that may be gibberish

A few pointers perhaps:

For Each cl in Columns(5).Cells

and

InStr(cl,"@") > 0 Then

or

InStr(cl,"@") Then

You should also look to restrict the range you're looping to include only those cells necessary... as is you're going to iterate every cell in Column E...

Explicitly Declaring variables (as outlined by Martin) is good practice if not always required.