Argument Not Optional?????

[chainedtodesk]

i have the below code set up but i keep getting an "Argument Not Optional" when i try to execute it. and it always points to the statement "SendCustList (rst1!addr)" i have this running without the attachment piece, but in trying to add that part it wont go, any ideas????? thanks



Option Compare Database
Option Explicit

Function sender() As Boolean

Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim sql1 As Variant
Dim sql2 As Variant

Set dbs = CurrentDb

sql1 = "Select addr from tblEmail"

Set rst1 = dbs.OpenRecordset(sql1)

sql2 = "Select atch from tblEmail"

Set rst2 = dbs.OpenRecordset(sql2)

Do Until rst1.EOF

SendCustList (rst1!addr) <- ERRORS HERE

SendCustList (rst2!atch)

rst1.MoveNext

rst2.MoveNext

Loop

rst1.Close
rst2.Close
dbs.Close

End Function

Function SendCustList(addr As String, atch As String) As Boolean

Dim objoutlook As Object
Dim objitem As MailItem

Set objoutlook = CreateObject("Outlook.Application")
' Create olMailItem
Set objitem = objoutlook.CreateItem(olMailItem)
With objitem
.Subject = "weekly sales flyer."
.To = addr
'.CC = "xxx@xxx.xxx"
'.BCC = "xxx@xxx.xxx"
.Body = "weekly sales flyer."
.Attachments.Add atch
.send
End With
Set objitem = Nothing
Set objoutlook = Nothing

End Function

 

[PHV]

Function SendCustList(addr As String, atch As String) As Boolean
SendCustList need 2 mandatory arguments.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lupins46]

I haven't looked at the code except for the error but you have to pass both values in one function call.

SendCustList (rst1!addr, rst!atch)

 

[chainedtodesk]

PHV - i already have that statement in my code, and the error is well before that statement??????

lupins46 - i tried to change the code string to match your style but that wont work, after keying in the ',' and the second 'rst' statement it gives me an error for bad code????

 

[lupins46]

my typo....should be rst2!atch

 

[chainedtodesk]

lupins46 - i did key it as
SendCustList (rst1!addr,rst2!atch) but then the code turns red and the error (expected =) appears????

 

[Axoliien]

You will be better off using early binding to help alleviate memory issues if your program errs for some reason.

What you are looking for is probably an optional value, as exemplfied below. You can call the function with either one or two values.

Example calls

    If Not SendCustList("xxx@yyy.z") Then
        Debug.Print "Error on single value call."
    End If

    If Not SendCustList("xxx@yyy.z", "path.form.document") Then
        Debug.Print "Error on two value call."
    End If

Function SendCustList(ByVal addr As String, Optional ByVal atch As String = "") As Boolean
    Dim objoutlook As Outlook.Application
    Dim objitem As Outlook.MailItem
    Dim opSucc As Boolean
    opSucc = False

    On Error Resume Next
    Set objoutlook = GetObject(, "Outlook.Application")
    If Err.Number = 429 Then 'No open application for reference
        Set objoutlook = New Outlook.Application
        Err = 0
    End If

    If Err.Number <> 0 Then
        GoTo ErrHandler
    End If
    
    On Error GoTo ErrHandler
    ' Create olMailItem
    Set objitem = objoutlook.CreateItem(olMailItem)
    With objitem
        .Subject = "weekly sales flyer."
        .To = addr
        '.CC = "xxx@xxx.xxx"
        '.BCC = "xxx@xxx.xxx"
        .Body = "weekly sales flyer."
        If Nz(atch, "") <> "" Then
            .Attachments.Add atch
        End If
        .Send
        opSucc = True
    End With

ClearMem:
    On Error Resume Next
    If Not objitem Is Nothing Then Set objitem = Nothing
    If Not objoutlook Is Nothing Then Set objoutlook = Nothing
    SendCustList = opSucc
    Exit Function

ErrHandler:
    MsgBox "There was an error with the Outlook Application." & vbCrLf & _
        "The application has terminated." & vbCrLf & _
        "Error " & Err.Number & ": " & Err.Description, _
        vbOKOnly + vbInformation, "Error: Operation failed."
    GoTo ClearMem
End Function

 

[PHV]

Replace this:
SendCustList (rst1!addr,rst2!atch)
By either this:
Call SendCustList(rst1!addr, rst2!atch)
Or this:
SendCustList rst1!addr, rst2!atch

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Axoliien]

You must be sure to do calls correctly as well.

There are a ton of ways to use function calling, which I think is one of the downsides to VB. I think these are all correct...

'Invalid
SomeFunctionCall()

'Valid
SomeFunctionCall

'Valid
Call SomeFunctionCall

'Invalid
AnotherFunctionCall(item1, item2)

'Valid
AnotherFunctionCall item1, item2

'Valid
Call AnotherFunctionCall (item1, item2)

'Valid (retVal is of the function call return type)
retVal = SomeFunctionCall()
retVal = AnotherFunctionCall(item1, item2)

 

[lupins46]

OK; when you are calling a function you must do something with a result.
Your function starts with:
Function SendCustList(addr As String, atch As String) As Boolean

This means that the value returned by this function is the value True or the value False.


So what you can do is

Dim result as boolean

result = SendCustList (rst1!addr, rst!atch)

 

[chainedtodesk]

as always a cure..... thanks to all that responded and helped me..... its greatly appreciated