Send Excel workbook as attachment

[cmhoz]

I am desprately seeking a way to send an Excel workbook as an attachment to WHATEVER the default mail program is (in this case, OS X Mail).

I have a cross platform file - If the user is on a Windows system, there are lots of easy ways to send email. However, when the user is on a Mac OS X machine, the default mail options do not work (because they are linked to Outlook). SO... here's what I've tried...

1.
ActiveWorkbook.FollowHyperlink _
"mailto:[email protected]" & _
"?subject=I have a dream.." & _
"&body=That one day soon this will work!"

Great. Calls up the default mail program. But - doesn't attach the current workbook.


2. Application.Dialogs(xlDialogSendMail).Show

3. All the different PC options... they all fail in OS X.

This is what excel generates what I record a macro to send workbook as attachment. But - when I play it back, it generates a runtime error and claims that xlDialogSendMail isn't valid.

HELP!!! There has to be SOMEONE out there who knows how to send an excel workbook on a Mac.... I just can't work it out, have even tried looking for an AppleScript solution, but that's a whole new world to me, so I'm very lost.

 

[shades]

Howdy. Several problems: DilaogSendmail does not work on Mac, despite the recordability. Also, the reference to files is slightly different on the Mac than on Win. Also, here is one approach using Entourage (Mac step-sister of Outlook).

Jim McGimpsey Mac MVP has put togehter both VBA and AppleScript to do what you need. Send attachments with Entourage

 

[shades]

BTW, Entourage does not support VBA, so AppleScript is the only way to go.

 

[cmhoz]

Excellent....

I tried the VBA solution and it works perfectly with Mail, but as there is not way to auto add the recipient or subject, I would like to try the AppleScript solution.

I've tried to make a small change to the code so it calls Mail instead of Entourage... but I get this error: "Invalid procedure call or argument."

This is the code:

Public Sub MailSendMail(theBook As Workbook, _
            recipient As String, subject As String)
        Dim mailStr As String
        
        mailStr = _
            "Tell application ""Mail""" & vbNewLine & _
            "make new outgoing message with properties" & _
            "{recipient:""" & recipient & """,subject:""" & subject & _
            """,attachment:""" & theBook.FullName & """}" & vbNewLine _
            & "move the result to out box folder" & vbNewLine & _
            "send" & vbNewLine & _
            "end tell"
            
        MacScript mailStr
        
    End Sub
    
    
    Sub sendit()
        MailSendMail ThisWorkbook, "[email protected]", "this is the subject"
    End Sub

I'm guessing that's because there the script for Mail is slightly different than that for Entourage... but I'm not sure where I need to change things. I've tried running the script in script editor and it fails at 'move to out box folder'. If I remove this, it appears to run, but no mail item is actually sent.

Thanks for any help... AppleScript is brand new to me.

 

[cmhoz]

Almost got it!

Made some changes... now it looks like this:

    Public Sub MailSendMail(theBook As Workbook, _
            recipient As String, subject As String)
        Dim mailStr As String
        
        mailStr = _
            "Tell application ""Mail""" & vbNewLine & _
            "make new outgoing message with properties" & _
            "{recipient:""" & recipient & """,subject:""" & subject & _
            """,attachment:""" & theBook.FullName & """,visible:true}" & vbNewLine _
            & "end tell"
        MacScript mailStr
    End Sub


Sub sendmessage()
    MailSendMail ThisWorkbook, "name@address", "This is the subject"
End Sub

BUT... the recipient isn't showing up in the message. Subject is fine, but no recipient....