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:

[sourcecode language=”VB”]
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

[/sourcecode]

Send a Mail using Predefined Template From MS Outlook Using Excel
Send a Mail using Predefined Template From MS Outlook Using Excel
Send a Simple Mail From MS Outlook Using Excel - 2
Send a Simple Mail From MS Outlook Using Excel – 2

1 thought on “Excel-VBA : Send a Mail using Predefined Template From MS Outlook Using Excel”

  1. Hi Sumit,

    Thank you so much for this awesome post. Could you also help us with the same kind of template for lotus notes also?

    Thanks in advance!

    Reply

Leave a Comment