Any help here would be incredibly appreciated. After multiple very long days trying to get this work I have exhausted my skill set and all options. I am trying to get this to work based on research and using the source below:
I am not trying to take any credit for anyone's work here. This code is primarily from this source: Mail from Excel 2016 with Mac Mail
More info with respect to Outlook 2016 here: AppleScriptTask in Office 2016 for the Mac
No matter what I try to do I keep getting: Run-time error '5': Invalid Procedure Call or argument
I am using:
MacBookPro running OS X 10.10.5
Excel 2016 - Version: 15.19.1 (160212)
####################
Main Script below
####################
#####################
This test Script work fine:
#####################
#######################
#######################
#######################
Below this point is the Applescripts:
#######################
#######################
#######################
Please let me know if I can provide any further information.
I am not trying to take any credit for anyone's work here. This code is primarily from this source: Mail from Excel 2016 with Mac Mail
More info with respect to Outlook 2016 here: AppleScriptTask in Office 2016 for the Mac
No matter what I try to do I keep getting: Run-time error '5': Invalid Procedure Call or argument
I am using:
MacBookPro running OS X 10.10.5
Excel 2016 - Version: 15.19.1 (160212)
####################
Main Script below
####################
Code:
Sub GenerateOutlookEmail()'For Excel 2016 for the Mac and Outlook 2016
'If you use Excel 2011 check out the other code on my site
'http://www.rondebruin.nl/mac/mail.htm
Dim FileExtStr As String, FileFormatNum As Long
Dim Destwb As Workbook, TempFilePath As String, TempFilePath2 As String
Dim TempFileName As String, NameFolder As String, SpecialFolder As String
Dim subject As String, mailbody As String, toaddress As String, ccaddress As String
Dim bccaddress As String, displaymail As Boolean, fileattachment As String
Dim ScriptStr As String, RunMyScript As String
Dim Sourcewb As Workbook
'Stop the macro if it is not Office 2016
If Val(Application.Version) < 15 Then Exit Sub
Set Sourcewb = ActiveWorkbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'***** ONLY EDIT THE PART BELOW WITH YOUR INFO *****
'Fill in the information to create the mail
'When you use more mail address separate them with a ,
subject = Sheets(1).Range("B34").Value
mailbody = Sheets(1).Range("B35").Value
toaddress = Sheets(1).Range("B36").Value
ccaddress = Sheets(1).Range("B37").Value
bccaddress = Sheets(1).Range("B38").Value
displaymail = True ' Use False to send directly
ScriptStr = subject & ";" & mailbody & ";" & toaddress & ";" & ccaddress & ";" & _
bccaddress & ";" & displaymail & ";" & fileattachment
Debug.Print "Script string is: " & ScriptStr
'Call the RDBMacOutlook.scpt script file with the AppleScriptTask function
' RunMyScript = AppleScriptTask("RDBMacOutlook.scpt", "myapplescripthandler", CStr(ScriptStr))
RunMyScript = AppleScriptTask("RDBMacOutlook.scpt", "myapplescripthandler", ScriptStr)
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
#####################
This test Script work fine:
#####################
Code:
Sub TestFile()
Dim RunMyScript As String
Dim FilePathName As String
Dim myparam As String
' FilePathName = "/Users/emiddlet/Documents/Tech Notes/Excel/Email from Sheet/Eric-v1-MacOutlookWithExcel2016.xlsm"
FilePathName = "/Users/emiddlet/Library/Application Scripts/com.microsoft.Excel/RDBMacOutlook.scpt"
myparam = "This is the text i am passing to the AppleScript"
RunMyScript = AppleScriptTask("MyFileTest.scpt", "ExistsFile", FilePathName)
Debug.Print "RunMyScript value is: " & RunMyScript
RunMyScript = AppleScriptTask("SimpleScript.scpt", "myapplescripthandler", myparam)
Debug.Print "Applescript returned: " & RunMyScript
End Sub
#######################
#######################
#######################
Below this point is the Applescripts:
#######################
#######################
#######################
Code:
[B]--Script: MyFileTest.scpt[/B]
[B]on[/B] ExistsFile(filePath)
--check if file exists and type is file
[B]tell[/B] [I]application[/I] "System Events" [B]to[/B] [B]return[/B] ([B]exists[/B] [I]disk item[/I] filePath) [B]and[/B] [I]class[/I] [B]of[/B] [I]disk item[/I] filePath = [I]file[/I]
[B]end[/B] ExistsFile
Code:
[B]--Script: SimpleScript.scpton[/B] myapplescripthandler(paramString)
#do something with paramString
[B]return[/B] "You told me " & paramString
[B]end[/B] myapplescripthandler
Code:
[B]--Script: RDBMacOutlook.scpt[/B]
--27-10-2015
[B]on[/B] myapplescripthandler(paramString)
[B]set[/B] {fieldValue1, fieldValue2, fieldValue3, fieldValue4, fieldValue5, fieldValue6, fieldValue7} [B]to[/B] SplitString(paramString, ";")
[B]tell[/B] [I]application[/I] "Microsoft Outlook"
[B]set[/B] NewMail [B]to[/B] ([B]make[/B] new [I]outgoing message[/I] with properties {subject:fieldValue1, content:fieldValue2})
[B]tell[/B] NewMail
[B]repeat[/B] [B]with[/B] toRecipient [B]in[/B] [B]my[/B] SplitString(fieldValue3, ",")
[B]make[/B] new [I]to recipient[/I] at [B]end[/B] [B]of[/B] [I]to recipients[/I] with properties {[I]email address[/I]:{address:contents [B]of[/B] toRecipient}}
[B]end[/B] [B]repeat[/B]
[B]repeat[/B] [B]with[/B] toRecipient [B]in[/B] [B]my[/B] SplitString(fieldValue4, ",")
[B]make[/B] new [I]to recipient[/I] at [B]end[/B] [B]of[/B] [I]cc recipients[/I] with properties {[I]email address[/I]:{address:contents [B]of[/B] toRecipient}}
[B]end[/B] [B]repeat[/B]
[B]repeat[/B] [B]with[/B] toRecipient [B]in[/B] [B]my[/B] SplitString(fieldValue5, ",")
[B]make[/B] new [I]to recipient[/I] at [B]end[/B] [B]of[/B] [I]bcc recipients[/I] with properties {[I]email address[/I]:{address:contents [B]of[/B] toRecipient}}
[B]end[/B] [B]repeat[/B]
[B]make[/B] new [I]attachment[/I] with properties {[I]file[/I]:[I]POSIX file[/I] fieldValue7 [B]as[/B] [I]alias[/I]}
[B]if[/B] fieldValue6 [B]as[/B] [I]boolean[/I] = [I]true[/I] [B]then[/B]
[B]open[/B] NewMail
[B]activate[/B] NewMail
[B]else[/B]
[B]send[/B] NewMail
[B]end[/B] [B]if[/B]
[B]end[/B] [B]tell[/B]
[B]end[/B] [B]tell[/B]
[B]end[/B] myapplescripthandler
[B]on[/B] SplitString(TheBigString, fieldSeparator)
[B]tell[/B] AppleScript
[B]set[/B] oldTID [B]to[/B] text item delimiters
[B]set[/B] text item delimiters [B]to[/B] fieldSeparator
[B]set[/B] theItems [B]to[/B] [I]text items[/I] [B]of[/B] TheBigString
[B]set[/B] text item delimiters [B]to[/B] oldTID
[B]end[/B] [B]tell[/B]
[B]return[/B] theItems
[B]end[/B] SplitString
Please let me know if I can provide any further information.