Sending an email via Excel for Outlook in Mac

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
With the help of this great messageboard, I have created the following procedure:

Code:
Sub MailMyForecast()
    
    'Do some stuff to create a message (called strMailData)
    
    
    Select Case Day(Date)
        Case 1, 21, 31
            strOrd = "th"
        Case 2, 22
            strOrd = "nd"
        Case 3, 23
            strOrd = "rd"
        Case Else
            strOrd = "th"
    End Select
    
    
    
    
    MailOut ThisWorkbook.Sheets("Data").Range("EMAILADDY").Value, "Forecast data as at " & _
            Format(Date, "d") & strOrd & " " & Format(Date, "mmmm") & ", " & Format(Date, "yyyy"), strMailData

End Sub

This calls this macro:

Code:
Function MailOut(MailUser As String, MailHeader As String, MailBody As String, Optional MailAttach As Variant)

' Mails the reports

    On Error Resume Next
    Set objOut = GetObject(, "Outlook.Application") 'FALLS DOWN HERE!
    If objOut Is Nothing Then
        Set objOut = CreateObject("Outlook.Application")
        blnCrt = True
        If objOut Is Nothing Then
            MsgBox "Unable to start Outlook."
            Exit Function
        End If
    End If
    On Error GoTo 0

    On Error Resume Next
    Set objTask = objOut.CreateItem(0)
    If objTask Is Nothing Then
        If blnCrt Then objOut.Quit
        Set objOut = Nothing
        MsgBox "Unable to create task."
        Exit Function
    End If
    On Error GoTo 0

    With objTask
        .To = MailUser
        .CC = ""
        .BCC = ""
        .Subject = MailHeader
        .Body = MailBody
        On Error Resume Next
        .attachments.Add MailAttach
        On Error GoTo 0
        
        '.Attachments.Add MailPath
        'You can add other files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With

    Set objTask = Nothing

    If blnCrt = True Then objOut.Quit
    Set objOut = Nothing

End Function

Which uses this set of procedures to work:

Code:
Option Explicit
Option Private Module
Declare Function OpenProcess Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long
 
Declare Function GetExitCodeProcess Lib "kernel32" _
        (ByVal hProcess As Long, _
        lpExitCode As Long) As Long
 
Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103
Public Sub ShellAndWait(ByVal PathName As String, Optional WindowState)
    Dim hProg As Long
    Dim hProcess As Long, ExitCode As Long
    'fill in the missing parameter and execute the program
    If IsMissing(WindowState) Then WindowState = 1
    hProg = Shell(PathName, WindowState)
    'hProg is a "process ID under Win32. To get the process handle:
    hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
    Do
        'populate Exitcode variable
        GetExitCodeProcess hProcess, ExitCode
        DoEvents
    Loop While ExitCode = STILL_ACTIVE
End Sub
Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
    On Error Resume Next
    bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Function Split97(sStr As Variant, sdelim As String) As Variant
'Tom Ogilvy
    Split97 = Evaluate("{""" & _
                       Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

The problem is that some of my users have Macs, using Office 2011 (MS Outlook for Mac 2011) and I can't find the application name for that, hence the procedure falls over where indicated.

How can I:

(a) Get the procedure to recognise whether it is being used on a Mac and call the correct application, and
(b) Find the proper application name for the Mac?

I am using Office 2007.

Any help gratefully received.

PS - I hate Macs!!!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Forum statistics

Threads
1,224,521
Messages
6,179,291
Members
452,902
Latest member
Knuddeluff

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
Back
Top