Be the first user to complete this post

  • 0
Add to List

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



Also Read:

  1. Excel-VBA : Send a Mail using Predefined Template From MS Outlook Using Excel
  2. VBA-Excel: Appending Text to an Existing Word Document - at the End
  3. VBA-Excel: Open and Print the Word Document
  4. VBA-Excel: Change Font, Color, Weight of Table Data in the Word document
  5. VBA-Excel: Format already written text in a word document – Format All Content
  6. VBA-Excel — AttachmentFetcher — Download all the Attachments from All the Mails of Specific Subject in Microsoft Outlook .
  7. Introduction to Excel WorkBook
  8. VBA-Excel: Convert Numbers (Rupees) into Words OR Text - Updated Till 1000000 Crore With Decimal Numbers