Airfix9
Well-known Member
- Joined
- Sep 23, 2005
- Messages
- 886
With the help of this great messageboard, I have created the following procedure:
This calls this macro:
Which uses this set of procedures to work:
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!!!
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!!!