Command Buttons not working with VBA

[poohbear2012]

Hi

I have button on one of my forms to enable users to send an email. I have the following code on the On Click command. Nothing happens when I click on the button on the form and no errors pop up.

Private Sub btnsendemail_Click()
If IsNull(Me.txtcount) Then
MsgBox "There are no appointments to email"
Else
On Error GoTo handdler

Dim mailItem As Outlook.mailItem
Dim outlookApp As Outlook.Application
Set outlookApp = New Outlook.Application
Set mailItem = outlookApp.CreateItem(olMailItem)
mailItem.Subject = "Appointment Reminder"
mailItem.To = Me.GPEMailAddressTextBox
mailItem.CC = "joe.bloggs@me.com"
mailItem.Body = "Thank you for your referral regarding" & Nz(Me.Forename, "") & " " & Nz(Me.Surname, "") & vbnewline & "We have booked an outpatient referral appointment for them to be seen in" & vbnewline
mailItem.Body = "We have booked an outpatient referral appointment for them to be seen in" & Nz(Me.Consultant, "") & " clinic on " & Nz(Me.[1st_Appointment], "") & vbnewline & "at insert time." & vbnewline & "_"
mailItem.Body = "Kind Regards" & vbnewline & "_"
mailItem.Body = "The Appointments Team." & vbnewline & "_"
mailItem.Body = "This message has been automatically generated by the Database System"

End If
handdler:
If Err.Number = 2501 Then
MsgBox "You have cancelled the request", vbInformation
Else
Exit Sub
End If
End Sub

Many Thanks
Trisha Thorpe

 

[tehNellie]

Add a breakpoint at your first IF statement (F9 with the cursor on the text or click on the grey margin at that point). that will at least confirm that clicking the button is triggering the procedure and enable you to step through your code.

My immediate thought is that you're getting past the

on error goto handler

Line, something is wrong in your code which is skipping to the error handling, it's not error 2501 so the procedure is simply exiting as instructed with no feedback as to what or where the problem is.

[edit] As far as I can see the outlook code is fine which might suggest there's an issue with the data in the fields.

[edit again] I fib about the outlook code, you're resetting your mailitem body text on each line so you ultimately end up with the mail body saying:

"This message has been automatically generated by the Database System"

You can use :

mailItem.Body = mailitem.body & "some more text"

Which should append text to your existing contents.