VBA code to extract sheet as values, clear all Ranges, Links, Form buttons and email to pre-selected list of recipients

[GVK]

Hi All,

I am using the below code, which is supposed to extract a worksheet from a much larger Excel file, unptotect this tab, paste everything as values, delete all named ranges, links and form buttons and save a date- and time-stamped copy of the file in a specific folder. Then email this file to a list of recipients. The code seems to be mostly working as intended but some times it is unstable (might throw out the debugger) and I cannot see why. I wanted to post the code here in case anyone can see anything wrong with it and if s/he has any view/s on how to simplify and streamline the code?

Many thanks in advance for your help!

--------------------------------------------------------------------------------------------------------------------------------------------
Sub Email_WHT()

Dim strFileName As String
Dim xName As Name
Dim x As Long
Dim i As Integer
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

Const strFilePath As String = "[path at this the file will be saved]"


If ActiveWorkbook.Name = "[Filename]" Then
If MsgBox("[Message Box]", vbYesNo) = vbYes Then
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Call UnprotectWS

ActiveWorkbook.Sheets("[Sheet Name]").Activate
ActiveSheet.Copy
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

strFileName = "WHT " & Format(Now(), "ddmmyyyy hhmmss") & ".xlsx"
filename = strFilePath & strFileName

With ActiveWorkbook
.SaveAs filename, FileFormat:=51
End With

For Each xName In Application.ActiveWorkbook.Names
If Split(xName.Name, ".")(0) <> "_xlfn" Then
xName.Delete
End If
Next

'Create an Array of all External Links stored in Workbook
ExternalLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

'Loop Through each External Link in ActiveWorkbook and Break it
For x = 1 To UBound(ExternalLinks)
ActiveWorkbook.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x

For i = 1 To 50
ActiveSheet.Buttons.Delete
Next i

ActiveWorkbook.Close savechanges:=True

With OutMail
.To = ""
.cc = ""
.BCC = ""
.body = ""
.Subject = ""
.Attachments.Add filename
.display
End With

Set OutMail = Nothing
Set OutApp = Nothing

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Else
End If
Else
End If
End Sub