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