HELP - Outlook 2016 Email generation from Excel using VBA with Applescript

[morgrim]

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

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:
#####################

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:

#######################
#######################
#######################

[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

[B]--Script: SimpleScript.scpton[/B] myapplescripthandler(paramString)
	
	#do something with paramString 
	[B]return[/B] "You told me " & paramString
	
[B]end[/B] myapplescripthandler

[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.

 

[morgrim]

Bump...