none
Command Button Code RRS feed

  • Question

  • Why does this Command Button Code not work to run the Sub mail show below in Module 1?

    Option Explicit
    Private Sub CommandButton1_Click()
    Sub Mail_ActiveSheet()
    End Sub

    Sub Mail_ActiveSheet()
    'Working in Excel 2000-2013
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim Sourcewb As Workbook
        Dim Destwb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim OutApp As Object
        Dim OutMail As Object

        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        Set Sourcewb = ActiveWorkbook

        'Copy the ActiveSheet to a new workbook
        ActiveSheet.Copy
        Set Destwb = ActiveWorkbook

        'Determine the Excel version and file extension/format
        With Destwb
            If Val(Application.Version) < 12 Then
                'You use Excel 97-2003
                FileExtStr = ".xls": FileFormatNum = -4143
            Else
                'You use Excel 2007-2013
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End With

        '    'Change all cells in the worksheet to values if you want
        '    With Destwb.Sheets(1).UsedRange
        '        .Cells.Copy
        '        .Cells.PasteSpecial xlPasteValues
        '        .Cells(1).Select
        '    End With
        '    Application.CutCopyMode = False

        'Save the new workbook/Mail it/Delete it
        TempFilePath = Environ$("temp") & "\"
        TempFileName = "Part of " & Sourcewb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")

        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)

        With Destwb
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            On Error Resume Next
            With OutMail
                .to = "bciepiela@earthlink.net"
                .CC = ""
                .BCC = ""
                .Subject = "This is the Subject line"
                .Body = "Hi there"
                .Attachments.Add Destwb.FullName
                'You can add other files also like this
                '.Attachments.Add ("C:\test.txt")
                .Send   'or use .Display
            End With
            On Error GoTo 0
            .Close savechanges:=False
        End With

        'Delete the file you have send
        Kill TempFilePath & TempFileName & FileExtStr

        Set OutMail = Nothing
        Set OutApp = Nothing

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub

    Saturday, January 18, 2014 3:26 PM

Answers

  • Hi Bob,

    There is no need to use the "Sub" keyword for calling the Mail_ActiveSheet macro.

    Private Sub CommandButton1_Click()
      Mail_ActiveSheet
    End Sub


    Saturday, January 18, 2014 6:31 PM

All replies

  • Hello Bob,

    It looks like you copied the sub from Excel. Do you want to run the code in Excel or Outlook?

    Please make sure that a reference to Excel is added if you are going to run the macro in Outlook due to the fact that the Excel object model is used in the code. Then you need to instantiate an instance  of the Excel Application class. There is no need to create a new instance of Outlook (see CreateObject in the code).

    Did you try to debug?

    Saturday, January 18, 2014 4:29 PM
  • Excel, when i RUN the code it stops here:

    Private Sub CommandButton1_Click()
    Sub Mail_ActiveSheet()

    I have a command button on each sheet to send the report  

    Saturday, January 18, 2014 4:39 PM
  • Hi Bob,

    There is no need to use the "Sub" keyword for calling the Mail_ActiveSheet macro.

    Private Sub CommandButton1_Click()
      Mail_ActiveSheet
    End Sub


    Saturday, January 18, 2014 6:31 PM