我有以下代码(由Ron de Bruin提供),并试图找出如何自定义它供我自己使用。
当我运行代码时,什么也没有发生(因为错误处理程序在错误"下标超出范围"时停止宏),
但是当我改变行:
ActiveWorkbook.EnvelopeVisible = False
:
ActiveWorkbook.EnvelopeVisible = True
可见信封允许我进行选择,并选择我要发送给谁,等等。
我想知道为什么它会遇到"超出范围"的错误,如果有可能让这个过程自动进行,而不需要我在事件触发后输入东西[它是从Workbook_Open()事件中触发的-如果这有任何区别,并且选择是在同一工作簿中的另一个工作表(工作表("ValLog"))中进行的]
我运行的代码是:
Private Sub workbook_open()
Dim AWorksheet As Worksheet
Dim Sendrng, rng As Range
Dim answer As Integer
On Error GoTo StopMacro
answer = MsgBox("Do you want to send e-mail notifications of upcoming tours?", vbYesNo)
If answer = vbYes Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sendrng = Worksheets("ValLog").Range("B5:K12").Select
With Sendrng
'Select the range you want to mail
Range("B5:K12").Select
' Create the mail and send it
ActiveWorkbook.EnvelopeVisible = True
With .Parent.MailEnvelope
.Introduction = "Test Test Test"
With .Item
.To = "myemail@blahblah.com"
.CC = ""
.BCC = ""
.Subject = "Why, Error?"
.Send
End With
End With
StopMacro:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
ActiveWorkbook.EnvelopeVisible = False
End With
Else
'Do Nothing
End If
End Sub
首先,您没有将SendRange声明为一个范围。线:
Dim SendRange, rng As Range
声明SendRange为一个变体,而rng为一个范围。将其更改为:
Dim SendRange As Range, rng As Range
第二,修改行:
Set Sendrng = Worksheets("ValLog").Range("B5:K12").Select
:
Set Sendrng = Worksheets("ValLog").Range("B5:K12")
您正在尝试在这里设置范围,而不是选择它。
然后,改变:
Range("B5:K12").Select
:
.Select
你在代码中实际做的是选择活动表中的范围(因为你没有在范围之前定义工作表)。通过将其放入"With"语句中,每个在其前面带有"。"的语句将从With语句中继承术语。因此,"。选择"您正在实际运行"发送。选择"