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

dgreen

Member
Local time
Today, 05:23
Joined
Sep 30, 2018
Messages
397
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 feel free to Remove this Comment

Code:
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
 
Last edited by a moderator:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:23
Joined
Oct 29, 2018
Messages
21,358
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

Immoderate Moderator
Staff member
Local time
Today, 05:23
Joined
Feb 28, 2001
Messages
27,001
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

Super Moderator
Staff member
Local time
Today, 10:23
Joined
Sep 12, 2006
Messages
15,614
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

Code:
      '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
 
Last edited:

Users who are viewing this thread

Top Bottom