Hello,
I have two form control buttons on my spreadsheet (one to print form and one to email form).
One of the buttons has a macro (with VBA code) to first save the sheet as a temp file and then open a new email message in outlook with the sheet attached (with date and time stamp) - and something in there to determine version of excel.
The button function is working...however...when I test this, if I close the email, it seems like the sheet/workbook becomes inactive?! I have to click in another cell to make it active again.
I suspect this has something to do with the VB code to email workbook as attachment, because it only happens if the "email" button is clicked first?!
If I click the print button first, that works fine and I can click on the submit via email button afterwards with no issues.
However, if I click to submit via email first, and then close the email to return to the spreadsheet and then click on the "print" button, the control is selected instead (e.g. button is selected - "format control" menu comes up, and can't be "clicked" to print unless I click in another cell first).
My concern is that users who click the button to submit via email first, might close the email without sending it, to either make a change on their form, or to decide to print instead, and will have trouble clicking on the print button!
Appreciate any help on figuring this out! I've tried locking/unlocking buttons, cells, sheet, text, etc. and nothing seems to prevent this from happening!
Here's the code for the submit to email button:
I have two form control buttons on my spreadsheet (one to print form and one to email form).
One of the buttons has a macro (with VBA code) to first save the sheet as a temp file and then open a new email message in outlook with the sheet attached (with date and time stamp) - and something in there to determine version of excel.
The button function is working...however...when I test this, if I close the email, it seems like the sheet/workbook becomes inactive?! I have to click in another cell to make it active again.
I suspect this has something to do with the VB code to email workbook as attachment, because it only happens if the "email" button is clicked first?!
If I click the print button first, that works fine and I can click on the submit via email button afterwards with no issues.
However, if I click to submit via email first, and then close the email to return to the spreadsheet and then click on the "print" button, the control is selected instead (e.g. button is selected - "format control" menu comes up, and can't be "clicked" to print unless I click in another cell first).
My concern is that users who click the button to submit via email first, might close the email without sending it, to either make a change on their form, or to decide to print instead, and will have trouble clicking on the print button!
Appreciate any help on figuring this out! I've tried locking/unlocking buttons, cells, sheet, text, etc. and nothing seems to prevent this from happening!
Here's the code for the submit to email button:
Code:
Sub Button5_Click()
'Do not forget to change the email ID
'before running this code
Dim OlApp As Object
Dim NewMail As Object
Dim TempFilePath As String
Dim FileExt As String
Dim TempFileName As String
Dim FileFullPath As String
Dim FileFormat As Variant
Dim Wb1 As Workbook
Dim Wb2 As Workbook
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Wb1 = ThisWorkbook
ActiveSheet.Copy
Set Wb2 = ActiveWorkbook
'Below code will get the File Extension and
'the file format which we want to save the copy
'of the workbook with the active sheet.
With Wb2
If Val(Application.Version) < 12 Then
FileExt = ".xls": FileFormat = -4143
Else
Select Case Wb1.FileFormat
Case 51: FileExt = ".xlsx": FileFormat = 51
Case 52:
If .HasVBProject Then
FileExt = ".xlsm": FileFormat = 52
Else
FileExt = ".xlsx": FileFormat = 51
End If
Case 56: FileExt = ".xls": FileFormat = 56
Case Else: FileExt = ".xlsb": FileFormat = 50
End Select
End If
End With
'Save your workbook in your temp folder of your system
'below code gets the full path of the temporary folder
'in your system
TempFilePath = Environ$("temp") & "\"
'Now append a date and time stamp
'in your new file
TempFileName = Wb1.Name & "-" & Format(Now, "dd-mmm-yy h-mm-ss")
'Complete path of the file where it is saved
FileFullPath = TempFilePath & TempFileName & FileExt
'Now save your currect workbook at the above path
Wb2.SaveAs FileFullPath, FileFormat:=FileFormat
'Now open a new mail
Set OlApp = CreateObject("Outlook.Application")
Set NewMail = OlApp.CreateItem(0)
On Error Resume Next
With NewMail
.To = "someone@somewhere.com"
.CC = ""
.BCC = ""
.Subject = "Request Form"
.Body = "Hello, please find request form attached. Thank you."
.Attachments.Add FileFullPath '--- full path of the temp file where it is saved
.Display 'or use .Display to show you the email before sending it.
End With
On Error GoTo 0
'Since mail has been sent with the attachment
'Now close and delete the temp file from the
'temp folder
Wb2.Close SaveChanges:=False
Kill FileFullPath
'set nothing to the objects created
Set NewMail = Nothing
Set OlApp = Nothing
'Now set the application properties back to true
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub