StephenC101
New Member
- Joined
- Jan 16, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hello everyone,
I am an Excel novice, I have a macro working in Windows, where the user clicks a button and the email client (Outlook) is opened, the spreadsheet is attached and 'To', 'Subject' and email body are pre-populated.
The objective to allow the user to amend the email wording as needed, plus allows them to hit send.
The code works fine in Windows but I have to admit I am struggling to get it working on Mac. Appreciate any help or suggestions. Thanks!!!
I am an Excel novice, I have a macro working in Windows, where the user clicks a button and the email client (Outlook) is opened, the spreadsheet is attached and 'To', 'Subject' and email body are pre-populated.
The objective to allow the user to amend the email wording as needed, plus allows them to hit send.
The code works fine in Windows but I have to admit I am struggling to get it working on Mac. Appreciate any help or suggestions. Thanks!!!
Windows VBA Script (works on Window machine) | Attempt to replicate macro for Mac |
Sub CreateEmailWindows() 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 Set ws = ThisWorkbook.Sheets("Extd and Advd") ' Replace "SheetName" with your sheet's name ' 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 = "supportsalesquote@ibm.com" .subject = "Please help me create a sales quote based on the customer and products in the file attachment" .Body = "Dear CoE Team, please assist me in creating a sales quote for the customer & parts listed in the attachment. Let me know if you have any additional questions. Many thanks!" .attachments.Add wb.FullName ' Attach the entire workbook ' .Attachments.Add ws.UsedRange.Address ' Attach only the used range of the worksheet .Display End With ' Clean up Set OutMail = Nothing Set OutApp = Nothing End Sub | Sub SendEmailMac() Dim OutApp As Object Dim OutMail As Object Dim wb As Workbook Dim ws As Worksheet Dim tempFile As String ' Set the workbook and worksheet you want to send Set wb = ThisWorkbook ' The current workbook Set ws = ThisWorkbook.Sheets("Extd and Advd") ' Replace "SheetName" with your sheet's name ' Save a temporary copy of the workbook tempFile = Environ$("TEMP") & Application.PathSeparator & "TempWorkbook.xlsx" wb.SaveCopyAs tempFile ' Create a new Outlook instance On Error Resume Next Set OutApp = GetObject(class:="Outlook.Application") On Error GoTo 0 If OutApp Is Nothing Then Set OutApp = CreateObject(class:="Outlook.Application") ' Create a new mail item Set OutMail = OutApp.CreateItem(0) ' 0 represents a mail item ' Compose the email With OutMail .To = "supportsalesquote@ibm.com" .subject = "Please help me create a sales quote based on the customer and products in the file attachment" .Body = "Dear CoE Team, please assist me in creating a sales quote for the customer & parts listed in the attachment. Let me know if you have any additional questions. Many thanks!" .attachments.Add tempFile ' Attach the temporary workbook copy .Display End With ' Clean up Set OutMail = Nothing Set OutApp = Nothing ' Delete the temporary file Kill tempFile End Sub |