Help Getting Macro Written on PC/Windows to Run on a Mac...

[thurberm]

Looking for assistance with getting this macro (code below), written for/running fine on a PC with Windows 7/MS Office 2010 to run just as well on a Mac with MS Office for Mac 2011. Macro no workie, and I have zero Mac knowledge.

The macro references an external function which checks to see if Outlook is running, then starts it if it isn’t. It then creates and displays a pre-formatted and addressed e-mail with a PDF of the current worksheet attached, ready to send.

All the other macros in the associated workbook run fine on the Mac, it’s just this one that fails. I’m guessing that it has something to do with either the creation of the PDF on the desktop or the manner in which Outlook is started, or both. But that’s only a semi-educated guess.

Any assist from you Mac gurus out there would be greatly appreciated!

Sub Send_Active_Sheet_as_PDF_Attachment()
 
  Dim IsCreated As Boolean
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  On Error GoTo ErrDataEntry
 
  ' Define PDF filename
 
  Title1 = Range("N2").Value
  Title2 = "IAR Weight & Balance Form for " & Range("N2").Value
 
  PdfFile = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & Title1 & ".pdf"
 
  ' Export activesheet as PDF
  
  With ActiveSheet
 
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
  End With
 
  '  Use already open Outlook or start it if it's not open already, dump the e-mail in the outbox
 
  On Error Resume Next
   
  If Not IsOutlookRunning Then
 
    CreateObject("wscript.shell").Run "outlook.exe", 7, False
   
  End If
 
  Set OutlApp = CreateObject("Outlook.Application")  ' Sets the already running instance of Outlook
 
  On Error GoTo 0
 
  ' Prepare e-mail with PDF attachment
 
  With OutlApp.CreateItem(0)
 
    ' Prepare e-mail
 
    .Subject = Title2
    .To = "" ' <-- Put email of the recipient here
    .BCC = "" ' <-- Put email of 'copy to' recipient here
    .Body = "ALCON," & vbLf & vbLf _
          & "Weight & balance form for today's flight attached in PDF format." & vbLf & vbLf _
          & "Regards," & vbLf & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
  
    ' Try to send the e-mail you just created
   
    .Display
   
  End With
 
  ' Release the memory of object variable
 
  Set OutlApp = Nothing
 
Exit Sub
 
ErrDataEntry:
 
  MsgBox "Unable. You must enter departure and destination ICAO code and a date at the top of the worksheet. Click OK to return and try again."
 
End Sub

 

[jsotola]

mac does not have any windows functions (mac is similar to linux)

"WScript.Shell" probably will not work

file path separators are / not \

check out this page

Excel 2011 for the Mac Tips

 

[thurberm]

Thanks, jsotola!

That link was chock full of win. I especially liked the code to test whether Excel is running on a Mac or a PC and run the appropriate macro.

Now, as soon as I can figure out how to create a PDF on a Mac user's desktop without knowing their user name, I'll be in business...