Convert an activeworkbook on xlsx to put in an email-CodePudding

I have a mail service who is good and i want to put my active workbook on my mail but not on xlsm, i want on xlsx.

I tried a solution but the convertion don't work (error).

My code :

Private Sub CommandButton4_Click()

Dim TheMail As Variant
Dim Path_name As String
Dim File_name As String
Dim Complete_File_name As String


Set LeMail = CreateObject("Outlook.Application")

File_name = ActiveWorkbook.Name
File_name , FileFormat:=xlOpenXMLWorkbook
Path_name = ThisWorkbook.Path
Complete_File_name = Path_name & "\" & File_name

With TheMail.CreateItem(olMailItem)
    .Subject = "Prerequisite"
    .To = ""
    .Body = ""
    .Attachments.Add Complete_File_name & ".xlsx"
    .Display
End With
End Sub

Thanks you for all of your response, Have a nice day !

CodePudding user response：

You cannot simple add a xlsx extension! They are two different workbooks. You should firstly save it as xlsx, to make the conversion you are talking about. Anyhow, a file having the name you try attaching does not even exist...

Please, use the next way:

Private Sub CommandButton4_Click()
 Dim TheMail As Variant, Path_name As String, File_name As String
 Dim Complete_File_name As String

 Set TheMail = CreateObject("Outlook.Application")

   File_name = ActiveWorkbook.name
   Path_name = ThisWorkbook.path
   Complete_File_name = Path_name & "\" & left(File_name, InStrRev(File_name, ".") - 1) & ".xlsx"
   ActiveWorkbook.saveas Complete_File_name, xlWorkbookDefault

 With TheMail.CreateItem(olMailItem)
    .Subject = "Prerequisite"
    .To = ""
    .body = ""
    .Attachments.Add Complete_File_name
    .Display
 End With
End Sub

If you do not need the 'converted' xlsx file, you can delete it. After closing, or use SaveCopyAs instead of saveAs, to not be necessary to close it.