Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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

In our ear­lier tuto­r­ial we have seen how to send a sim­ple text mail using MS Out­look but how about when you want to send a proper com­posed mail, means your will com­pose the mail in a proper for­mat in you excel and you want for­mat­ting 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:

  • Cre­ate object of Out­look Application.
  • Cre­ate a Mail Item.
  • Get the Doc­u­ment Object (for pasting)
  • Com­pose and Send mail using mail Item.


Cre­ate object of Out­look Application.

Set otlApp = CreateObject("Outlook.Application")

Cre­ate a Mail Item.

Set olMail = otlApp.CreateItem(olMailItem)

Get the Doc­u­ment Object (for pasting)

Set Doc = olMail.GetInspector.WordEditor

Com­pose and Send mail using mail Item, use doc­u­ment 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

Com­plete 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

Send a Mail using Predefined Template From MS Outlook Using Excel

Send a Mail using Pre­de­fined Tem­plate From MS Out­look Using Excel

Send a Simple Mail From MS Outlook Using Excel - 2

Send a Sim­ple Mail From MS Out­look Using Excel — 2

You may also like...

1 Response

  1. Venu says:

    Hi Sumit,

    Thank you so much for this awe­some post. Could you also help us with the same kind of tem­plate for lotus notes also?

    Thanks in advance!

Leave a Reply

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

%d bloggers like this: