Be the first user to complete this post
|
Add to List |
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
![Send a Mail using Predefined Template From MS Outlook Using Excel](/static/media/excel/2014/12/Send-a-Mail-using-Predefined-Template-From-MS-Outlook-Using-Excel-1024x404.jpg)
![Send a Simple Mail From MS Outlook Using Excel - 2](/static/media/excel/2014/12/Send-a-Simple-Mail-From-MS-Outlook-Using-Excel-2.jpg)
Also Read:
- Excel-VBA : Send a Excel Workbook as Attachment in Mail From MS Outlook Using Excel
- Excel-VBA : Insert Multiple Images from a Folder to Excel Cells
- VBA-Excel: Open and Print the Word Document
- Send Mail With Link to a Workbook, From MS Outlook using Excel.
- VBA-Excel: Update XML File