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
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