Sendmail form a FORM error ... HELPP please :( !!

[tidz123]

I wanted users to enter an email address they wanted to email the worksheet to, therefore I created a form and for the send button I had the following code :

Private Sub CommandButton1_Click()
Dim wb As Workbook
Set wb = ActiveWorkbook

If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

On Error Resume Next
wb.SendMail Array(TextBox1, TextBox2), _
"Updated Weekly Sheet"
On Error GoTo 0
End Sub


The problem is that once you click send, the worksheet is sent to the email addresses but

1. The form doesn't hide

2. If I click the cancel or 'x' button, the form hides but the an error message pops up saying 'Path/File access error'. If i click OK, there is another error message behind saying "Run-time error '75': Could not find the specified object". Once I click debug it shows there is a problem with UserForm1 which is the form the send button is on. When i try clicking the UserForm1 it displays the same error message it displayed at first 'Path/File access error' and it doesn't open the form.
When i close and open the worksheet again, the form would work the first time as it sends the message but the same problem will occur if I try the second time round.

Does anyone know a solution to this? Or another way round letting the users enter the email addresses they want to send the worksheet to?

All help appreciated

 

[sijpie]

Yes, the easiest is to copy TextBox1.vale and Textbox2.value to two variables you declare. Then you unloiad the form, and only then send the email

Private Sub CommandButton1_Click()
Dim wb As Workbook
dim EMaddress as string, EMSubject as string
Set wb = ActiveWorkbook

If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If

   EMaddress = Textbox1.value
   EMsubject = Textbox2.value
   unload me
On Error Resume Next
wb.SendMail Array(TextBox1, TextBox2), _
"Updated Weekly Sheet"
On Error GoTo 0
End Sub

 

[tidz123]

hi there,
thanks for the reply

one of the problems is solved; the form unloads

the other problem still remains though. the email is sent the first time you use the form but when you try and click the macro button to display the form the second time, its showing the same error messages as before and when I click open visual basic to see the form, it says 'Path/File access Error'...
Any idea what's wrong ?

i tried making a new form as i thought the form might be corrupt but the same thing is happening with the second form aswell, works fine the first time but stops the second time.

Help, so frustrated

 

[sijpie]

I see I made an error. Perhaps you already caught it:

wb.SendMail Array(EMaddress, EMsubject), _

 

[tidz123]

yeah i caught that
thanks..

do you by any chance know why the form doesn't work after the first time?

 

[sijpie]

No.
Th error may be in another sectrion of your vba. Something not being unloaded. Some object you set but did not reset to nothing on finishing with it.
What happens if you comment out this commadbutton code other than the unload me line. And then try to "send" the email a second time?