时间戳

首页 > 解决方案 > 在 Excel 中选择要通过电子邮件发送的范围时需要 VBA 运行时错误 424 对象

excel - 在 Excel 中选择要通过电子邮件发送的范围时需要 VBA 运行时错误 424 对象

问题描述

有人可以帮忙吗？我有 VBA 在通过电子邮件发送附件的电子表格上选择一个范围。如果我在选择范围时单击取消，我会得到所需的运行时 424 对象。有没有我可以添加的代码来用一个消息框覆盖它。谢谢。

Sub SendRange()
Dim xFile As String
Dim xFormat As Long
Dim Wb As Workbook
Dim Wb2 As Workbook
Dim Ws As Worksheet
Dim FilePath As String
Dim FileName As String
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim WorkRng As Range
xTitleId = "Example"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set Wb = Application.ActiveWorkbook
Wb.Worksheets.Add
Set Ws = Application.ActiveSheet
WorkRng.Copy Ws.Cells(1, 1)
Ws.Copy
Set Wb2 = Application.ActiveWorkbook
Select Case Wb.FileFormat
Case xlOpenXMLWorkbook:
    xFile = ".xlsx"
    xFormat = xlOpenXMLWorkbook
Case xlOpenXMLWorkbookMacroEnabled:
    If Wb2.HasVBProject Then
        xFile = ".xlsm"
        xFormat = xlOpenXMLWorkbookMacroEnabled
    Else
        xFile = ".xlsx"
        xFormat = xlOpenXMLWorkbook
    End If
Case Excel8:
    xFile = ".xls"
    xFormat = Excel8
Case xlExcel12:
    xFile = ".xlsb"
    xFormat = xlExcel12
End Select
FilePath = Environ$("temp") & "\"
FileName = Wb.Name & Format(Now, "dd-mmm-yy h-mm-ss")
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
Wb2.SaveAs FilePath & FileName & xFile, FileFormat:=xFormat
With OutlookMail
    .To = "someone@gmail.com"
    .CC = ""
    .BCC = ""
    .Subject = "Test"
    .Body = "Hi there."
    .Attachments.Add Wb2.FullName
    .Send
End With
Wb2.Close
Kill FilePath & FileName & xFile
Set OutlookMail = Nothing
Set OutlookApp = Nothing
Ws.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
enter code here

标签： excelvba

解决方案

取消Application.InputBox时范围

代替

Dim WorkRng As Range
xTitleId = "Example"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

尝试

xTitleId = "Example"

Dim drg As Range ' Default Range
If TypeName(Selection) = "Range" Then
    Set drg = Application.Selection
Else
    Set drg = Range("A1")
End If

On Error Resume Next
Dim WorkRng As Range
Set WorkRng = Application.InputBox("Range", xTitleId, drg.Address, Type:=8)
On Error GoTo 0

If WorkRng Is Nothing Then
    MsgBox "You canceled.", vbExclamation, "Canceled By User"
    Exit Sub
End If

'MsgBox "Work Range Address: " & WorkRng.Address, vbInformation, "Address"

推荐阅读