command button to send email to recipient not working

[Ray707]

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?

[Ray707]

I got it working.

Basically in the below part of the code I had it referenced to cell G3, which had an email address in. I had two workbooks open at the same time when trying to run the code and I guess that confuses the code because it was unsure which workbook it had to go into cell G3 and collect the email address. I closed the other workbook and ran the command button successfully.

.To = "xxxxxxxxxxxx"