Email is Null but VBA code errors (runtime erro 94 - invalid use of null)

[dgreen]

The below code works when I have a record with an Email_Address value. It fails when the value is null / blank. I've tried variations of If Not IsNull, IsBlank, etc....

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please read this for further information:-

Please use Code Tags when posting VBA Code

To make your code easier to read, please use the Code tag around your code, this will ensure that any code you copy and paste from your DB retains it's formatting making it easier to read and follow; Firstly click on the Code button, that's the button at the top of the posting window with the...

www.access-programmers.co.uk

Please feel free to Remove this Comment

Private Sub Command9_Click()
    'https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook
    Dim OApp As Object, OMail As Object, Signature As String, Notes As String, Email As String

    '***creates an instance of Outlook
    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)
   
    '**gathers information from your form.  this sets the string variable to your fields
    Email = Me.[Email_Address]
   
    If Not IsNull(Email) Then
        With OMail
             .Display
         End With
        
         Signature = OMail.HTMLBody
        
         '***creates and sends email
         With OMail
             .To = Email
             .HTMLBody = " " & vbNewLine & Signature
             '.Send
         End With
        
         Set OMail = Nothing
         Set OApp = Nothing
    End If
End Sub

 

[theDBguy]

If Me.[Email_Address] could be Null, then either wrap it around with theNz() function or change your Email variable declaration from String to Variant.

 

[The_Doc_Man]

Agree with theDBguy, with the added comment that not all variables allow nulls, so a test for null might sometimes fail. BUT variants allow nulls so an IsNull test would work. AND if you use the NZ(x,default) function you would guarantee a usable test.

 

[gemma-the-husky]

Do you have this declaration at the top of your code module? You need to have this. It forces you to declare the name and type of any variable you use.

Option Explicit

In the editor, use menu Tools, Oprions, Editor, and put a tick in Require Variable Declaration. This will add Option Explicit to any module you create.

In your code you have this.

Email = Me.[Email_Address]
If Not IsNull(Email) Then

Now if Email is a string, then if [Email_address] is null, the FIRST assignment Email = Me.[Email_Address] will fail, as a string cannot be null. It can be a "", a zero-length string, but that isn't null. If you aren't declaring variables, then Email will actually be declared implicitly as an object, but this shouldn't be the way you work. If you don't declare variables, then typos can cause you to use the wrong variables, and programmes don't work in the way you expect. Allowing variables that ought be typed to instead be created as objects isn't advisable.

What you really want at the top is this sort of thing

      'make sure you do have an email address first
if nz(Me.[Email_Address],"") = "" then
     msgbox "There is no email address entered."
     'you could just exit the sub here, as you know you can't produce an email
else
    'only now do you need to create an outlook instance
    '***creates an instance of Outlook
    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)

    '**gathers information from your form.  this sets the string variable to your fields
   'you probably don't even need email declaring - you could just use [email_address], but maybe you have a specific reason to save it as a string.
  'and now you don't need to test whether the Email_Address has a value, as you know it does, so simply
    Email = Me.[Email_Address]
    ...
    ...
end if