Jiwa

I need to enhance the emailing based on Contact Positions. The following breakout which uses the spEmailAddress works in V6 but I am struggling to find the relevant event handlers. Can you point me in the right direction for V7
*********************** V6 Breakout
'Form Email Invoice Clicked - Modified By Atronics 07/12/2006
'Last Modified :- 26/09/2011

Dim ReadHandle
Dim SQL

With SalesOrderObject.Database
ReadHandle = .StatementOpen(.ConnectionRead1, False, "", 0)

SQL = "EXEC spEmailAddress '" & SalesOrderObject.DebtorID & "', 'Email Invoice'"
If .ExecuteSelect(CInt(ReadHandle), CStr(SQL)) <> 0 Then
.BindMem CInt(ReadHandle), 1, vbString
If .FetchRow(CInt(ReadHandle)) = True Then
EmailTo = .getData(CInt(ReadHandle), 1)
End If
End If

.StatementRenew CInt(ReadHandle)

If EmailTo = "" Then
MsgBox("No email address on file. Email cancelled")
rtnCancel = True
Else
SQL = "EXEC spEmailAddress '" & SalesOrderObject.DebtorID & "', 'CC Email Invoice'"
If .ExecuteSelect(CInt(ReadHandle), CStr(SQL)) <> 0 Then
.BindMem CInt(ReadHandle), 1, vbString
If .FetchRow(CInt(ReadHandle)) = True Then
CC = .getData(CInt(ReadHandle), 1)
End If
End If

End If

.StatementRenew CInt(ReadHandle)
.StatementClose CInt(ReadHandle)
Subject = "Invoice From Advanced Seed"
NameToGiveAttachment = "Invoice " & SalesOrderObject.InvoiceNo & "-D0" & CStr(SalesOrderObject.SelectedHistoryNo)
Message = "Thank you for your order. Please find our invoice attached" & vbCrLf & vbCrLf & _
"Kind Regards" & vbCrLf & _
SalesOrderObject.Database.JiwaLoginUserName
End With

End Sub

***************************spEmailAddress
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[spEmailAddress]
(@DebtorID char(20),
@Position varchar(50))
AS

Declare @EmailAddress varchar(200)

Select Top 1 @EmailAddress = cc.EmailAddress
From CN_Contact cc, CN_Main cm, CN_ContactPosition cp1
Where cc.ProspectID = cm.ProspectID
And cm.DebtorID = @DebtorID
And cc.PrimaryID = cp1.ContactPositionID
And cp1.Position = @Position

If @EmailAddress Is Null
Select Top 1 @EmailAddress = cc.EmailAddress
From CN_Contact cc, CN_Main cm, CN_ContactPosition cp2
Where cc.ProspectID = cm.ProspectID
And cm.DebtorID = @DebtorID
And cc.SecondaryID = cp2.ContactPositionID
And cp2.Position = @Position

If @EmailAddress Is Null
Select Top 1 @EmailAddress = cc.EmailAddress
From CN_Contact cc, CN_Main cm, CN_ContactPosition cp3
Where cc.ProspectID = cm.ProspectID
And cm.DebtorID = @DebtorID
And cc.TertiaryID = cp3.ContactPositionID
And cp3.Position = @Position

Select EmailAddress = IsNull(@EmailAddress, '')

GO

Atronics

Posts: 21
Joined: Fri Feb 29, 2008 4:40 pm

Attached is a fairly complete conversion of what you had in Version 6.

It looks like a lot, but I simply copied the standard "Sales Order - Custom Email" plugin and added the bits to call your stored procedure for the TO and CC recipient address.

Mike

Mike,
Does V7 treat the sp's differently to V6?
When I run the plugin, I get the following error, even though the sp is available.
'spEmailAddress" is not declared. It may be inaccessible due to its protection level.

Your error is because on line 66 you have:

Code: Select all

Call spEmailAddress

It's thinking you want to invoke a methods named spEmailAddress, and there is none.

The plugin I supplied has that line as:

Code: Select all

' Call spEmailAddress stored proc to obtain TO address   

Note the apostrophe at the start of the line - this indicates it is a comment and the compiler will ignore it. Your line is missing that comment indicator.

Did you modify the plugin I supplied after importing it? If so, undo your changes or re-import the supplied plugin and test again.