Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
command Button
Im trying to automatically send an email and im good with everything except I
want it to come up with a command button that when selected it automatically sends the email. Heres my code: Sub Mail_Workbook_2() Dim wb1 As Workbook Dim wb2 As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String 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 be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With 'Make a copy of the file/Open it/Mail it/Delete it 'If you want to change the file name then change only TempFileName TempFilePath = Environ$("temp") & "\" TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy") FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1))) wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr) With wb2 On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
command Button
View Toolbars Forms
create a button and the Assign Macro Dialogbox will appear. Just assign your macro to it. -- Gary''s Student - gsnu200781 "Ewing25" wrote: Im trying to automatically send an email and im good with everything except I want it to come up with a command button that when selected it automatically sends the email. Heres my code: Sub Mail_Workbook_2() Dim wb1 As Workbook Dim wb2 As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String 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 be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With 'Make a copy of the file/Open it/Mail it/Delete it 'If you want to change the file name then change only TempFileName TempFilePath = Environ$("temp") & "\" TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy") FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1))) wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr) With wb2 On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
command Button
Awesome thanks!
"Ewing25" wrote: Im trying to automatically send an email and im good with everything except I want it to come up with a command button that when selected it automatically sends the email. Heres my code: Sub Mail_Workbook_2() Dim wb1 As Workbook Dim wb2 As Workbook Dim TempFilePath As String Dim TempFileName As String Dim FileExtStr As String 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 be no VBA code in the file you send." & vbNewLine & _ "Save the file first as xlsm and then try the macro again.", vbInformation Exit Sub End If End If With Application .ScreenUpdating = False .EnableEvents = False End With 'Make a copy of the file/Open it/Mail it/Delete it 'If you want to change the file name then change only TempFileName TempFilePath = Environ$("temp") & "\" TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy") FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1))) wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr) With wb2 On Error Resume Next .SendMail ", _ "This is the Subject line" On Error GoTo 0 .Close SaveChanges:=False End With 'Delete the file Kill TempFilePath & TempFileName & FileExtStr With Application .ScreenUpdating = True .EnableEvents = True End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wanting to Create A Command Button Command | Excel Programming | |||
VB's Command Button vs Form's Command Button | Excel Programming | |||
Command Button vs Form Button | Excel Programming | |||
Command Button vs Control Button | Excel Programming | |||
Command Button vs Form Button | Excel Programming |