Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

Excel-VBA : Send a Excel Workbook as Attachment in Mail From MS Outlook Using Excel

In out ear­lier post we have seen, How to send a sim­ple text mail using MS Excel. In this tuto­r­ial we will learn How to send the entire Excel Work­book as attach­ment along with the mail from Microsoft Out­look using Excel VBA.

Func­tion to add attachment :

olMail.Attachments.Add “File Name”

NOTE: I have used the browse option to make it more real, you can avoid that by directly pro­vid­ing the com­plete file name as parameter.

To under­stand the com­plete Code, Read — How to send a sim­ple text mail using MS Excel

Com­plete Code:

 

Dim mainWB As Workbook
Sub sumit()
    Dim SendID
    Dim CCID
    Dim Subject
    Dim Body
    Dim AttachFile
    Set otlApp = CreateObject("Outlook.Application")
    Set olMail = otlApp.CreateItem(olMailItem)
    Set Doc = olMail.GetInspector.WordEditor
    Set mainWB = ActiveWorkbook
    
    SendID = mainWB.Sheets("Mail").Range("B1").Value
    CCID = mainWB.Sheets("Mail").Range("B2").Value
    Subject = mainWB.Sheets("Mail").Range("B3").Value
    Body = mainWB.Sheets("Mail").Range("B5").Value
    AttachFile = mainWB.Sheets("Mail").Range("B4").Value
    With olMail
        .To = SendID
        If CCID <> "" Then
          .CC = CCID
        End If
        .Subject = Subject
        mainWB.Sheets("Mail").Range("B5").Copy
        Set WrdRng = Doc.Range
        .Display
        WrdRng.Paste
        If InStr(1, AttachFile, "xls", vbTextCompare) Then
            .Attachments.Add AttachFile
        End If
        .Send
          
    End With
    MsgBox ("you Mail has been sent to " & SendID)
End Sub
Sub browse()
    Dim FSO As Object
    Dim blnOpen
    strFileToOpen = Application.GetOpenFilename(Title:="Please choose a file to open", _
    FileFilter:="Excel Files *.xls* (*.xls*),")
    If strFileToOpen = False Then
      MsgBox "No file selected.", vbExclamation, "Sorry!"
      Exit Sub
    Else
        mainWB.Sheets("Mail").Range("B4").Value = strFileToOpen
    End If
End Sub

Mail with Attachment Workbook

Mail with Attach­ment Workbook

Received mail - Mail with Attachment Workbook

Received mail — Mail with Attach­ment Workbook

You may also like...

4 Responses

  1. Dave says:

    Thank you very much. I found sev­eral of your tuto­ri­als very help­ful and easy to follow.

  2. Ren says:

    Hi, would like to seek assis­tance if it’s pos­si­ble to call two or more cells for the email body, and sig­na­ture also. Thanks!

  3. Ren says:

    Hi, may I ask, is it pos­si­ble to have 2 cells for the email body? can we also add an sig­na­ture? Thanks a lot.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: