VBA For Each in Array type mismatch

Question

I have been using the following code to send out emails, but I get a "run time error 13" when there is only one value in column M.

It works fine if I have more than two values. Any help please?

Sub testDemo()
    Dim outlookApp As Object
    Dim objMail As Object
    Dim Region
    Dim rng As Range
    Dim Mailaddr As String
    Dim MyRange As String
    Dim arr As Variant
    Dim lastrow As Long
    Dim lastrow2 As Long


     ' Create email
    Set outlookApp = CreateObject("Outlook.Application")

    ' Update with your sheet reference
    With Sheets("Escalate")

    lastrow = Range("A65536").End(xlUp).Row
    lastrow2 = Range("M65536").End(xlUp).Row
    Set rng = .Range("A1:I" & lastrow)

    End With

    arr = Range("M2:M" & lastrow2).Value


    For Each Region In arr

    myrangename = Worksheets("email").Range("C2:D200")
    Mailaddr = WorksheetFunction.VLookup(Region, myrangename, 2, False)

    On Error Resume Next


    With outlookApp.CreateItem(0)
            ' Add table to Email body
            .SentOnBehalfOfName = "script Tracking"
            .cc = "[email protected]; [email protected]"
            .HTMLBody = "Dear Team," & "<br><br>" & _
            "blahblah  " & "<br><br>" & _
            GenerateHTMLTable(rng, CStr(Region), True) & "<br><br>" & _
            "Many thanks in advance " & "<br><br>" & _
            "Kind regards "

            .To = Mailaddr
            .Subject = "Region " & Region & " Outstanding scripts -  " & Range("L1")
            .Display

        End With
skip:

    Next Region


End Sub

Public Function GenerateHTMLTable(srcData As Range, Region As String, Optional FirstRowAsHeaders As Boolean = True) As String
    Dim InputData As Variant, HeaderData As Variant
    Dim HTMLTable As String
    Dim i As Long



    ' Declare constants of table element
    Const HTMLTableHeader As String = "<table>"
    Const HTMLTableFooter As String = "</table>"

    ' Update with your sheet reference
    If FirstRowAsHeaders = True Then
        HeaderData = Application.Transpose(Application.Transpose(srcData.Rows(1).Value2))
        InputData = Range(srcData.Rows(2), srcData.Rows(srcData.Rows.Count)).Value2
        ' Add Headers to table
        HTMLTable = "<tr><th>" & Join(HeaderData, "</th><th>") & "</th></tr>"


    End If

    ' Loop through each row of data and add selected region to table output
        For i = LBound(InputData, 1) To UBound(InputData, 1)
        ' Test Region against chosen option
        If Region = InputData(i, 9) Then
            ' Add row to table for output in email
            HTMLTable = HTMLTable & "<tr><td>" & Join(Application.Index(InputData, i, 0), "</td><td>") & "</td></tr>"


        End If


Next i


    GenerateHTMLTable = HTMLTableHeader & HTMLTable & HTMLTableFooter

End Function

enter image description here

Siddharth Rout · Accepted Answer · 2019-03-13 11:36:21Z

1

This will explain it better

Sub Sample()
    Dim arr

    lastrow2 = 2

    arr = Range("M2:M" & lastrow2).Value

    lastrow2 = 3

    arr = Range("M2:M" & lastrow2).Value
End Sub

When the lastrow2 = 2, arr holds only one cell value and hence it becomes a Variant/(String/Double...etc depending on the value in cell M2)

When the lastrow2 > 2, arr becomes a 2D array and hence it becomes a Variant/Variant(1 to 2, 1 to 1)

The above can be verified using a Watch on arr in VBA.

This is the reason why your code works when you have more than one cell.

answered Mar 13, 2019 at 11:36

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chronocidal · Accepted Answer · 2019-03-13 11:50:35Z

0

Because it is not a collection or an array, it is a single value - you can test this by checking IsArray(arr) before you run the For Each

There are several ways to fix this, but the fastest would be to include the line If Not IsArray(Arr) Then Arr = Array(Arr) before your For Each, to turn it into a 1-element array.

Other points to consider:

What is the purpose of your On Error Resume Next?
What is the purpose of your skip: label?
Variable myrangename is not defined - consider adding Option Explicit to the top of your Module, so that "Debug > Compile VBA Project" will catch those errors for you

answered Mar 13, 2019 at 11:50

Chronocidal

8,3551 gold badge15 silver badges30 bronze badges

Collectives™ on Stack Overflow

VBA For Each in Array type mismatch

2 Answers 2

Comments

Other points to consider:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Other points to consider:

Comments

Your Answer

Sign up or log in

Post as a guest

Related