Convert VBA Code Designed for Windows To Run On A Mac

[Runninrep]

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.

I keep trying to post the code here, but the system keeps denying it, says it has too many URL's or bad words, but there are neither in the code. How else can I provide the current code I have?

Thanks in advance for your help.

[Aussiebear]

Here is your 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
SetOutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
'Change the following as needed
sSendTo = "Michael@lafamilialf.com"
sSendCC = "mrempel@excel-bytes.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
         SetOutMail = 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 fro this project: " & vbCrLf & vbCrLf
                  'Assumes project name is in Column B
                  sTemp & " " & Cells(lRow,1) & vbCrLf & vbCrLf
                  sTemp = sTemp & "Please take appropriate action." & vbCrLf & vbCrLf
                  s temp = 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

[Aussiebear]

Just two changes I'd make initially and then we'll wait for someone to review this 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
SetOutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
'Change the following as needed
sSendTo = "Michael@lafamilialf.com"
sSendCC = "mrempel@excel-bytes.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
         SetOutMail = 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 fro this project: " & vbCrLf & vbCrLf
                  'Assumes project name is in Column B
                  sTemp & " " & Cells(lRow,1) & vbCrLf & vbCrLf
                  sTemp = sTemp & "Please take appropriate action." & vbCrLf & vbCrLf
                  s temp = sTemp & "Thank You!" & vbCrLf
                  .Body = sTemp
                  'Change the following to.Send if you want to send the message without reviewing first .Send
                  .Send
               End If
            End If
         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

[Aussiebear]

Just had a quick look at something similar from Ron DeBruin at learn.microsoft.com

Sub Mail_Selection_In_Excel2011()'For Excel 2011 for the Mac and Apple Mail
    Dim Source As Range
    Dim Destwb As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long


    If Val(Application.Version) < 14 Then Exit Sub


    Set Source = Nothing
    On Error Resume Next
    Set Source = Selection.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0


    If Source Is Nothing Then
        MsgBox "The source is not a range or the sheet is protected, " & _
               "please correct and try again.", vbOKOnly
        Exit Sub
    End If


    If ActiveWindow.SelectedSheets.Count > 1 Or _
       Selection.Cells.Count = 1 Or _
       Selection.Areas.Count > 1 Then
        MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
               "You have more than one sheet selected." & vbNewLine & _
               "You only selected one cell." & vbNewLine & _
               "You selected more than one area." & vbNewLine & vbNewLine & _
               "Please correct and try again.", vbOKOnly
        Exit Sub
    End If


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    Set wb = ActiveWorkbook
    Set Destwb = Workbooks.Add(xlWBATWorksheet)


    Source.Copy
    With Destwb.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial Paste:=xlPasteValues
        .Cells(1).PasteSpecial Paste:=xlPasteFormats
        .Cells(1).Select
        Application.CutCopyMode = False
    End With


    'Save format and extension
    FileExtStr = ".xlsx": FileFormatNum = 52


    'Or if you want it in xls format, use:
    'FileExtStr = ".xls": FileFormatNum = 57




    'Save the new workbook, mail it, and delete it.
    'If you want to change the file name then change only TempFileName
    TempFilePath = MacScript("return (path to documents folder) as string")
    TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")


    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        MailFromMacWithMail bodycontent:="Hi there", _
                    mailsubject:="Mail Selection Test", _
                    toaddress:="ron@debruin.nl", _
                    ccaddress:="", _
                    bccaddress:="", _
                    attachment:=.FullName, _
                    displaymail:=False
        .Close SaveChanges:=False
    End With


    KillFileOnMac TempFilePath & TempFileName & FileExtStr


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Perhaps this may give you some ideas. Notice that he doesn't use vbCrLf but vbNewLine.

[Runninrep]

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.Aussiebear, thanks for getting my code onto the forum. Do you have any idea why I was getting messages saying that it would not post because it had too may URL's or not acceptable language?

I am also told that 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. I am a little familiar with VBA, just enough to get me in trouble, but am not at all familiar with MacSrcipt, so I'm not sure where to start. But thank you very much for the work you did on this.

[Aussiebear]

Not high enough post count I'm assuming. Admin have their rules.