Hi Guys,
Thanks in advance for your help with this. I've modified some code online and am by no means a programmer, but dabbled in the attempt.
I am getting a parameter value error, which from my research means their is a unassigned null value. I have input some code for the only var lookup statement I believe will return a null value however am still getting an error.
Also, as I say, I'm new and there may well be some other errors. If some eagle eye would be kind enough to look at what I'm doing and give me some advice, for something that may crop up after having variable matter sorted it'd be hugely appreciated.
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)
If IsNull(ContactEmail1) = True Then
variable = "Left blank"
End If
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
Thanks in advance for your help with this. I've modified some code online and am by no means a programmer, but dabbled in the attempt.
I am getting a parameter value error, which from my research means their is a unassigned null value. I have input some code for the only var lookup statement I believe will return a null value however am still getting an error.
Also, as I say, I'm new and there may well be some other errors. If some eagle eye would be kind enough to look at what I'm doing and give me some advice, for something that may crop up after having variable matter sorted it'd be hugely appreciated.
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)
If IsNull(ContactEmail1) = True Then
variable = "Left blank"
End If
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