473,405 Members | 2,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,405 software developers and data experts.

Compile Error Expected End Sub excel 2007 command button and code not working togeth

I am using VB to create a code that will save a copy, send it via outlook then delete the copy it saved. I want this to be via command button but am not picky as long as it's as easy as the comman button. The code I have is
Expand|Select|Wrap|Line Numbers
  1. Sub Mail_workbook_Outlook_2()
  2. ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
  3.     Dim wb1 As Workbook
  4.     Dim wb2 As Workbook
  5.     Dim TempFilePath As String
  6.     Dim TempFileName As String
  7.     Dim FileExtStr As String
  8.     Dim OutApp As Object
  9.     Dim OutMail As Object
  10.  
  11.     Set wb1 = ActiveWorkbook
  12.     If Val(Application.Version) >= 12 Then
  13.         If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
  14.             MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
  15.                    "be no VBA code in the file you send. Save the" & vbNewLine & _
  16.                    "file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
  17.             Exit Sub
  18.         End If
  19.     End If
  20.  
  21.     With Application
  22.         .ScreenUpdating = False
  23.         .EnableEvents = False
  24.     End With
  25.  
  26.     ' Make a copy of the file.
  27.     ' If you want to change the file name then change only TempFileName variable.
  28.     TempFilePath = Environ$("temp") & "\"
  29.     TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
  30.     FileExtStr = "." & LCase(Right(wb1.Name, _
  31.                                    Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
  32.  
  33.     wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
  34.     Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
  35.  
  36.     Set OutApp = CreateObject("Outlook.Application")
  37.  
  38.     Set OutMail = OutApp.CreateItem(0)
  39.  
  40.     On Error Resume Next
  41.    ' Change the mail address and subject in the macro before you run this procedure.
  42.     With OutMail
  43.         .To = "Todd.Esplund@Entrematic.com"
  44.         .CC = ""
  45.         .BCC = ""
  46.         .Subject = "This is the Subject line"
  47.         .Body = "Hello World!"
  48.         .Attachments.Add wb2.FullName
  49.         ' You can add other files by uncommenting the following line.
  50.         '.Attachments.Add ("C:\test.txt")
  51.         ' In place of the following statement, you can use ".Display" to
  52.         ' display the mail.
  53.         .Send
  54.     End With
  55.     On Error GoTo 0
  56.  
  57.     wb2.Close SaveChanges:=False
  58.  
  59.     ' Delete the file.
  60.     Kill TempFilePath & TempFileName & FileExtStr
  61.  
  62.     Set OutMail = Nothing
  63.     Set OutApp = Nothing
  64.  
  65.     With Application
  66.         .ScreenUpdating = True
  67.         .EnableEvents = True
  68.     End With
  69. End Sub
  70.  
  71.  
  72. This code works fine. when I run it in VB, I recieve the email and all changes made to the form.  My problem is when I enter the command button and then insert the code there. The code looks like this I get the error code.  I tried taking the seemingly redundant end sub off the bottom. I am at a loss here and very much out of my element
  73. Private Sub CommandButton1_Click()
  74. Sub Mail_workbook_Outlook_2()
  75. ' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
  76.     Dim wb1 As Workbook
  77.     Dim wb2 As Workbook
  78.     Dim TempFilePath As String
  79.     Dim TempFileName As String
  80.     Dim FileExtStr As String
  81.     Dim OutApp As Object
  82.     Dim OutMail As Object
  83.  
  84.     Set wb1 = ActiveWorkbook
  85.     If Val(Application.Version) >= 12 Then
  86.         If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
  87.             MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
  88.                    "be no VBA code in the file you send. Save the" & vbNewLine & _
  89.                    "file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
  90.             Exit Sub
  91.         End If
  92.     End If
  93.  
  94.     With Application
  95.         .ScreenUpdating = False
  96.         .EnableEvents = False
  97.     End With
  98.  
  99.     ' Make a copy of the file.
  100.     ' If you want to change the file name then change only TempFileName variable.
  101.     TempFilePath = Environ$("temp") & "\"
  102.     TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
  103.     FileExtStr = "." & LCase(Right(wb1.Name, _
  104.                                    Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
  105.  
  106.     wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
  107.     Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
  108.  
  109.     Set OutApp = CreateObject("Outlook.Application")
  110.  
  111.     Set OutMail = OutApp.CreateItem(0)
  112.  
  113.     On Error Resume Next
  114.    ' Change the mail address and subject in the macro before you run this procedure.
  115.     With OutMail
  116.         .To = "Todd.Esplund@Entrematic.com"
  117.         .CC = ""
  118.         .BCC = ""
  119.         .Subject = "This is the Subject line"
  120.         .Body = "Hello World!"
  121.         .Attachments.Add wb2.FullName
  122.         ' You can add other files by uncommenting the following line.
  123.         '.Attachments.Add ("C:\test.txt")
  124.         ' In place of the following statement, you can use ".Display" to
  125.         ' display the mail.
  126.         .Send
  127.     End With
  128.     On Error GoTo 0
  129.  
  130.     wb2.Close SaveChanges:=False
  131.  
  132.     ' Delete the file.
  133.     Kill TempFilePath & TempFileName & FileExtStr
  134.  
  135.     Set OutMail = Nothing
  136.     Set OutApp = Nothing
  137.  
  138.     With Application
  139.         .ScreenUpdating = True
  140.         .EnableEvents = True
  141.     End With
  142. End Sub
  143.  
  144. End Sub
Aug 29 '14 #1
1 2467
I decided to make the macro have a shortcut key instead which worked out fine. Thanks everyone.
Aug 30 '14 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Mr. Bungle | last post by:
When importing excel from access I am fully aware that one can import directly into a table. Can you get as specific via code or something to import data from an excel sheet to a FORM (Not Table)...
3
by: wwwursa | last post by:
I am trying to use the Right function in a VB6 program. I have used it used many times before in other programs. When I press the enter key after entering the code line, the word "Right" turns...
5
by: iheartvba | last post by:
Hi Everyone, I keep on getting an error saying Compile Error: Expected Case in my (MS Access) VBA What I was trying to do was to do a DSUM function for a Table where the criteria was coming from...
1
by: kieran04 | last post by:
every time i run this program in VBE i keep getting the message 'Compile Error: expected array' for the two bold lines underneath, please help? Public Function KnotsToKmPerHr(knots As...
5
by: Mike | last post by:
I am calling an event from the on click event of a command button. I cant however figure out how to refrence "which" button was clicked in the first procedure, in the second procedure... In...
2
by: rocky102 | last post by:
I am a novice old timer who enjoys fooling around with excel, I copied this macro from and old computer and wanted to use it one a new one I got. Its supposed to draw a line across a row when...
2
by: falroc | last post by:
I created a Access 2007 database that had a add record command button that worked just fine until I split the database and made Sqlexpress 2005 the backend. Now, when I click on the add record...
4
by: Ezzz | last post by:
I have customised a form to display my own error messages using:- Private Sub Form_Error(DataErr As Integer, Response As Integer) ' If an error occurs because of duplicate data in a required...
1
by: Dorota Prywata | last post by:
Hi, I need help with VBA Macro for Auto-filter to high lite a header plp advice as per below course a I got Compile error: Expected End Sub ? Sub Macro1() Function FilterOn(myCell As Range)...
4
by: Lindsay Bradley | last post by:
I have code built into a form in Access and everytime I try to open the form it gives me a Compile error: Expected array message and then highlights the word Left. I did not create this program and...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.