Adapting to Mac User enviorment

Keoxes

New Member
Joined
Apr 30, 2014
Messages
10
I have perfectly working script that works on windows that creates a directory to save the sheet as pdf and triggers outlook mail and self attaches the pdf ready to mail.
but I now have numerous mac users where the script breaks on each one of these functions. could some one please help to add mac variables to this please so that it works on both Windows and Macs by detecting the OS. I have never worked on linux or mac to insert the appropriate variables

VBA Code:
Public Sub SaveToPDFAndMail()

Application.ScreenUpdating = False 'Turn off screen updating

Dim tDate As String
Dim CName As String
Dim FName As String
Dim DName As String
Dim OutApp As Object
Dim OutMail As Object
Dim sheetArray As Variant
'Dim Signature As String

sheetArray = Array("Overview", "LoECalc")
'Sheets("Overview").Select ' Select sheet that you want to save to PDF
tDate = Format(DateTime.Now, "yyyymmdd_hhmm") 'Format Today's date for filename
CName = Sheets("Overview").Range("C15").Value
FName = CName & "_Quote_" & tDate 'create filename string

DName = Environ("USERPROFILE") & "\Documents\Quotes" ' Generate folder name
If Dir(DName, vbDirectory) = "" Then MkDir DName ' Create folder if it's not there

'select and save sheet to PDF using filename from FName string
Sheets(sheetArray).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=DName & "\" & FName & ".pdf", Quality:=xlQualityStandard

Sheets("LoECalc").Select 'Reselect page

Application.ScreenUpdating = True 'Turn on screenupdating
MsgBox ("Your Quote named " & FName & " has been saved to your Quotes folder in Documents.") 'Display a message box outlining what the PDF has been called.

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
    With OutMail
    .Display
    End With
        Signature = OutMail.Body
    With OutMail
    .To = ""
    .Subject = "ACME " & CName & " Quote"
    .Attachments.Add DName & "\" & FName & ".pdf"
    .HTMLBody = "<p class=MsoNormal> <Span style='font-size:11.0pt'>Hi  , <br><br>Please see attached quote requested for " & CName & ". </Span></p> " & .HTMLBody
   

End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,488
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your folder paths won't work on Macs - you should use Application.Pathseparator instead of hardcoding the backslashes. You're also in for a world of fun with sandboxing, so I suggest you read Ron's pages here: Home | Mac Excel Automation
 

Watch MrExcel Video

Forum statistics

Threads
1,127,866
Messages
5,627,352
Members
416,244
Latest member
JBKool

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top