File Extension
 

Hi Team

This is more an annoyance than an actual life-or-death problem.

The below code is not the all I use in this instance but, I can tell you everything works as expected, with one small caveat:

When the sheet is attached to the email, ( and this happens with every file I use this code in ) it saves it with two extensions:

example: "myFileName.xlsm.xlsx"

The workbook(s) this is generated from is/are always [.xlsm], but the newly created sheet that attaches to emails is always a [.xlsx].

Any thoughts on how I can restructure the below code to stop this from happening please:

As always
TIA
Mark.


Dim TempFilePath As String, TempFileName As String, FileExtStr As String
Set Sourcewb = ActiveWorkbook

With Destwb
If Val(Application.Version) < 12 Then
FileExtStr = ".xls": FileFormatNum = -4143
Else
Select Case Sourcewb.FileFormat
Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
Case 52:
If .HasVBProject Then
FileExtStr = ".xlsm": FileFormatNum = 52
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
Case 56: FileExtStr = ".xls": FileFormatNum = 56
Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
End Select
End If
End With

TempFilePath = "T:\National\Incident Register\"
TempFileName = Sourcewb.Name

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

On Error Resume Next
With OutMail
.To = toCell
.CC = ccCell
.BCC = ""
.Subject = "Incident Report(s) yet to be Closed"
.Attachments.Add Destwb.FullName
.HTMLBody = strBody & "<br" & RangetoHTML(rng) & "<br" & Signature
.display 'or use .Display
End With
On Error GoTo 0

.Close savechanges:=False
End With

Kill TempFilePath & TempFileName & FileExtStr

File Extension
 

Hi All

I worked it out.

I simply changed: TempFileName = Sourcewb.Name to TempFileName = "myFileName"

Annoyance sorted.

Cheers
Mark.