asked on

Insert table in email using VBA

I want to add table in email sent by VBA code from access . Below is the code as of now.

MyStr1 = StrComp(.Fields(11), "Cisco")
If MyStr1 = 0 Then
Set objBP = email.AddRelatedBodyPart("C:\images\cisco.jpg", "cisco.jpg", 1)
email.Fields.Item("urn:schemas:mailheader:Content-ID") = "<cisco.jpg>"
email.Fields.Update
email.HTMLBody = "<html><img src=""cisco.jpg""><br><br>Dear " & .Fields(3) & ",<br><br> " & str1 & " in <b> " & .Fields(5) & " days</b>.<br><br>" & str2 & "<br><br>Your current<b> " & .Fields(4) & " </b>for <b>" & .Fields(13) & "</b> will end on<b> " & .Fields(10) & " </b>.<br><br>" & str3 & " <br><br>Below are the Products/ Support/Services that are nearing expiry:<br>Service Details:<b> " & .Fields(4) & "<br></b>Expiry Date:<b> " & .Fields(10) & "</b><br><br><br>" & str4 & ".<br>Thanks for taking action now.<br><br> " & str5 & " <br><br><br><b><i>Thank You.<br><br>Customer Service<br>Company Name<br>support@domain.com<br><br>Customer Service<br><br><img src=""logo.jpg""></b></i></div></body></html>"""
End If

Open in new window

I want to insert a table like below in the same email as below. is there anyone who can guide me?
--------------------------------------------------
|Service Details: |& .Fields(4) |
--------------------------------------------------
|Expiry Date: |& .Fields(10) |
---------------------------------------------------

Daniel Pineault

You need to build the proper HTML code for a table using the proper tags

<table>
<tr>
<td>Row1 Col1</td>
<td>Row1 Col2</td>
</tr>
<tr>
<td>Row2 Col1</td>
<td>Row2 Col2</td>
</tr>
</table>

Open in new window

Bill Prew

Here's a solution to a prior question that shows how I like to attack these. You may or may not like it, but it's an approach, and shows the incorporation of tables into the HTML body. I like to build a template file with the HTML tags and formatting in it, and then use that at execution time and merge in the needed data where "placeholder" strings have been added to the template. If you look at it I hope you get the idea.

It's a VBS solution, but adapting that to VBA would be easy enough, it's really the concept you are after too...

Email Solution

Some of the more relevant and important code is snipped below, and also look at the HTML file from the prior question to see where the data gets merged in to the HTML place holders.

' Read body template and save in variable for email building
With objFSO.OpenTextFile(strBodyTemplate, ForReading, False, TriStateUseDefault)
    strBody = .ReadAll
    .Close
End With


' - - - - - - - - - - - - - - - - - - - - - - - - - -


        .HtmlBody = BuildBody(arrFields, strAttachment)


' - - - - - - - - - - - - - - - - - - - - - - - - - -


' Function to build the html body for each email sent
Function BuildBody(arrFields(), strAttachment)

    ' Start from default body template we loaded earlier
    BuildBody = strBody

    ' Replace any data driven tokens with this recipients data
    BuildBody = Replace(BuildBody, "[[CompletedOn]]",   arrFields(cCompletedOn),   1, -1, vbTextCompare)
    BuildBody = Replace(BuildBody, "[[CompletedBy]]",   arrFields(cCompletedBy),   1, -1, vbTextCompare)
    BuildBody = Replace(BuildBody, "[[Certificate]]",   arrFields(cCertificate),   1, -1, vbTextCompare)
    BuildBody = Replace(BuildBody, "[[OrderNumber]]",   arrFields(cOrderNumber),   1, -1, vbTextCompare)
    BuildBody = Replace(BuildBody, "[[EmailAddress]]",  arrFields(cEmailAddress),  1, -1, vbTextCompare)
    BuildBody = Replace(BuildBody, "[[ClientSiteJob]]", arrFields(cClientSiteJob), 1, -1, vbTextCompare)
    BuildBody = Replace(BuildBody, "[[Company]]",       cCompany,                  1, -1, vbTextCompare)
    BuildBody = Replace(BuildBody, "[[CompanyName]]",   cCompanyName,              1, -1, vbTextCompare)
    BuildBody = Replace(BuildBody, "[[Attachment]]",    strAttachment,             1, -1, vbTextCompare)

End Function

Open in new window

»bp

M A

ASKER

Getting this error when I copied the table from above thread

Bill Prew

Without seeing the full VBA hard to be sure, but I would say that is because you have double quotes inside a string literal. You will need to "escape" embedded double quotes in a string by doubling them up, so to create a string of:

<table class="datatable">

you would need to do this in VBA:

"<table class=""datatable"">"

Hope that makes sense. And this is why I tend to place the template for the HTML into an external file that I can read, it avoids all this and makes it a lot easier in the long run to test and maintain.

»bp

M A

ASKER

I changed to this now but still table is not coming in the email.

email.HTMLBody = "<html><img src=""ms.jpg""><br><br>Dear " & .Fields(3) & ",<br><br> " & str1 & " in <b> " & .Fields(5) & " days</b>.<br><br>" & str2 & "<br><br>Your current<b> " & .Fields(4) & " </b>for <b>" & .Fields(13) & "</b> will end on<b> " & .Fields(10) & " </b>.<br><br>" & str3 & " <br><br>Below are the Products/ Support/Services that are nearing expiry:<br>Service Details:<b> " & .Fields(4) & "<br></b>Expiry Date:<b> " & .Fields(10) & "</b><br><br><br>" & str4 & ".<br>Thanks for taking action now.<br><br> " & str5 & " <br><br><br><b><i>Thank You.<br><br>Customer Service<br>Company Name<br>support@domain.com<br><table class=""datatable""><tr><td class=""datatable label"">Service Details:</td><td class=""datatable data"">[&.Fields(4).value]</td></tr><tr><td class=""datatable label"">Expiry Date:</td><td class=""datatable data"">[.Fields(10).value]</td></tr></table><br>Customer Service<br><br><img src=""logo.jpg""></b></i></div></body></html>"""

Open in new window

Bill Prew

If it were me, I would DEBUG that VBA code, and right after that statement copy the full value of the email.htmlbody property, and then save that to am HTML file, and then open that in your internet browser of choice to see what it displays. Could still be an error in the HTML itself.

»bp

Bill Prew

One easy problem to spot was right at the end of the string. This:

</html>"""

should be:

</html>"

That being said, there are a lot of other things going on in there that could cause a problem, just copying the HTML from the prior question and placing it in your code is not likely to work that well. There was some embedded CSS in the other questions HTML template, and images being referenced, and data fields, etc. It was meant more as an example for the approach, and less as a turnkey set of code.

»bp

Daniel Pineault

You could try something more along the lines of

"<html><img src=""ms.jpg""><br><br>Dear " & .Fields(3) & ",<br><br> " & str1 & " in <b> " & .Fields(5) & " days</b>.<br><br>" & str2 & _
        "<br><br>Your current<b> " & .Fields(4) & " </b>for <b>" & .Fields(13) & "</b> will end on<b> " & .Fields(10) & " </b>.<br><br>" & str3 & _
        " <br><br>Below are the Products/ Support/Services that are nearing expiry:<br>Service Details:<b> " & .Fields(4) & _
        "<br></b>Expiry Date:<b> " & .Fields(10) & "</b><br><br><br>" & str4 & ".<br>Thanks for taking action now.<br><br> " & str5 & _
        " <br><br><br><b><i>Thank You.<br><br>Customer Service<br>Company Name<br>support@domain.com<br><table class=""datatable"">" & _
        "<tr><td class=""datatable label"">Service Details:</td><td class=""datatable data"">" & .Fields(4) & "</td></tr>" & _
        "<tr><td class=""datatable label"">Expiry Date:</td><td class=""datatable data"">" & .Fields(10) & "</td></tr></table><br>" & _
        "Customer Service<br><br><img src=""logo.jpg""></b></i></div></body></html>"

Open in new window

Also, in your code you make use of </div> and </body>, yet you don't have any such opening tags? You should have the content within <html><body>Your Content Should Go Here</body></html> tags.
You also are making use of classes, but I don't see them defined anywhere? They would need to be defined in the <head></head> section if you are doing so.

M A

ASKER

Bill Prew

It looks like you are missing the

email.HTMLBody =

there.

»bp

M A

ASKER

Yes. I added.
Appreciate your help.
Now I am not getting emails.

Bill Prew

Do you have ON ERROR GOTO 0 set, so that any errors that occur will pop up at runtime? Rather than ON ERROR RESUME NEXT which will suppress them...

»bp

M A

ASKER

I tested with only table.

email.HTMLBody = "<html><img src=""cisco.jpg""><body><table> <tr><th> Client Number:</th><th>ClientSiteJob</th></tr> </table></body></html>""" '

Open in new window

I am getting no table in email. Below is the email screenshot.

Bill Prew

Actually you are getting the table, which you only defined with a header row. The "Client Number: ClientSiteJob" is the header of that table.

»bp

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

A little hint that might hep, instead of using double quotes in the HTML code try using single quotes, for example.

"<html><img src='ms.jpg'><br><br>Dear " & .Fields(3)

Open in new window

Daniel Pineault

Also, because your tags aren't opened and closed properly as previously mentioned it is possible that it won't render. Fix the <body> <div> ... and other html issues and try again.