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