Argument Not Optional when emailing an order

G

grapevine

Registered User.
Local time
Today, 21:49
Joined
Feb 21, 2009
Messages
39
I have an order form and I want users to be able to email selected information from this form to the supplier when they have completed the order entry, I have found some code which I have adapted, but when I click on the command button to run this code I get Argument Not Optional with the name of the module routine highlighted. Does this mean my code below is wrong or the code in the module does not see the information (I think) I am collecting, or is this name not supposed to be the name of the module routine.
This is the first time I have ever tried to email information from Access via code so any help gratefully received.


Private Sub EmailJob_Click()
Dim strMsgAddress As String
Dim strSubject As String
Dim strBody As String
'code to send email with job details
DoCmd.RunCommand acCmdSaveRecord
strMsgAddress = Forms!frmOrders.me.ContEmail
strSubject = "Purchase Order Number : CC " & Forms!frmOrders.me.OrderID
strBody = "Please arrange for delivery of the following order." _
& "The purchase order number that must be quoted on all correspondence is: " & vbCrLf & "Purchase Order Number: CC" & Forms!frmOrders.me.OrderID& _
& vbCrLf & "If you have any queries please contact " & Forms!frmOrders.me.CoContact& _
& "at Darrow Road on" _
& Forms!frmOrders.me.CoTelephone
If Not IsNull(Forms!frmOrders.me.ItemDescription) And Not IsNull(Forms!frmOrders.me.ItemQty) And Not IsNull(Forms!frmOrders.me.LineTotalExcVat) Then strBody = strBody & vbCrLf & Forms!frmOrders.me.ItemDescription & " " & Forms!frmOrders.me.ItemQty & " " & Forms!frmOrders.me.LineTotalExcVat _
CreateOrdersEmail strMsgAddress, strSubject, strBody
End Sub
 
CreateOrdersEmail is the name of the routine in the module which sends the email.

Sub CreateOrdersEmail(strMsgAddress As Variant, strSubject As Variant, strBody As Variant, blnDisplay As Boolean)
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
On Error GoTo EH
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
If Not IsNull(strMsgAddress) Then
.Recipients.Add strMsgAddress
End If
.Subject = strSubject
.Body = strBody
If blnDisplay = True Then
.Display
Else
.Send
End If
End With
Set objOutlook = Nothing
Exit Sub
EH:
Err.Clear
Set objOutlook = Nothing
End Sub
 
First of all this syntax Forms!frmOrders.me.ContEmail is incorrect and you have lots of these sort of syntax errors in your code. Have a look at how to reference forms correctly:

http://www.mvps.org/access/forms/frm0031.htm

The error is coming from the CreateOrdersEmail function. It requires 4 parameters but you have only given it 3.
 
your CreateOrdersEmail sub require 4 arguments: strMsgAddress, strSubject, strBody, blnDisplay
none of them is optional.
you send only 3 of the required arguments - CreateOrdersEmail strMsgAddress, strSubject, strBody

this what cause the Argument Not Optional error
 
Thank you for your speedy replies, much appreciated. I am checking my code to get the correct syntax, but meanwhile could you please explain what bldDisplay does. It was already in the code which I found. Will it be alright to just delete it from the module code or will that cause me other problems. Many thanks
 
Run the code with a blnDisplay value of True then run it with False and see the differences yourself.
 
I am really sorry to sound an idiot, but I dont know what to put into the first set of code to pass blnDisplay as a string so that the module will run.
 
It is not a string, it is a boolean value of TRUE or FALSE.
 
I am having real problems trying to sort out the syntax for the coding, I thought I have rewritten the code to reflect the guidance given, but I keep getting "type declaration character does not match the declared data type" so something is obviously still incorrect Could you just confirm the terminology found on the link - by control would this mean a text box containing a calculated field?
Many thanks
 
A control could be a textbox, combo box, listbox, command button, label etc. Nothing to do with whether it's calculated or not.
 
true:
CreateOrdersEmail strMsgAddress, strSubject, strBody, true

false:
CreateOrdersEmail strMsgAddress, strSubject, strBody, false


my guess it will either show outlook before sending the mail (so you can edit it) or silency send the mail
 
Thank you VBAInet for clarifying that, I have managed to eliminate one error, so I will keep working through each control in turn to try and get the correct syntax.

Thank you also smig for clarifying how to pass the information to the module - easy when you know how!

Thank you both very much for your speedy replies, your help and assistance has been most useful.
Hopefully, I should be able to email my order in a short while.
 
I hope I have got the syntax correct, but to clarify all the fields listed at the top are in a frmOrders with only one calculated field POnum and the fields in the ISNull statement are in a subform called frmOrdersItem, the last field being a calculated field. I am assuming that the fields drawing data from the form are all recordsources or have I made a fundamental error as I am now getting a runtime error 2046 - The command or action save record isnt available now. I am obviously missing something very straightforward, which I cannot see. - could I have a little more help please. Many thanksPrivate Sub EmailJob_Click()Dim strMsgAddress As StringDim strSubject As StringDim strBody As String'code to send email with job detailsDoCmd.RunCommand acCmdSaveRecordstrMsgAddress = Me.ContEmailstrSubject = "Purchase Order Number : CC " & Me.OrderIDstrBody = "Please arrange for delivery of the following order." _& "The purchase order number that must be quoted on all correspondence is: " & vbCrLf & "Purchase Order Number: " & Me!PONum & vbCrLf & "If you have any queries please contact " & Me.CoContact _& "at Crown Chickens on" _& Me.CoTelephoneIf Not IsNull(Me!Ordersitem.Form.ItemDescription) And Not IsNull(Me!Ordersitem.Form.ItemQty) And Not IsNull(Me!Ordersitem.Form!LineTotalExcVat) Then strBody = strBody & vbCrLf & Me!Ordersitem.Form.ItemDescription & " " & Me!Ordersitem.Form.ItemQty & " " & Me!Ordersitem.Form!LineTotalExcVatLineTotalExcVat _CreateOrdersEmail strMsgAddress, strSubject, strBody, TrueEnd Sub
 
I have just realised that the code did not come out very clearly so I have posted my amended code again. Sorry about that

Private Sub EmailJob_Click()
Dim strMsgAddress As String
Dim strSubject As String
Dim strBody As String
'code to send email with job details
DoCmd.RunCommand acCmdSaveRecord
strMsgAddress = Me.ContEmail
strSubject = "Purchase Order Number : CC " & Me.OrderID
strBody = "Please arrange for delivery of the following order." _
& "The purchase order number that must be quoted on all correspondence is: " & vbCrLf & "Purchase Order Number: " & Me!PONum & vbCrLf & "If you have any queries please contact " & Me.CoContact _
& "at Crown Chickens on" _
& Me.CoTelephone
If Not IsNull(Me!Ordersitem.Form.ItemDescription) And Not IsNull(Me!Ordersitem.Form.ItemQty) And Not IsNull(Me!Ordersitem.Form!LineTotalExcVat) Then strBody = strBody & vbCrLf & Me!Ordersitem.Form.ItemDescription & " " & Me!Ordersitem.Form.ItemQty & " " & Me!Ordersitem.Form!LineTotalExcVatLineTotalExcVat _
CreateOrdersEmail strMsgAddress, strSubject, strBody, True
 
As always it is the idiot in the driving seat who causes the problem. I have now managed to get the code working to send the order details to email. The correct information was sent and all in the right places - I could not have done it without your help, so thank you very much.

I do however have one more problem. The code justs sends the first item in the order.

I am presuming I need a line of code to loop through all the items for that order, but I do not know how to do this. Could I ask for this one final piece of advice.

My working codes finally ended up as follows

Private Sub EmailJob_Click()
Dim strMsgAddress As String
Dim strSubject As String
Dim strBody As String
'code to send email with job details
'DoCmd.RunCommand acCmdSaveRecord
strMsgAddress = Me.ContEmail
strSubject = "Purchase Order Number : CC " & Me.OrderID
strBody = "Please arrange for delivery of the following order." & "The purchase order number that must be quoted on all correspondence is: " & vbCrLf & "Purchase Order Number: " & Me!PONum & vbCrLf & "If you have any queries please contact " & Me.CoContact & "at Crown Chickens on" & Me.CoTelephone _
If Not IsNull(Me!frmtblOrdersItem.Form.ItemDescription) And Not IsNull(Me!frmtblOrdersItem.Form.ItemQty) And Not IsNull(Me!frmtblOrdersItem.Form!LineTotalExcVat) _
Then strBody = strBody & vbCrLf & Me!frmtblOrdersItem.Form.ItemDescription & " " & Me!frmtblOrdersItem.Form.ItemQty & " " & Me!frmtblOrdersItem.Form!LineTotalExcVat
CreateOrdersEmail strMsgAddress, strSubject, strBody, True
End Sub
 
Something like this. Replace this:
Code: 
If Not IsNull(Me!frmtblOrdersItem.Form.ItemDescription) And Not  IsNull(Me!frmtblOrdersItem.Form.ItemQty) And Not  IsNull(Me!frmtblOrdersItem.Form!LineTotalExcVat) _
Then strBody = strBody & vbCrLf &  Me!frmtblOrdersItem.Form.ItemDescription & " " &  Me!frmtblOrdersItem.Form.ItemQty & " " &  Me!frmtblOrdersItem.Form!LineTotalExcVat
With this:
Code: 
Dim rst as DAO.Recordset

set rst = Me!frmtblOrdersItem.Form.RecordsetClone

With rst
    Do While Not .EOF
        If Not IsNull(!ItemDescription & !ItemQty & !LineTotalExcVat) Then
            strBody = strBody & vbCrLf & !ItemDescription & " " & !ItemQty & " " & !LineTotalExcVat
        End If
        .movenext
    Loop
End With

set rst = Nothing
You can move that Dim line with the rest of your Dim statements.

I remember you mentioned you have a calculated control, if that calculated control is one of the fields you're trying to put together then you need to perform the calculation in the record source of your subform.
 
Last edited:
Thank you so much, it works like a dream. I have understood what you and smig have told me, so it has been a very useful learning exercise.

You are both stars, I cannot thank you enough.

For those that follow this thread in the future looking for help and advice, I have included the final code below. It is not perfect, I need to improve the layout a bit, bit the working part in there.

Private Sub EmailJob_Click()
Dim strMsgAddress As String
Dim strSubject As String
Dim strBody As String
Dim rst As DAO.Recordset

'code to send email with job details
'DoCmd.RunCommand acCmdSaveRecord
strMsgAddress = Me.ContEmail
strSubject = "Purchase Order Number : CC " & Me.OrderID
strBody = "Please arrange for delivery of the following order." & "The purchase order number that must be quoted on all correspondence is: " & vbCrLf & "Purchase Order Number: " & Me!PONum & vbCrLf & "If you have any queries please contact " & Me.CoContact & "at Crown Chickens on" & Me.CoTelephone _
Set rst = Me!frmtblOrdersItem.Form.RecordsetClone
With rst
Do While Not .EOF
If Not IsNull(!ItemDescription & !ItemQty & !LineTotalExcVat) Then
strBody = strBody & vbCrLf & !ItemDescription & " " & !ItemQty & " " & !LineTotalExcVat
End If
.MoveNext
Loop
End With
Set rst = Nothing
CreateOrdersEmail strMsgAddress, strSubject, strBody, True
End Sub
 
You must log in or register to reply here.

Similar threads

N
Solved Receiving Emails in Access Database using CDO 2000 library
Replies
9
Views
1,135
sonic8
S
E
SendKeys "%{S}" Not Working anymore?
Replies
7
Views
893
Gasman
Gasman
mike60smart
Error 2501
2
Replies
29
Views
3,306
mike60smart
mike60smart
D
Default Signature new outlook current user
Replies
17
Views
1,550
GPGeorge
GPGeorge
C
Join key of '***' not in recordset
Replies
9
Views
857
ChrisLeicester
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom