Convert VBA Code Designed for Windows To Run On A Mac

[excelbytes]

I have a code that works well to send e-mails from Excel based on certain criteria when run. It was designed for Windows. I also need it to run on a Mac. Can anyone explain what needs to change in order to do this? My understanding is that I get an "error 429: Active X component can't create object" because Mac doesn't recognize Active X and therefore won't connect to Outlook.

Here is the code:

Sub Send_Email()
Dim OutApp As Object, OutMail As Object
Dim lLastRow As Long, lRow As Long
Dim sSendTo As String, sSendCC As String, sSendBCC As String
Dim sSubject As String, sTemp As String

On Error GoTo errHandler
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

' Change the following as needed
sSendTo = "person1@email.com"
sSendCC = "person2@email.com"
sSubject = "Project Past Due!"

lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
For lRow = 2 To lLastRow
   If Cells(lRow, 4) <> "COMPLETED" Then
      If Cells(lRow, 2) <= Date Then
         Set OutMail = OutApp.CreateItem(0)
         'On Error Resume Next
         With OutMail
            .To = sSendTo
            If sSendCC > "" Then .CC = sSendCC
            If sSendBCC > "" Then .BCC = sSendBCC
               .Subject = sSubject
               sTemp = "Hello!" & vbCrLf & vbCrLf
               sTemp = sTemp & "The due date has passed for this project: " & vbCrLf & vbCrLf
               ' Assumes project name is in column B
               sTemp = sTemp & " " & Cells(lRow, 1) & vbCrLf & vbCrLf
               sTemp = sTemp & " Please take the appropriate action." & vbCrLf & vbCrLf
               sTemp = sTemp & "Thank you!" & vbCrLf
               .Body = sTemp
               ' Change the following to .Send if you want to
               ' send the message without reviewing first
               '.Send
               .Send
           End With
           Set OutMail = Nothing
           Cells(lRow, 6) = "E-mail sent on: " & Now()
       End If
   End If
Next lRow
exitHere:
Set OutApp = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[Jim Gordon]

Hi

Microsoft Outlook for Mac is not scriptable using Visual Basic for Applications. You have to use AppleScript, which you can launch using MacScript within VBA. Ron DeBruin has example scripts you can use.