How to resolve runtime error 94 "Invalid use of null"

[Danick]

This code sends an email using Outlook. The email is in the text box called "CompanyEmail" This works fine when there is an email in that textbox but gives me an runtime error 94 "Invalid use of null" when the field is empty.
Any help would be appreciated.

Private Sub cmdSendemailWork_Click()
If Not IsNull(CompanyEmail) Then

   Dim strEmail As String
  '**create variables for Outlook
  Dim objOutlook As Object
  Dim objEmail As Object
   
  '**gathers information from your form.  this sets the string variable to your fields
  strEmail = Me!CompanyEmail
   
  '***creates an instance of Outlook
  Set objOutlook = CreateObject("Outlook.Application")
  Set objEmail = objOutlook.CreateItem(olMailItem)
   
  '***creates and Displays email
  With objEmail
      .To = strEmail
      .Display
  End With
  Exit Sub
End If

 

[Minty]

Try using

If Len(Me.CompanyEmail & "") > 0 Then ...

And then further down

 strEmail = Me.CompanyEmail

As this will trap a zero length string (which is not Null!) at the start.

Make sure your textbox is called CompanyEmail as well.

 

[Danick]

Thanks Minty.
It was actually the

strEmail = Me!CompanyEmail

that was messing everything up. I looked at the code over and over and missed that "!" instead of a "." Thanks for finding the problem.

I left the first one as is since it seems to be working fine without any error. Any reason to change it to

If Len(Me.CompanyEmail & "") > 0 Then ...

Actually, I took a look at this code in other forms I'm using at for some reason, it's working fine in another form with the "!". So I don't understand why this exact code worked in one form but not in another. But just to be on the safe side, I went ahead and changed the "!" to a "." in the other form as well.