运行时错误"91"引用范围以生成 Outlook 电子邮件

Question

我想生成四封Outlook电子邮件。

我得到这个错误:

运行时错误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

Answer 1

声明你的范围为数组而不是多个变量，这样你就可以遍历数组:

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

注意，当你觉得你需要给变量编号时，你可以肯定你做错了。始终使用数组。

---

您甚至可以将其改进为最小值:

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

请注意，如果您生成一个包含Array()的数组，它将从0开始计数，而不是从1开始计数!

Answer 2

请尝试下一个改编方式:

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