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. VBA-Excel: Create a new Word Document
2. VBA-Excel: Perform Google Search on Internet Explorer using Microsoft Excel
3. VBA-Excel: Update XML File
4. VBA-Excel: Format already written text in a word document – Format All Content