Tweak to existing VBA code to send emails on Mac

**mrbusto71** · 06-04-2018, 02:17 PM

Y'all, I'm about to pull what little hair I have left out of my head.

I know sending emails from Mac using VBA is a pain, and I know Ron de Bruin has a workaround that's wonderful. The issue is that his workaround requires the installation of a script onto the end user's machine, and I can't use it as a result (my IT department won't allow me to create an automated script to place the file in the correct location and I don't trust the end users to execute properly).

So I might be up the proverbial creek here, but this is all I want to know:

Is there a way to modify this code

Formula:

Sub Mail()

    Dim OutlookApp As Object

    Dim Mess As Object

    Set OutlookApp = CreateObject("Outlook.Application")

    Set Mess = OutlookApp.CreateItem(olMailItem)

    With Mess

to NOT use Outlook as its default mailing application? Or to use a Hyperlink in some way instead? The HYPERLINK function works!... until there's more than like, 4 email addresses being concatenated, then it returns the #VALUE! error.

I'm at my wit's end. I just want this email functionality to work on both Mac and PC, nothing more. PC is a charm. Mac is... just ugh.

If anyone has ANY functional suggestions that don't involve my end users on Mac having to do anything other than click a button/link, I will name a star after you.
Please help.

**Gregor y** · 06-18-2018, 11:20 PM

Just an FYI, the reason CreateObject("Outlook.Application") works is due to Windows' COM model, which all the other OS's don't have(ok except maybe WINE on Linux). I don't know the Nuts'n'Bolts of Mac's OS, but I feel fairly confident in stating that MS did not implement whichever low-level marshaling system Mac has into Mac-Excel (we can barely get them to do it for Windows, and my fear is not at all for UWP).

however since it sounds like you've had some limited success with hyperlinks have you tried Workbook.FollowHyperlink yet?

Private Sub MailTest1()
    Dim str As String
    str = "mailto:[email protected]"
    str = str & ",[email protected]"
    str = str & "?="
    str = str & "&[email protected]"
    str = str & ",[email protected]"
    str = str & "&[email protected]"
    str = str & ",[email protected]"
    str = str & "&subject=" & Replace("Here's an awesome email", " ", "%20")
    str = str & "&body=" & Replace("Here's an awesome email", " ", "%20")
    
    ActiveWorkbook.FollowHyperlink Address:=str
End Sub

**Gregor y** · 06-28-2018, 06:24 PM

also just stumbled upon this SO Article which may be of some use to you, but don't ask me how it works I can't test it without a mac.

**mikerickson** · 06-28-2018, 06:30 PM

Have you considered instead of the .scrpt file, that you make a big string function and pass the result to the MacScript function.

Tweak to existing VBA code to send emails on Mac

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Tweak to existing VBA code to send emails on Mac

Re: Tweak to existing VBA code to send emails on Mac

Re: Tweak to existing VBA code to send emails on Mac

Re: Tweak to existing VBA code to send emails on Mac

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Existing Code tweak needed

[SOLVED] Slight Tweak to existing code

Use of CDO code to send emails failing

Help with code to send automatic emails

Any Excel code to send emails automatically

Tweak an Existing Code

existing macro to send emails to multiple recipents needs fix

Bookmarks

Bookmarks

Posting Permissions