Hi Experts!
I have a line item in my vba that saves an excel file in a temporary folder and then attach in an outlook email. It perfectly works on a normal laptop but when my co-worker tried it in his mac, the vba is debugging showing a run time error '1004': Microsoft Excel cannot access the file. Something with the file name or path doesn't exist.. Is there a way to tweak below code that will be able to work even on mac laptops, or maybe if it detected that it's a mac laptop, just save the file on the desktop instead of accessing temp file folder?
I have a line item in my vba that saves an excel file in a temporary folder and then attach in an outlook email. It perfectly works on a normal laptop but when my co-worker tried it in his mac, the vba is debugging showing a run time error '1004': Microsoft Excel cannot access the file. Something with the file name or path doesn't exist.. Is there a way to tweak below code that will be able to work even on mac laptops, or maybe if it detected that it's a mac laptop, just save the file on the desktop instead of accessing temp file folder?
Code:
Sub EmailWorkbook_Issue()
'PURPOSE: Create email message with ActiveWorkbook attached
'SOURCE: www.TheSpreadsheetGuru.com
Dim SourceWB As Workbook
Dim DestinWB As Workbook
Dim OutlookApp As Object
Dim OutlookMessage As Object
Dim TempFileName As Variant
Dim TempFilePath As String
Dim FirstName As String
Dim LastName As String
Dim c As Long
Set SourceWB = ActiveWorkbook
'Determine Temporary File Path and File Name
TempFilePath = Environ$("temp") & "\"
TempFileName = "Access Issue from " & Application.UserName & " - " & Format(DateTime.Now, "MMM dd, yyyy")
'Save Temporary Workbook
[B] SourceWB.SaveCopyAs TempFilePath & TempFileName & ".xls" -- this is where i encountered the error[/B]
Set DestinWB = Workbooks.Open(TempFilePath & TempFileName & ".xls")
'Save Changes
DestinWB.Save
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
'Create Instance of Outlook
On Error Resume Next
Set OutlookApp = GetObject(class:="Outlook.Application") 'Handles if Outlook is already open
Err.Clear
If OutlookApp Is Nothing Then Set OutlookApp = CreateObject(class:="Outlook.Application") 'If not, open Outlook
If Err.Number = 429 Then
MsgBox "Outlook could not be found, aborting.", 16, "Outlook Not Found"
GoTo ExitSub
End If
On Error GoTo 0
'Create a new email message
Set OutlookMessage = OutlookApp.CreateItem(0)
c = InStr(Application.UserName, ",")
FirstName = Right(Application.UserName, c + 1)
LastName = Left(Application.UserName, c - 1)
'Create Outlook email with attachment
On Error Resume Next
With OutlookMessage
.To = "bmssecurity.im@pg.com"
.CC = ""
.BCC = ""
.Subject = TempFileName
.Body = "Hi BMS Security," & vbNewLine & vbNewLine & "Please help me with my access issue. See attached." & vbNewLine & vbNewLine & "Thanks," & vbNewLine & FirstName & " " & LastName
.Attachments.Add DestinWB.FullName
.Display
End With
On Error GoTo 0
'Close & Delete the temporary file
DestinWB.Close SaveChanges:=False
Kill TempFilePath & TempFileName & ".xls"
'Clear Memory
Set OutlookMessage = Nothing
Set OutlookApp = Nothing
'Optimize Code
ExitSub:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
End Sub
