Tuesday, October 07, 2008   
  Search   
 
Register  Login  
Forums  
     Minimize  

Welcome to MeraWindows forums.

Thank you for being at the Microsoft Windows Community Site. You may have to register before posting in forums. It's absolutely free. After registering, you can get all the benefits available to our registered members, you can access our Downloads section, you can participate in contests, etc. You can post in forums in English as well as in Hindi, in fact we encourage you to use Hindi in your posts. If you have any problem with registration or login, please contact us.

     
  


 
  Microsoft Windows Forums  Microsoft Offic...  Microsoft Excel  Email button in excel
Previous Previous
 
Next Next
New Post 5/17/2008 5:56 PM
User is offline jasfoster
1 posts
Member


Email button in excel 

I have created an email button in excel with the code below. I want to workbook to close after the button is clicked and the e-mail is sent. What additional code do I need to add to make this happen?

Sub Mail_ActiveSheet()>>
'Working in 97-2007>>
    Dim FileExtStr As String>>
    Dim FileFormatNum As Long>>
    Dim Sourcewb As Workbook>>
    Dim Destwb As Workbook>>
    Dim TempFilePath As String>>
    Dim TempFileName As String>>
 >>
    With Application>>
        .ScreenUpdating = False>>
        .EnableEvents = False>>
    End With>>
 >>
    Set Sourcewb = ActiveWorkbook>>
 >>
    'Copy the sheet 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>>
            'We exit the sub when your answer is NO in the security dialog that you only>>
            'see  when you copy a sheet from a xlsm file with macro's disabled.>>
            If Sourcewb.Name = .Name Then>>
                With Application>>
                    .ScreenUpdating = True>>
                    .EnableEvents = True>>
                End With>>
                MsgBox "Your answer is NO in the security dialog">>
                Exit Sub>>
            Else>>
                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 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")>>
 >>
    With Destwb>>
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum>>
        On Error Resume Next>>
        .SendMail "ron@debruin.nl", _>>
                  "This is the Subject line">>
        On Error GoTo 0>>
        .Close SaveChanges:=False>>
    End With>>
 >>
    'Delete the file you have send>>
    Kill TempFilePath & TempFileName & FileExtStr>>
 >>
    With Application>>
        .ScreenUpdating = True>>
        .EnableEvents = True>>
    End With>>
End Sub>>
 
Previous Previous
 
Next Next
  Microsoft Windows Forums  Microsoft Offic...  Microsoft Excel  Email button in excel
   Get Your Own E-Mail Account @MeraWindows.com Minimize  
New Page 1 New Page 1
Show your cool quotient with @merawindows.com email account