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

You may also like...

1 Response

  1. Venu says:

    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!

Leave a Reply to Venu Cancel reply

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

Show Buttons
Hide Buttons
%d bloggers like this: