For some reason this code does not populate the text from the column and I am trying to create a loop where if the row has the same email address then it will populate one email instead sending out multiple emails for the same entity.
Option Explicit
Sub SendcomplexEmailHTML()
'early binding
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Dim Source As Rangevvvvvvvvvvv
Application.DisplayAlerts = False
Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
.bodyFormat = olFormatHTML
.Display
.HTMLBody = GetHeadHTML & "<p>Dear Sir (s)</p><br><br>" & _
GetMovieDataHTML & .HTMLBody & "</body>"
'.Attachments.Add ""
.To = "aj1st@hotmail.com"
.subject = "Movie Report"
'.Send
End With
End Sub
Function GetMovieDataHTML() As String
Dim FilmColumn As Range, FilmRow As Range, r As Range, c As Range
Dim str As String
Sheet1.Activate
Set FilmColumn = Range("A2", Range("A1").End(xlDown))
str = "<table>"
For Each r In FilmColumn
str = str & "<tr>"
Set FilmRow = Range(r, r.End(xlToRight))
For Each c In FilmRow
str = str & "<td>" & c.Value & "</td>"
Next c
str = str & "</tr>"
Next r
str = str & "</table>"
GetMovieDataHTML = str
End Function
Function GetHeadHTML() As String
Dim str As String
str = "<head><style>" & vbNewLine
str = str & "p {colour:blue;font-family:calibri;font-size:12px;}" & vbNewLine
str = str & "table{colour:blue;font-family:calibri;font-size:12px;border:2px solid blue;}" & vbNewLine
str = str & "</style></head>******>"
GetHeadHTML = str
End Function
Option Explicit
Sub SendcomplexEmailHTML()
'early binding
Dim olApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Dim Source As Rangevvvvvvvvvvv
Application.DisplayAlerts = False
Set olApp = New Outlook.Application
Set olEmail = olApp.CreateItem(olMailItem)
With olEmail
.bodyFormat = olFormatHTML
.Display
.HTMLBody = GetHeadHTML & "<p>Dear Sir (s)</p><br><br>" & _
GetMovieDataHTML & .HTMLBody & "</body>"
'.Attachments.Add ""
.To = "aj1st@hotmail.com"
.subject = "Movie Report"
'.Send
End With
End Sub
Function GetMovieDataHTML() As String
Dim FilmColumn As Range, FilmRow As Range, r As Range, c As Range
Dim str As String
Sheet1.Activate
Set FilmColumn = Range("A2", Range("A1").End(xlDown))
str = "<table>"
For Each r In FilmColumn
str = str & "<tr>"
Set FilmRow = Range(r, r.End(xlToRight))
For Each c In FilmRow
str = str & "<td>" & c.Value & "</td>"
Next c
str = str & "</tr>"
Next r
str = str & "</table>"
GetMovieDataHTML = str
End Function
Function GetHeadHTML() As String
Dim str As String
str = "<head><style>" & vbNewLine
str = str & "p {colour:blue;font-family:calibri;font-size:12px;}" & vbNewLine
str = str & "table{colour:blue;font-family:calibri;font-size:12px;border:2px solid blue;}" & vbNewLine
str = str & "</style></head>******>"
GetHeadHTML = str
End Function
