Consulting

Results 1 to 2 of 2

Thread: command button to send email to recipient not working

  1. #1
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location

    command button to send email to recipient not working

    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?

  2. #2
    VBAX Regular
    Joined
    Apr 2021
    Posts
    45
    Location
    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" 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •