Private Sub GenerateEmailFromAccountTable()
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As ListRow
Dim accountName As String
Dim templateAsHTML As String
Dim i As Long
Set ws = ThisWorkbook.Sheets("YourSheetName")
Set tbl = ws.ListObjects("tblAccount")
' get the template
templateAsHTML = GenerateHTML
For i = 1 To tbl.ListRows.Count
accountName = tbl.ListRows(i).Range(, 1).Value
If Len(accountName) > 0 Then
EmailAccount accountName, templateAsHTML
tbl.ListRows(i).Range(, 2).Value = Now
End If
Next rng
End Sub
Private Function GenerateHTML() As String
Dim html As String
html = "<html><body><p>Your account is awesome!</p></body></html>"
GenerateHTML = html
End Function
Private Sub EmailAccount(accountName As String, templateAsHTML As String)
Dim app As Object
Dim item As Object
On Error GoTo eh
Application.EnableEvents = False
Application.ScreenUpdating = False
Set app = CreateObject("Outlook.Application")
Set item = app.CreateItem(0)
If Len(accountName) > 0 Then
With item
.To = accountName
.Subject = "Subject Line"
.Body = templateAsHTML
.send
End With
Else
Err.Raise 1001, "EmailAccount", "Email is not set in admin, can't send"
End If
GoTo out
eh:
' do something with the error
out:
Application.EnableEvents = True
Application.ScreenUpdating = True
Set app = Nothing
Set item = Nothing
End Sub
