Hi all,
I have a command button in the first worksheet of my excel file that should email that first worksheet to a recipient when the button is pressed. The code is not working and I am a bit stuck as to the reason why.
I have used this type of code before and it's worked but when I press this command button, it bizarrely creates a new file called 'book1' with a copy of that worksheet in it. Nothing gets emailed out. The aim is to email the worksheet to the recipient automatically; no new file should be created at all, the email should just fly out.
Here is the code:
Private Sub CommandButton1_Click()
'Update 20131209
Dim wb1 As Workbook, wb2 As Workbook
Dim sFilePath As String, sFileName As String
Dim iFormat As Integer
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
Set wb1 = Application.ActiveWorkbook
ActiveSheet.Copy
Set wb2 = Application.ActiveWorkbook
sFilePath = Environ$("temp")
sFileName = sFilePath & "\ " & wb1.Name
iFormat = wb1.FileFormat
wb2.SaveAs sFileName, iFormat
wb2.Close
With CreateObject("Outlook.Application").createItem(0)
.To = "xxxxxxxxxxxx"
.CC = ""
.BCC = ""
.Subject = "Excel sheet"
.Body = "Hello" & vbLf & vbLf & "Please find spreadsheet attached." & vbLf & vbLf & "Regards" & vbLf & "Ray"
.Attachments.Add sFileName
.Send
End With
Kill sFileName
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
The command button is an activeX control command button and I have inserted the code in Microsoft Excel Objects, not a Module.
Is anyone able to help me please?