Mac Excel macro help needed please

[StephenC101]

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!!!

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

 

[jkpieterse]

I suggest to have a look at Ron de Bruin's site: Mail with Mac Mail | Mac Excel Automation