Sales Order emailing based on contact position

[Atronics]

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

[Mike.Sheen]

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

[Atronics]

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.

[Mike.Sheen]

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.