Hi Forum, access 2000
I found an email database on the forum and got it to work for me but I have to close the database and reopen to send a 2nd email
Here is the full code which includes the original.
I have yet to edit out any surplus code.
Can anyone spot where I need to alter the code to allow more then one 'click" of the button.
Even if you select a new record, the button won't work.
Only works Once when Database is opened.
I found an email database on the forum and got it to work for me but I have to close the database and reopen to send a 2nd email
Here is the full code which includes the original.
I have yet to edit out any surplus code.
Can anyone spot where I need to alter the code to allow more then one 'click" of the button.
Even if you select a new record, the button won't work.
Only works Once when Database is opened.
Code:
Private Sub CmdEmailPointsBalance_Click()
Dim varTo As Variant 'Email Address
Dim stText As String 'Email Text
Dim stSubject As String 'Email Subject Line
Dim strSQL As String 'SQL String
Dim PointsAvailable As Integer 'Available Club Points
Dim MembID As String 'Member ID
Dim errLoop As Error
MembID = Me.MemberID 'set forms current Member ID to be variable value
PointsAvailable = Me.txtPointsBalance 'Set Forms Points Available to Use as variable value
On Error GoTo Err_CmdEmailPointsBalance_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 stTicketID As String '-- The ticket ID from form
' Dim stWho As String '-- Reference to tblUsers
' Dim stHelpDesk 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.cboAssignee
' stWhere = "tblUsers.strUserID = " & "'" & stWho & "'"
'-- Looks up email address from TBLACCDET
varTo = DLookup("[ADEmail]", "TBLACCDET", "[ADPK]=" & MembID)
stSubject = ":: Member Club Points Balance ::"
' stTicketID = Format(Me.txtTicketID, "00000")
' RecDate = Me.txtDateReceived
'-- Helpdesk employee who assigns ticket
' strHelpDesk = Me.cboReceivedBy.Column(1)
stText = "Your Club Points Balance is " & PointsAvailable & ". " & Chr$(13) & Chr$(13) & _
"This is an automated message. Please do not respond to this e-mail."
'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 tblHelpDeskTickets SET tblHelpDeskTickets.ysnTicketAssigned = -1 " & _
' "Where tblHelpDeskTickets.lngTicketID = " & Me.txtTicketID & ";"
' 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.chkTicketAssigned.Requery
' Me.chkTicketAssigned.SetFocus
' Me.cmdMailTicket.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_CmdEmailPointsBalance_Click:
Exit Sub
Err_CmdEmailPointsBalance_Click:
MsgBox Err.Description
Resume Exit_CmdEmailPointsBalance_Click
End Sub
