Runtime error '91' referencing a range to generate Outlook emails

user_10631384 0
Viewed 60

I would like to generate four Outlook emails.

I got this error:

runtime error 91

at: rng = "rng" & i.

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
vba excel
2 Answers

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!

user_32196130

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
user_22333080
Related