1

Can anyone help please? I have VBA to select a range on a spreadsheet which sends an attachment by e-mail. If I click on cancel when selecting the range I get a run time 424 object required. Is there code that I can add to override this with a msg box. Thanks.

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
Gwen Taylor
  • 21
  • 2

1 Answers1

0

Cancel Application.InputBox When Range

Instead of

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

try

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"
VBasic2008
  • 44,888
  • 5
  • 17
  • 28