Sending Email & Null Values

[Will_J]

Hi Guys,

Thanks in advance for the help. I'm a novice to this so appreciate all the help I can get.
I've remodeled some code I've got offline with the SendObject command to insert and record emails sent to prospects in my sales process.
The email table is labelled as follows;
EmailID, EmailType, EmailDate, EmailTo, EmailCC, EmailBCC, EmailTitle, EmailOutline, EmailAttachment, EmailSent, EmployeeID.
The customer one is a bit more long winded but for the purpose of this query the field name of the email address in ContactEmail1 and the ID of the Contact table ContactID.

The code is as follows;
Private Sub cmdSendEmail_Click()
On Error GoTo Err_cmdSendEmail_Click

Dim stWhere As String '-- Criteria for DLookup
Dim varTo As Variant '-- Address for SendObject
Dim stText As String '-- E-mail text
Dim RecDate As Variant '-- Rec date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim stEmailID As String '-- The ticket ID from form
Dim stWho As String '-- Reference to tblUsers
Dim EmployeeID As String '-- Person who assigned ticket
Dim strSQL As String '-- Create SQL update statement
Dim errLoop As Error

'-- Combo of names to assign ticket to
stWho = Me.cmdEmailTo
stWhere = "tblContacts.ContactID = " & "'" & stWho & "'"
'-- Looks up email address from TblUsers
varTo = DLookup("[ContactEmail1]", "tblContacts", stWhere)

stSubject = "Remember to assign me to a request!"

stEmailID = Format(Me.EmailID, "00000")
RecDate = Me.EmailDate
'-- Helpdesk employee who assigns ticket
EmployeeID = Me.cmdEmployeeID.Column(1)

stText = Chr$(13) & "Email Reference: " & EmailID & Chr$(13) & _
"This email has been sent to you by: " & EmployeeID & _
Chr$(13) & "Sent On: " & RecDate & Chr$(13) & _
Chr$(13) & "This is an internal reference message"

'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1

'Set the update statement to disable command button
'once e-mail is sent
strSQL = "UPDATE tblEmail " & _
"SET tblEmail.EmailSent = -1 " & _
"Where tblEmail.EmailID = " & Me.EmailID & ";"


On Error GoTo Err_Execute
CurrentDb.Execute strSQL, dbFailOnError
On Error GoTo 0

'Requery checkbox to show checked
'after update statement has ran
'and disable send mail command button
Me.EmailSent.Requery
Me.EmailSent.SetFocus
Me.cmdSendEmail.Enabled = False

Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next


Exit_cmdSendEmail_Click:
Exit Sub

Err_cmdSendEmail_Click:
MsgBox Err.Description
Resume Exit_cmdSendEmail_Click

End Sub

I'm getting a 'Data type mismatch' error occur, which I'm guessing is down to null values being present in the table, which there are, and this can't be helped. Any idea on the code I need to allocate 0s to these records went the command runs or am I off the beaten track?
Also please point out any other areas you think I might have problems with, at the moment I'm just learning the basics.

Thanks again,

Will

[mrojas]

It appears you're not checking for NULL on any of your If statements.
If IsNull(field name)=True then
variable="Left blank"
else
your code goes here
end if