Consulting

Results 1 to 4 of 4

Thread: Attach an excel file to outlook email without sending it

  1. #1
    VBAX Regular
    Joined
    Jun 2018
    Posts
    9
    Location

    Attach an excel file to outlook email without sending it

    Hi

    Please assist

    I want to copy an excel sheet from workbook a to a new workbook(workbook b) and send that workbook b to people using outlook. However I want to type new comments in the body of the email and manually press the send button.

    Reason:
    Emailing list of people change as well as instructions.

  2. #2
    VBAX Mentor Logit's Avatar
    Joined
    Sep 2016
    Posts
    328
    Location
    .

    Copy paste sheet from another workbook to the workbook being email :

    Option Explicit
    
    
    Sub Add_Bridge_1()
        Dim wbk1 As Workbook, wbk2 As Workbook
        Dim fileStr As String
        Dim ws2 As Worksheet
    
    
        'add your own file path
        
        fileStr = "C:\Users\My\Desktop\New Workbook Name.xlsx"
    
    
        Set wbk1 = ActiveWorkbook
        Set wbk2 = Workbooks.Add(fileStr)
         
        
        
        'wbk2.Sheets("WORKINGS").Copy After:=wbk1.Sheets("Sheet3")
        wbk1.Sheets("Sheet1").Copy wbk2.Sheets("Sheet2").Paste
       
        wbk2.Saved = True
    End Sub
    This macro will allow you to select which file to attach to the email, then it displays the email so you can edit anything desired before sending :

    
    Sub SendEmail()
       
        Dim xStrFile As String
        Dim xFilePath As String
        Dim xFileDlg As FileDialog
        Dim xFileDlgItem As Variant
        Dim xOutApp
        Dim xMailOut
        Dim olMailItem
        
        Application.ScreenUpdating = False
        Set xOutApp = CreateObject("Outlook.Application")
        Set xMailOut = xOutApp.CreateItem(olMailItem)
        Set xFileDlg = Application.FileDialog(msoFileDialogFilePicker)
        If xFileDlg.Show = -1 Then
            With xMailOut
                .To = "happy.xuebi@163.com"
                .Subject = "test"
                .HTMLBody = "test"
                For Each xFileDlgItem In xFileDlg.SelectedItems
                    .Attachments.Add xFileDlgItem
                Next xFileDlgItem
                .Display
            End With
        End If
        Set xMailOut = Nothing
        Set xOutApp = Nothing
        Application.ScreenUpdating = True
    
    
    End Sub
    Attached Files Attached Files

  3. #3
    VBAX Regular
    Joined
    Jun 2018
    Posts
    9
    Location
    Thank you so much. I modified it slightly to add the attachment I want.

  4. #4
    VBAX Mentor Logit's Avatar
    Joined
    Sep 2016
    Posts
    328
    Location
    .
    Glad you have your answer. Happy to help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •