XLSM Workbook error: Run-time error '91': Object variable or With block variable not set.

[codyssey]

In an XLSM doc I am getting an error on the line below. It happens when the user tries to save the workbook as the existing workbook name. Thanks in advance

Run-time error '91': Object variable or With block variable not set

ExternalLinks = DestinWB.LinkSources(Type:=xlLinkTypeExcelLinks)

Here is the rest of the code:

Sub Email()
'PURPOSE: Create email message with ActiveWorkbook attached
'SOURCE: www.TheSpreadsheetGuru.com

Dim SourceWB As Workbook
Dim DestinWB As Workbook
Dim OutlookApp As Object
Dim OutlookMessage As Object
Dim TempFileName As Variant
Dim ExternalLinks As Variant
Dim TempFilePath As String
Dim FileExtStr As String
Dim DefaultName As String
Dim UserAnswer As Long
Dim x As Long

Set SourceWB = ActiveWorkbook

'Check for macro code residing in
  If Val(Application.Version) >= 12 Then
    If SourceWB.FileFormat = 52 And SourceWB.HasVBProject = True Then
      UserAnswer = MsgBox("You will be asked for the file name to be sent. " & _
        "Email functionality requires Microsoft Outlook to be installed. " & _
        "Do you wish to proceed?", vbYesNo, "Email A Copy")
    
    If UserAnswer = vbNo Then Exit Sub 'Handle if user cancels
  
    End If
  End If

'Determine Temporary File Path
  TempFilePath = Environ$("temp") & "\"

'Determine Default File Name for InputBox
  If SourceWB.Saved Then
    DefaultName = Left(SourceWB.Name, InStrRev(SourceWB.Name, ".") - 1)
  Else
    DefaultName = SourceWB.Name
  End If

'Ask user for a file name
  TempFileName = Application.InputBox("What would you like to name your attachment? (No Special Characters!)", _
    "File Name", Type:=2, Default:=DefaultName)
    
    If TempFileName = False Then Exit Sub 'Handle if user cancels
  
'Determine File Extension
  If SourceWB.Saved = True Then
    FileExtStr = "." & LCase(Right(SourceWB.Name, Len(SourceWB.Name) - InStrRev(SourceWB.Name, ".", , 1)))
  Else
    FileExtStr = ".xlsm"
  End If

'Optimize Code
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.DisplayAlerts = False

'Save Temporary Workbook
  SourceWB.SaveCopyAs TempFilePath & TempFileName & FileExtStr
  Set DestinWB = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

'Break External Links
  ExternalLinks = DestinWB.LinkSources(Type:=xlLinkTypeExcelLinks)

    'Loop Through each External Link in ActiveWorkbook and Break it
      On Error Resume Next
        For x = 1 To UBound(ExternalLinks)
          DestinWB.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
        Next x
      On Error GoTo 0
      
'Save Changes
  DestinWB.Save

'Create Instance of Outlook
  On Error Resume Next
    Set OutlookApp = GetObject(Class:="Outlook.Application") 'Handles if Outlook is already open
  Err.Clear
    If OutlookApp Is Nothing Then Set OutlookApp = CreateObject(Class:="Outlook.Application") 'If not, open Outlook
    
    If Err.Number = 429 Then
      MsgBox "Outlook could not be found, aborting.", 16, "Outlook Not Found"
      GoTo ExitSub
    End If
  On Error GoTo 0

'Create a new email message
  Set OutlookMessage = OutlookApp.CreateItem(0)

'Create Outlook email with attachment
  On Error Resume Next
    With OutlookMessage
     .To = ""
     .CC = ""
     .BCC = ""
     .Subject = TempFileName
     .Body = ""
     .Attachments.Add DestinWB.FullName
     .Display
    End With
  On Error GoTo 0

'Close & Delete the temporary file
  DestinWB.Close SaveChanges:=False
  Kill TempFilePath & TempFileName & FileExtStr

'Clear Memory
  Set OutlookMessage = Nothing
  Set OutlookApp = Nothing
  
'Optimize Code
ExitSub:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.DisplayAlerts = True

End Sub

 

[V_Malkoti]

My guess is that since the filename is same as the one currently open, Excel throws an error on this line:

Set DestinWB = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

Excel cannot open two files with same name even though they are in different folders. Therefore the variable is not set and you get error 91 on next line.

 

[codyssey]

AH, I forgot about that. That makes perfect sense. Thank you! Is there a way to handle that error? "You cannot save as the same name. Please choose a different name."

 

[V_Malkoti]

Try this:

'Ask user for a file name
strErrMsg = ""
strDefaultMsg = "What would you like to name your attachment? (No Special Characters!)"
Do
    TempFileName = Application.InputBox(strErrMsg & strDefaultMsg, _
            "File Name", Type:=2, Default:=DefaultName)
    If TempFileName = DefaultName Then strErrMsg = "New file name must be different from current one." & vbCrLf
Loop Until (TempFileName <> DefaultName Or TempFileName = False)
If TempFileName = False Then Exit Sub 'Handle if user cancels

 

[codyssey]

Thank you so much. You are a life saver!

 

[V_Malkoti]

Glad I could help.