Excel VBA runtime error '91' when trying to generate multiple outlook emails-CodePudding

I am writing a sub to generate four outlook emails, I got a runtime error 91 at this line: rng = "rng" & i, can anyone help me understand what is wrong? thanks in advance.

Sub generate4emails()

Dim OutApp As Object, OutMail As Object
Dim i As Integer
Dim rng As Range, rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range

Set rng1 = ThisWorkbook.Sheets("Sheet1").Range("C12:F14")
Set rng2 = ThisWorkbook.Sheets("Sheet1").Range("C16:F18")
Set rng3 = ThisWorkbook.Sheets("Sheet1").Range("H12:K14")
Set rng4 = ThisWorkbook.Sheets("Sheet1").Range("H16:K18")

For i = 1 To 4
    Set Outappp = CreateObject("Outlook.application")
    Set OutMail = OutApp.Createitem(0)
    rng = "rng" & i
    
    With OutMail
        .To = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
        .Subject = "Notice" & i
        .HTMLBody = RangetoHTML(rng)
        .display
    End With
    Set OutMail = Nothing
Next i   
End Sub

CodePudding user response：

Declare your ranges as array instead of multiple variables so you can loop through that array:

Sub generate4emails()
    Dim OutApp As Object, OutMail As Object
    Dim i As Long
    Dim rng(1 To 4) As Range
    
    Set rng(1) = ThisWorkbook.Sheets("Sheet1").Range("C12:F14")
    Set rng(2) = ThisWorkbook.Sheets("Sheet1").Range("C16:F18")
    Set rng(3) = ThisWorkbook.Sheets("Sheet1").Range("H12:K14")
    Set rng(4) = ThisWorkbook.Sheets("Sheet1").Range("H16:K18")
    
    For i = 1 To 4
        Set Outappp = CreateObject("Outlook.application")
        Set OutMail = OutApp.Createitem(0)
        
        With OutMail
            .To = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
            .Subject = "Notice" & i
            .HTMLBody = RangetoHTML(rng(i))
            .display
        End With
        Set OutMail = Nothing
    Next i   
End Sub

Note, when ever you have the feeling you need to number your variables you can be sure you are doing it wrong. Always use arrays instead.

You can even improve this to a minimum:

Sub generate4emails()
    Dim OutApp As Object, OutMail As Object
    Dim i As Long

    Dim RngAddresses As Variant
    RngAddresses = Array("C12:F14", "C16:F18", "H12:K14", "H16:K18")
    
    For i = LBound(RngAddresses) To UBound(RngAddresses)
        Set Outappp = CreateObject("Outlook.application")
        Set OutMail = OutApp.Createitem(0)
        
        With OutMail
            .To = ThisWorkbook.Sheets("Sheet1").Range("A1").Value
            .Subject = "Notice" & i   1
            .HTMLBody = RangetoHTML(ThisWorkbook.Sheets("Sheet1").Range(RngAddresses(i))
            .display
        End With
        Set OutMail = Nothing
    Next i   
End Sub

Note that if you generate an array with Array() it starts counting with 0 not with 1!

CodePudding user response：

Please, try the next adapted way:

Sub generate4emails()

Dim OutApp As Object, OutMail As Object
Dim i As Integer
Dim rng As Range, rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, arrRng

Set rng1 = ThisWorkbook.Sheets("Sheet1").Range("C12:F14")
Set rng2 = ThisWorkbook.Sheets("Sheet1").Range("C16:F18")
Set rng3 = ThisWorkbook.Sheets("Sheet1").Range("H12:K14")
Set rng4 = ThisWorkbook.Sheets("Sheet1").Range("H16:K18")
arrRng = Array(rng1, rng2, rng3, rng4)

For i = 0 To UBound(arrRng)
    Set Outappp = CreateObject("Outlook.application")
    Set OutMail = OutApp.CreateItem(0)
    Set rng = arrRng(i)
    
    With OutMail
        .To = ThisWorkbook.Sheets("Sheet1").Range("A1").value
        .Subject = "Notice" & i
        .HtmlBody = RangetoHTML(rng)
        .Display
    End With
    Set OutMail = Nothing
Next i
End Sub