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 - Sub Mail_workbook_Outlook_2()
-
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
-
Dim wb1 As Workbook
-
Dim wb2 As Workbook
-
Dim TempFilePath As String
-
Dim TempFileName As String
-
Dim FileExtStr As String
-
Dim OutApp As Object
-
Dim OutMail As Object
-
-
Set wb1 = ActiveWorkbook
-
If Val(Application.Version) >= 12 Then
-
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
-
MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
-
"be no VBA code in the file you send. Save the" & vbNewLine & _
-
"file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
-
Exit Sub
-
End If
-
End If
-
-
With Application
-
.ScreenUpdating = False
-
.EnableEvents = False
-
End With
-
-
' Make a copy of the file.
-
' If you want to change the file name then change only TempFileName variable.
-
TempFilePath = Environ$("temp") & "\"
-
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
-
FileExtStr = "." & LCase(Right(wb1.Name, _
-
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
-
-
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
-
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
-
-
Set OutApp = CreateObject("Outlook.Application")
-
-
Set OutMail = OutApp.CreateItem(0)
-
-
On Error Resume Next
-
' Change the mail address and subject in the macro before you run this procedure.
-
With OutMail
-
.To = "Todd.Esplund@Entrematic.com"
-
.CC = ""
-
.BCC = ""
-
.Subject = "This is the Subject line"
-
.Body = "Hello World!"
-
.Attachments.Add wb2.FullName
-
' You can add other files by uncommenting the following line.
-
'.Attachments.Add ("C:\test.txt")
-
' In place of the following statement, you can use ".Display" to
-
' display the mail.
-
.Send
-
End With
-
On Error GoTo 0
-
-
wb2.Close SaveChanges:=False
-
-
' Delete the file.
-
Kill TempFilePath & TempFileName & FileExtStr
-
-
Set OutMail = Nothing
-
Set OutApp = Nothing
-
-
With Application
-
.ScreenUpdating = True
-
.EnableEvents = True
-
End With
-
End Sub
-
-
-
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
-
Private Sub CommandButton1_Click()
-
Sub Mail_workbook_Outlook_2()
-
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
-
Dim wb1 As Workbook
-
Dim wb2 As Workbook
-
Dim TempFilePath As String
-
Dim TempFileName As String
-
Dim FileExtStr As String
-
Dim OutApp As Object
-
Dim OutMail As Object
-
-
Set wb1 = ActiveWorkbook
-
If Val(Application.Version) >= 12 Then
-
If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
-
MsgBox "There is VBA code in this xlsx file. There will" & vbNewLine & _
-
"be no VBA code in the file you send. Save the" & vbNewLine & _
-
"file as a macro-enabled (. Xlsm) and then retry the macro.", vbInformation
-
Exit Sub
-
End If
-
End If
-
-
With Application
-
.ScreenUpdating = False
-
.EnableEvents = False
-
End With
-
-
' Make a copy of the file.
-
' If you want to change the file name then change only TempFileName variable.
-
TempFilePath = Environ$("temp") & "\"
-
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
-
FileExtStr = "." & LCase(Right(wb1.Name, _
-
Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
-
-
wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
-
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
-
-
Set OutApp = CreateObject("Outlook.Application")
-
-
Set OutMail = OutApp.CreateItem(0)
-
-
On Error Resume Next
-
' Change the mail address and subject in the macro before you run this procedure.
-
With OutMail
-
.To = "Todd.Esplund@Entrematic.com"
-
.CC = ""
-
.BCC = ""
-
.Subject = "This is the Subject line"
-
.Body = "Hello World!"
-
.Attachments.Add wb2.FullName
-
' You can add other files by uncommenting the following line.
-
'.Attachments.Add ("C:\test.txt")
-
' In place of the following statement, you can use ".Display" to
-
' display the mail.
-
.Send
-
End With
-
On Error GoTo 0
-
-
wb2.Close SaveChanges:=False
-
-
' Delete the file.
-
Kill TempFilePath & TempFileName & FileExtStr
-
-
Set OutMail = Nothing
-
Set OutApp = Nothing
-
-
With Application
-
.ScreenUpdating = True
-
.EnableEvents = True
-
End With
-
End Sub
-
-
End Sub
1 2467
I decided to make the macro have a shortcut key instead which worked out fine. Thanks everyone.
Sign in to post your reply or Sign up for a free account.
Similar topics
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)...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
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
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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...
|
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,...
|
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...
|
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...
|
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,...
|
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...
| |