Results 1 to 4 of 4

Getting Expected End Sub error.. No Reason!

Threaded View

  1. #1
    Registered User
    Join Date
    04-15-2014
    Location
    hell
    MS-Off Ver
    Excel 2007
    Posts
    16

    Getting Expected End Sub error.. No Reason!

    Hi,

    I am using one of Ron De Bruins useful Excel VBAs to send a PDF Email from Excel. For some reason, I am getting the "Expected End Sub" error when the End Sub line is present. I do not know what is wrong but whatever code I use, I am getting this error. Here is the code:
    Private Sub CommandButton2_Click()
    
    Sub Mail_Range()
    'Working in Excel 2000-2013
    'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
        Dim Source As Range
        Dim Dest As Workbook
        Dim wb As Workbook
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim OutApp As Object
        Dim OutMail As Object
    
        Set Source = Nothing
        On Error Resume Next
        Set Source = Range("A1:K50").SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
    
        If Source Is Nothing Then
            MsgBox "The source is not a range or the sheet is protected, please correct and try again.", vbOKOnly
            Exit Sub
        End If
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set wb = ActiveWorkbook
        Set Dest = Workbooks.Add(xlWBATWorksheet)
    
        Source.Copy
        With Dest.Sheets(1)
            .Cells(1).PasteSpecial Paste:=8
            .Cells(1).PasteSpecial Paste:=xlPasteValues
            .Cells(1).PasteSpecial Paste:=xlPasteFormats
            .Cells(1).Select
            Application.CutCopyMode = False
        End With
    
        TempFilePath = Environ$("temp") & "\"
        TempFileName = "Selection of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007-2013
            FileExtStr = ".xlsx": FileFormatNum = 51
        End If
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        With Dest
            .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
            On Error Resume Next
            With OutMail
                .to = "[email protected]"
                .CC = ""
                .BCC = ""
                .Subject = "This is the Subject line"
                .Body = "Hi there"
                .Attachments.Add Dest.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
    
        Kill TempFilePath & TempFileName & FileExtStr
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    Can Someone help?
    Last edited by PatCell; 04-22-2014 at 12:46 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] #Ref error cant work out the reason
    By Tristanfrontline in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-16-2013, 02:02 AM
  2. [SOLVED] #REF! error.... for no apparent reason
    By singer.joseph in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 02-25-2013, 06:44 AM
  3. CIRCULAR Error for no good reason
    By TeaNicola in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2012, 07:19 PM
  4. Syntax Error and Compile Error: Expected Line Number or Label...
    By AnthonyWB in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-31-2011, 09:59 AM
  5. [SOLVED] Help with this error Unknown reason for error with Range().Select
    By Brad Sumner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1