Consulting

Results 1 to 6 of 6

Thread: Convert VBA Code Designed for Windows To Run On A Mac

  1. #1

    Convert VBA Code Designed for Windows To Run On A Mac

    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.

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,236
    Location
    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
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,236
    Location
    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
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,236
    Location
    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.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    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.

  6. #6
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,236
    Location
    Not high enough post count I'm assuming. Admin have their rules.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •