I am trying to insert text, hyperlink and table in the mail body.
Sub Sendmail()
Dim olItem As Outlook.MailItem
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Dim sPath As String
Dim iRow As Long
Dim strRFIitems As String
Dim Signature As String
sPath = "**"
' // Excel
Set xlApp = CreateObject("Excel.Application")
' // Workbook
Set xlBook = xlApp.Workbooks.Open(sPath)
' // Sheet
Set xlSht = xlBook.Sheets("Sheet1")
' // Create e-mail Item
Set olItem = Application.CreateItem(olMailItem)
trRFIitems = xlSht.Range("E2")
Signature = xlSht.Range("F2")
With olItem
.To = Join(xlApp.Transpose(xlSht.Range("A2", xlSht.Range("A9999").End(xlUp))), ";")
.CC = Join(xlApp.Transpose(xlSht.Range("B2", xlSht.Range("B9999").End(xlUp))), ";")
.Subject = xlSht.Range("C2")
.Body = xlSht.Range("D2") & Signature
.Attachments.Add (strRFIitems)
.Display
End With
' // Close
xlBook.Close SaveChanges:=True
' // Quit
xlApp.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSht = Nothing
Set olItem = Nothing
End Sub
This code retrieves the data from the linked Excel sheet and sends a mail.
The requirement is:
Retrieve the To, CC, Body, Subject and signature data from the linked Excel sheet.
The expected result:
Please note this is the expected format.
The expected mail body contains both hyperlink and a table.
Note: I need to get values from Excel because the values in the table keep changing.