Create mails with table - standard header specific row

[esben]

I am trying to create a code that can create Namelist amount of emails. Each email should include some specific cell variables such as name and email AND a table in which i need the header from the excel sheet and a specific row.

I have tried to incoporate a take one row at a time approach but can't seem to make it function. It will not include the specific rows. I will set the ActiveRow (first row) in always.

I have tried to make a for each Name in Namelist.count and it seems that the Namelist.count can find the specific number of rows.

This is the code:

The columns work great and always give the header. There is some code that i have tried and not suceeded with still so you might get an idea of where it started.

I am very new to this, so hope someone has a kind advice or knows how to fix it.

Sub PLGBarcodeFile()

Dim EApp As Object
Set EApp = CreateObject("Outlook.Application")

Application.ScreenUpdating = False

Dim EItem As Object

Dim NameList As Range
Set NameList = Range("A2", Range("a2").End(xlDown))

'Table header.
Dim iColumnsCount, iColCnt As Integer
Dim sTableHeads As String
iColumnsCount = Worksheets("Sent Email").UsedRange.Columns.Count

For iColCnt = 1 To iColumnsCount
'Table header concatenated with HTML <th> tags.
    If (sTableHeads) = "" Then
        sTableHeads = "<th>" & Worksheets("Sent Email").Cells(1, iColCnt) & "</th>"
    Else
        sTableHeads = sTableHeads & "<th>" & Worksheets("Sent Email").Cells(1, iColCnt) & "</th>"
    End If
Next iColCnt

' Table data.

'Dim iRowsCount, iRows As Integer
 Dim sTableData As String
'iRowsCount = Worksheets("Sent Email").UsedRange.Rows.Count

sTableData = "<tr>"
For iColCnt = 1 To iColumnsCount

' Table data concatenated with HTML <td> tags.
    If (sTableData) = "" Then
        sTableData = "<td>" & sTableHeads = Worksheets("Sent Email").Cells(ActiveCell.Row, iColCnt) & "  </td>"
    Else
        sTableData = sTableData & "<td>" & Worksheets("Sent Email").Cells(ActiveCell.Row, iColCnt) & "</td>"
    End If

Next iColCnt
    
sTableData = sTableData & "</tr>"

' Add CSS style to the table.

Dim sTableStyle As String
sTableStyle = "<style> table.edTable { width: 75%; font: 18px calibri; } table, table.edTable th,       table.edTable td { border: solid 1px #000000; border-collapse: collapse; padding: 3px; text-align: center;  } table.edTable td { background-color: #ffffff; color: #000000; font-size: 14px; } table.edTable th {  background-color : #ffffff; color: #000000; } tr:hover td { background-color: #000000; color: #ffffff; }  </style>"

Dim sHTMLBody As String            ' The body (in HTML format) of the email. The table has a CSS class.
sHTMLBody = "Dear " & Worksheets("Sent Email").Cells(ActiveCell.Row, 10) & "," & "<br>" & "<br>" _
& "" & "<br>" & "<br>" _
& "" & "(" & Date & ")" & "." & "<br>" & "<br>" _
& "<b>Current/Old process: </b>" & "<br>" _
& "" & "<br>" & "<br>" _
& "<b>New process: </b>" & "<br>" _
& "" & "<br>" & "<br>" _
& "" & "<br>" _
& "<b>Action for you:</b>" & "<br>" _
& "" & "<br>" & "<br>" _
& sTableStyle & "<table class='edTable'><tr>" & sTableHeads & "</tr>" & _
        "<tr>" & sTableData & "</tr></table>" & "<br>" & "<br>" _
& "" & "<br>" _
& "" & "<br>" _
& ""
    
For Name = 1 To NameList.Count

Set EItem = EApp.CreateItem(0)

ActiveCell.Offset(Name, 0).Range("A2").Select
    
With EItem
    .To = Worksheets("Sent Email").Cells(ActiveCell.Row, 9)
    .Subject = "Evaluate information regarding barcodes for " & Worksheets("Sent         Email").Cells(ActiveCell.Row, 2)
    .HTMLbody = sHTMLBody
    
    .Display
     
End With
 
Next Name

Application.ScreenUpdating = True

End Sub

 

[esben]

I solved it by moving the Next name on NameList up infront of the tabledata retrieve code - also i moved the sHTMLbody string down to the HTMLbody - This was not necessary but i prefered it.

I changed all the ActiveCell.Row to Name