Excel-VBA : Send a Mail using Predefined Template From MS Outlook Using Excel

In our earlier tutorial we have seen how to send a simple text mail using MS Outlook but how about when you want to send a proper composed mail, means your will compose the mail in a proper format in you excel and you want formatting to be retained while sending.

In that case you will not fetch the text from a excel cell for body, instead you will copy and paste the entire cell into your mail body.

Steps:

• Create object of Outlook Application.
• Create a Mail Item.
• Get the Document Object (for pasting)
• Compose and Send mail using mail Item.

Create object of Outlook Application.

Set otlApp = CreateObject("Outlook.Application")

Create a Mail Item.

Set olMail = otlApp.CreateItem(olMailItem)

Get the Document Object (for pasting)

Set Doc = olMail.GetInspector.WordEditor

Compose and Send mail using mail Item, use document object for paste the mail in body.

With olMail
.To = SendID
If CCID <> "" Then
.CC = CCID
End If
.Subject = Subject
mainWB.Sheets("Mail").Range("B4").Copy
Set WrdRng = Doc.Range
.Display
WrdRng.Paste
.Send
End With

Complete Code:

Sub sumit()
Dim mainWB As Workbook
Dim SendID
Dim CCID
Dim Subject
Dim Body

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("B4").Value
With olMail
.To = SendID
If CCID <> "" Then
.CC = CCID
End If
.Subject = Subject
mainWB.Sheets("Mail").Range("B4").Copy
Set WrdRng = Doc.Range
.Display
WrdRng.Paste
.Send

End With
MsgBox ("you Mail has been sent to " & SendID)
End Sub

Also Read:

1. Introduction to Excel WorkBook
2. VBA-Excel: Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet
3. Send Mail With Multiple Different Attachments From MS Outlook using Excel.
4. VBA-Excel: Read Data from XML File
5. VBA-Excel: Open word document using GetObject()