Misterbean
New Member
- Joined
- Jan 28, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi dear Community,
I am beginner in Excel VBA and found my way to send a (.xlsm ) workbook in attachment of an email but I struggle with the format since I have to send it in .xlsx format to recipient when the workbook I am working initially is a .xlsm. I also created (in the same macro) an automatic save in specific Path , enabling me to save a copy in .xlsx format. great !
So my question is how may I set in Email attachement the .xlsx version and not the orginal .xlsm one ? - I guess I have to work on the section highlighted in red in the code below ?
Your feedback is highly appreciated
MB0
Here is the code :
Sub SendEmailSave2Path()
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim ws As Worksheet
' Set the workbook and worksheet you want to send
Set wb = ThisWorkbook ' The current workbook
' Create a new Outlook instance
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0) ' 0 represents a mail item
' Compose the email
With OutMail
.To = "" ' Replace with the recipient's email address
.Subject = "My Subject title_" ' Replace with your email subject
.Body = "Hi ," & "Kind regards"
.Attachments.Add wb.FullName ' Attach the entire workbook
.display
End With
' Clean up
Set OutMail = Nothing
Set OutApp = Nothing
Dim MyFilePath As String
Dim MyFileName As String
MyFilePath = "C:\Users\Documents"
MyFileName = "MOR_"
ActiveWorkbook.SaveCopyAs Filename:=MyFilePath & MyFileName & ".xlsx"
End Sub
I am beginner in Excel VBA and found my way to send a (.xlsm ) workbook in attachment of an email but I struggle with the format since I have to send it in .xlsx format to recipient when the workbook I am working initially is a .xlsm. I also created (in the same macro) an automatic save in specific Path , enabling me to save a copy in .xlsx format. great !
So my question is how may I set in Email attachement the .xlsx version and not the orginal .xlsm one ? - I guess I have to work on the section highlighted in red in the code below ?
Your feedback is highly appreciated
MB0
Here is the code :
Sub SendEmailSave2Path()
Dim OutApp As Object
Dim OutMail As Object
Dim wb As Workbook
Dim ws As Worksheet
' Set the workbook and worksheet you want to send
Set wb = ThisWorkbook ' The current workbook
' Create a new Outlook instance
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0) ' 0 represents a mail item
' Compose the email
With OutMail
.To = "" ' Replace with the recipient's email address
.Subject = "My Subject title_" ' Replace with your email subject
.Body = "Hi ," & "Kind regards"
.Attachments.Add wb.FullName ' Attach the entire workbook
.display
End With
' Clean up
Set OutMail = Nothing
Set OutApp = Nothing
Dim MyFilePath As String
Dim MyFileName As String
MyFilePath = "C:\Users\Documents"
MyFileName = "MOR_"
ActiveWorkbook.SaveCopyAs Filename:=MyFilePath & MyFileName & ".xlsx"
End Sub
