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 earlier post we have seen, How to send a simple text mail using MS Excel. In this tutorial we will learn How to send the entire Excel Workbook as attachment along with the mail from Microsoft Outlook using Excel VBA.

Function 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 providing the complete file name as parameter.

To understand the complete Code, Read – How to send a simple text mail using MS Excel

Complete 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 Attachment Workbook

Received mail - Mail with Attachment Workbook

Received mail – Mail with Attachment Workbook

You may also like...

4 Responses

  1. Dave says:

    Thank you very much. I found several of your tutorials very helpful and easy to follow.

  2. Ren says:

    Hi, would like to seek assistance if it’s possible to call two or more cells for the email body, and signature also. Thanks!

  3. Ren says:

    Hi, may I ask, is it possible to have 2 cells for the email body? can we also add an signature? Thanks a lot.

Leave a Reply

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

%d bloggers like this: