invalid use of null

[steve111]

hi , this is the code that I use to send an e-mail

but I think because there is no e-mail address in the form I get the error
"invalid use of null"

what I would like it to say is
" please enter customer e-mail address in customer form"
any help apppricated

steve

Private Sub cmdEmail_Click()
 On Error GoTo Err_Handler
 
    Dim strTo As String
    Dim strSubject As String
    Dim strMessageText As String
    
 
 
    Me.Dirty = False
    
    strTo = Me.[E-Mail address]
    strSubject = "Invoice Number " & Me.Invoicenumber
    strMessageText = Me.CustomerName & ":" & _
        vbNewLine & vbNewLine & _
        "Your latest invoice is attached." & _
        vbNewLine & vbNewLine & _
        "Customer Accounts Department, Widget Supply Company"
 
    DoCmd.SendObject ObjectType:=acSendReport, _
        ObjectName:="Invoice report", _
        OutputFormat:=acFormatPDF, _
        To:=strTo, _
        Subject:=strSubject, _
        MESSAGETEXT:=strMessageText, _
        EditMessage:=True
Exit_Here:
    Exit Sub
Err_Handler:
    MsgBox Err.Description
    Resume Exit_Here
     
 End Sub

 

[pbaldy]

You get an error because a string variable can't hold a Null. Test that control before that point and put your message box there, and exit the sub.

 

[CJ_London]

try

 if isnull(Me.[E-Mail address]) then
     msgbox "no email address"
 else
     strTo = Me.[E-Mail address]
    ....
     ....
     ....
     ....
 end if

or modify your error handler code - although you need to be sure what is generating the error to ensure you are displaying the correct message

 Err_Handler:
    msgbox "no email address"
     'MsgBox Err.Description
     Resume Exit_Here

 

[steve111]

hi

i managed to get this code from the internet
I have no idea about VBA so I would not know where to start on how to test it
could you please advise how i should do that

thanks
steve

 

[steve111]

sorry cj i did not see your post i will try it now

 

[steve111]

hi
thank you
this is what it made it work

rivate Sub cmdEmail_Click()
On Error GoTo Err_Handler

Dim strTo As String
Dim strSubject As String
Dim strMessageText As String

If IsNull(Me.[E-Mail address]) Then
MsgBox "no email address"
Exit Sub

Else


Me.Dirty = False

strTo = Me.[E-Mail address]
strSubject = "Invoice Number " & Me.Invoicenumber
strMessageText = Me.CustomerName & ":" & _
vbNewLine & vbNewLine & _
"Your latest invoice is attached." & _
vbNewLine & vbNewLine & _
"Customer Accounts Department, Widget Supply Company"
End If
DoCmd.SendObject ObjectType:=acSendReport, _
ObjectName:="Invoice report", _
OutputFormat:=acFormatPDF, _
To:=strTo, _
Subject:=strSubject, _
MESSAGETEXT:=strMessageText, _
EditMessage:=True
Exit_Here:
Exit Sub



Err_Handler:
MsgBox Err.Description
Resume Exit_Here

End Sub

steve

 

[steve111]

could the code be modified to now open the customer form and find the " customer name" before it exit sub so I can put the e-mail address in

steve

 

[CJ_London]

not sure what you mean - the form is already open because you are using Me.[E-Mail address]

 

[steve111]

hi

its not I have a subform called customers that comes from a query

I use this to get the address =[Customers].[Form]![E-Mail address]
and th name of that text box in E-MAIL address

steve

 

[CJ_London]

still don't understand, please explain again what you actually have. Include screenshots if it helps - and please, make sure you are consistent with names and terminology e.g.

open the customer form and find the " customer name" before it exit sub so I can put the e-mail address in

makes no sense to me

From what you are describing you could just as easily have used

strTo =[Customers].[Form]![E-Mail address]

But it is still on the same form, and if you want the customer name that would be

=[Customers].[Form]![Customer Name]

 

[steve111]

ok ,

I am working in the form called invoice
this is where I will create the reports
1. the invoice
2 the invoice delivery note
3 the certificate of conformity


I have 3 buttons (vba)

1 to show the invoice
1 to send the 3 reports to a file
and the one we are working on now to send the e-mail

the main form in called orders , this has the customer name in it and the order number

the 2 subforms are called customers which is a query and invoice details
the form called customers only show me what orders to invoice.

when I select the order number from a drop down box it fetches the orders for that order number
I then fill in the invoice details in the invoice detail form

the 3 reports will have name , address, shipping details , etc coming from a query called invoice reports . this has the tables, customer, orders, order details, invoice details , shipping information and banking details

so if anything is missing from the customer table when we run the reports I want to tell the user this is missing

I have changed what you said to strTo =[Customers].[Form]![E-Mail address]
and all my other forms to suit thank you
learning a lot
steve