HTML table using excel data (1 Viewer)

Brookie-m · Nov 28, 2019

Hi all,

I'm looking for help with sending multiple emails based on data in excel in a table format. I've tested it out and it works the way I need it to, except where it says TEST in the HTML table, I want it to pull in cell values - however nothing I try works (fyi completely new to this, this is a first attempt so hoping to learn):

Sub SendMailToUsers()

Dim outapp As New Outlook.Application

Dim outmail As Outlook.MailItem

Dim wks As Worksheet

Set outapp = CreateObject("outlook.application")

Set wks = Worksheets("sheet1")

For i = 3 To wks.Range("Q" & Rows.Count).End(xlUp).Row

Set outmail = outapp.CreateItem(olMailItem)

With outmail

.To = wks.Range("Q" & i).Value

.Subject = wks.Range("S" & i).Value

.BodyFormat = olFormatHTML

.HTMLBody = "<head><style>table,th,td{border:1px solid black;border-collapse:collapse;}th,td{padding:5px;}th{text-align:left;}</style></head><body><h2>Remittance Advice</h2><table style=width:50%><tr><th>Reference</th><th>Amount</th></tr><tr><td>TEST</td><td>TEST</td></tr></tr></table>"

.CC = wks.Range("R" & i).Value

.Send

End With

On Error GoTo 0

Set outmail = Nothing

Next i

Set outapp = Nothing

End Sub

Brookie-m · Nov 28, 2019

Sorry should probably explain that I tried using wks.Range("T" & i).Value but this didn't work.

vba_php · Nov 28, 2019

2 things.. .

1. The way u reference an xlUP spec in excel is like this:

Range("q50", range("q50").end(xlup)).

2.

You cant concatenate a string and an integer. So "q" & i wont fly. Use this:

"Q" & cstr(i)

Gasman · Nov 28, 2019

vba_php said:
2 things.. .

You cant concatenate a string and an integer. So "q" & i wont fly. Use this:

"Q" & cstr(i)

I beg to differ?

Code:

? Range("A" & ActiveCell.Row).Value 
25/12/2018

Gasman · Nov 28, 2019

Admittedly, I am not a software engineer, however I managed it this way, albeit in Access, but the logic is the same

Code:

' Set up HTML tags
    strPad = "<tr><td>"
    strEndPad = "</td></tr>"
    strPadCol = "</td><td>"
    strBlankLine = "<tr></tr>"

...................

            With objOutlookMsg
                .HTMLBody = .HTMLBody & strPad & str3rdPartyType & strPadCol & str3rdParty & strEndPad
                .HTMLBody = .HTMLBody & strPad & strDatetype & strPadCol & strDate & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Method:" & strPadCol & strMethod & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Reference:" & strPadCol & strRef & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Amount:" & strPadCol & strAmount & strEndPad
                .HTMLBody = .HTMLBody & strPad & "Balance:" & strPadCol & strBalance & strEndPad
                ' Add any notes if they exist
                If Len(strNotes) > 0 Then
                    .HTMLBody = .HTMLBody & strPad & "Notes:" & strPadCol & strNotes & strEndPad

                End If
'                ' Add blank line for next set
                .HTMLBody = .HTMLBody & strBlankLine & strBlankLine
            End With

So put the required cell values into variables and use those.
Notice how the text is concatenated with the html tags AND the variables.?

I know one could use the cell values directly, but I feel it males for easier to read code using variables.?

HTH

vba_php · Nov 28, 2019

Gasman said:
I beg to differ?

Code:

? Range("A" & ActiveCell.Row).Value 25/12/2018

could be! it never has worked for me so I always used cstr(). it must be a bug similar to when I was asked about my stripped down query string from a google search I did 2 days ago and posted in a thread for a question asker.

Cronk · Nov 29, 2019

Immediate window

Code:

? "A" & 5
A5


? "A" & date()
A11/29/2019

Try it.

isladogs · Nov 30, 2019

Also in the immediate window

Code:

i=3
?"X" & i
X3

HTML table using excel data (1 Viewer)

Brookie-m

New member

Brookie-m

New member

vba_php

Forum Troll

Gasman

Enthusiastic Amateur

Gasman

Enthusiastic Amateur

vba_php

Forum Troll

Cronk

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread