Be the first user to complete this post

  • 0
Add to List

Send Mail With Link to a Workbook, From MS Outlook using Excel.

In earlier post you have learned  Send Mail with Embedded Image in message body From MS Outlook. In this article we will learn about how to Send Mail With a link in a Message body to a Excel Workbook From MS Outlook using Excel.

I would recommend that you must read Send a Simple Mail From MS Outlook Using Excel and Send Mail with Embedded Image in message body From MS Outlook to understand the complete code below.

Complete Code:

Sub sumit()
Dim mainWB As Workbook
Dim SendID
Dim CCID
Dim Subject
Dim Body
Dim olMail As MailItem

Set otlApp = CreateObject("Outlook.Application")
Set olMail = otlApp.CreateItem(olMailItem)
Set Doc = olMail.GetInspector.WordEditor
'Dim colAttach As Outlook.Attachments
Dim oAttach As Outlook.Attachment

Set mainWB = ActiveWorkbook

SendID = mainWB.Sheets("Mail").Range("B1").Value
CCID = mainWB.Sheets("Mail").Range("B2").Value
Subject = mainWB.Sheets("Mail").Range("B3").Value
Link = mainWB.Sheets("Mail").Range("B4").Value
Body = mainWB.Sheets("Mail").Range("B5").Value
With olMail
.To = SendID
If CCID <> "" Then
.CC = CCID
End If
.Subject = Subject
.HTMLBody = Body & "<A HREF=""file://" & Link & _
""">Click me open the file</A>" & _
"<br><br>Regards," & _
"<br><br>Sumit Jain</font>"
.Display
.Send
End With
MsgBox ("you Mail has been sent to " & SendID)
End Sub

Sub browse()
Dim FSO As Object
Dim blnOpen
strFileToOpen = Application.GetOpenFilename(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")
If strFileToOpen = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Sheet1.Range("B4").Value = strFileToOpen
End If
End Sub

Send Mail With Link to a Workbook, From MS Outlook  - Mail
Send Mail With Link to a Workbook, From MS Outlook  - Output
Send Mail With Link to a Workbook, From MS Outlook  - Open excel 2
Send Mail With Link to a Workbook, From MS Outlook  - Open excel 3
Send Mail With Link to a Workbook, From MS Outlook  - Open excel



Also Read:

  1. Excel-VBA : Send a Mail using Predefined Template From MS Outlook Using Excel
  2. Excel-VBA : Prevent Changing the WorkSheet Name
  3. Introduction to Excel WorkBook
  4. VBA-Excel — AttachmentFetcher — Download all the Attachments from All the Mails of Specific Subject in Microsoft Outlook .
  5. VBA-Excel: Format already written text in a word document – Format Paragraphs