I want the database sheet to be emailed as an attachment not the whole workbook.

Private Sub cmdEmail_Click()
    'Declare Outlook Variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object
    'Open the Outlook Application and Start a new mail
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon
    With OLMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .body = ""
        .Attachments.Add ActiveWorkbook.Sheets("Database")
        .Display
        ' .Send
    End With
    
    'Clearing Memory
    
    Set OLMail = Nothing
    Set OLApp = Nothing
End Sub

Error is saying I cannot save this workbook name to choose another name or choose another workbook

CodePudding user response：

Example

Private Sub cmdEmail_Click()
    'Declare Outlook Variables
    Dim OLApp As Outlook.Application
    Dim OLMail As Object
    'Open the Outlook Application and Start a new mail
    Set OLApp = New Outlook.Application
    Set OLMail = OLApp.CreateItem(0)
    OLApp.Session.Logon
    With OLMail
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .body = ""
        Dim ws As Worksheet
        Set ws = ActiveWorkbook.Sheets("Database")
        ws.Copy
        Dim wb As Workbook
        Set wb = ActiveWorkbook
        wb.SaveAs "E:\Temp\Database.xlsx" ' Change Path
        .Attachments.Add wb.FullName
        .Display
        ' .Send
    End With
    
    'Clearing Memory
    
    Set OLMail = Nothing
    Set OLApp = Nothing
End Sub

CodePudding user response：

Error its saying I can not save this workbook name to choose another name. or choose another workbook