Email with table (1 Viewer)

Onlylonely

Registered User.
Local time
Today, 23:14
Joined
Jan 18, 2017
Messages
43
Hi Guys,

Appreciate if you are able to give some advise on below issue:

What i'm getting now is

Part Number : 54
Received Qty: 54
Rejected Qty: 1
QN : 2
Supplier : 3
Rejection : 4

What i wish to get is in table form (Not able to draw a table here)

Part Number 54
Received Qty 54
Rejected Qty 1
QN********* 2
Supplier*** 3
Rejection** 4

Code:
Function Email()

Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")
Dim mailbody As String

Set oMail = oApp.CreateItem(olMailItem)
oMail.Subject = "[eIncoming - Autogenerated] - Part " & Forms!QCform!Part_Number & " rejected."

oMail.Body = "Hi Team," & Chr(10) & Chr(10)
oMail.Body = oMail.Body & "Part Number : " & Forms!QCform!Part_Number & Chr(10)
oMail.Body = oMail.Body & "Received Qty: " & Forms!QCform!Qty & Chr(10)
oMail.Body = oMail.Body & "Rejected Qty: " & Forms!QCform!Qty_Reject & Chr(10)
oMail.Body = oMail.Body & "QN          : " & Forms!QCform!QN & Chr(10)
oMail.Body = oMail.Body & "Supplier    : " & Forms!QCform!Supplier & Chr(10)
oMail.Body = oMail.Body & "Rejection   : " & Forms!QCform!Txtrejection & Chr(10)


'              omailbody = omailbody & "<TR>" & _
'               "<TD ><center>" & rs.Fields![Rep name].Value & "</TD>" & _
'               "<TD><center>" & rs.Fields![zone].Value & "</TD>" & _
'               "<TD><center>" & rs.Fields![Location].Value & "</TD>" & _
'               "<TD><center>" & rs.Fields![Sales].Value & "</TD>" & _


'oMail.Body = "Hi Team, " & vbCrLf & vbCrLf & " Part Number: " & Forms!QCform!Part_Number & vbCrLf & " Rejected Qty: " & Forms!QCform!Qty & vbCrLf & "QN: " & Forms!QCform!Part_Number & vbCrLf & vbCrLf & vbCrLf & "Regards, " & vbCrLf & "TonyShieh"
oMail.To = "abc@yahoo.com"
oMail.Send
Set oMail = Nothing
Set oApp = Nothing

End Function
 

isladogs

MVP / VIP
Local time
Today, 15:14
Joined
Jan 14, 2017
Messages
18,186
I assume you want to line up the numbers vertically.
To do so, do one of the following:

Method 1
1. use a monospaced or fixed width font. See https://www.typewolf.com/top-10-monospaced-fonts
2. use the space function e.g. Space(5) will add 5 spaces

Take the longest string, Received qty, count the characters (12) and add e.g. 8 spaces to make a total length 20
Repeat with the other lines but adding more spaces as needed to give a total length 20 each time. e.g. QN & Space(18)
If you want the numbers to line up at the right then you need to take the number length into account as well!

Method 2
Use vbTab as a filler to line up the numbers
This sounds simpler but can be less reliable
 
Last edited:

Onlylonely

Registered User.
Local time
Today, 23:14
Joined
Jan 18, 2017
Messages
43
Hi Sir,

Thank you for your reply. What i wish to get is the data in a table.

Example as attached.
 

Attachments

  • 123.JPG
    123.JPG
    17.2 KB · Views: 107

isladogs

MVP / VIP
Local time
Today, 15:14
Joined
Jan 14, 2017
Messages
18,186
The methods I suggested will give a tabular layout but without the table grid lines
 

Onlylonely

Registered User.
Local time
Today, 23:14
Joined
Jan 18, 2017
Messages
43
Hi All,

I've tried to use HTML method. The method that you have provide is get the data from recordsource.

I wish to get the data from input instead of database. May i know how to get it? For example below code, i wish to get

(Part number) (12345) < in a table

Code:
Function BuildHtmlBody()

    Dim html

    html = "<!DOCTYPE html><html><body>"
    html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">"
    html = html & "Dear {name}, <br /><br />This is a test email from MS Access using VBA. <br />"
    html = html & "Here is current recordset data:<br /><br />"
    html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>"


        html = html & "<tr>"
        html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" Part_Number "</td>"
        html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & Forms!QCForm!Part_Number & "</td>"
        html = html & "</tr>"

'        rs.MoveNext
'    Loop

    html = html & "</table></div></body></html>"
    BuildHtmlBody = html

End Function
 

Onlylonely

Registered User.
Local time
Today, 23:14
Joined
Jan 18, 2017
Messages
43
I've figured out the way to do that. But can anyone teach me how to limit the width of the column?

I tried to put Table width = 50mm, but still not able to get it.

Code:
Function Email()

Dim oApp As Outlook.Application
'Dim g As MailItem
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")
Dim mailbody As String

Set oMail = oApp.CreateItem(olMailItem)
oMail.Subject = "[eIncoming - Autogenerated] - Part " & Forms!QCForm!Part_Number & " rejected."

'oMail.Body = "Hi Team," & Chr(10) & Chr(10)
mailbody = "<TABLE Border=""2"",TABLE WIDTH = ""30mm"", Cellspacing=""1"" ><TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> Part Number </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> " & Forms!QCForm!Part_Number & " </p></Font></TD></TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> Received Qty </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> " & Forms!QCForm!Qty_Reject & " </p></Font></TD></TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> QN </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> " & Forms!QCForm!QN & " </p></Font></TD></TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> Supplier </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> " & Forms!QCForm!Supplier & " </p></Font></TD></TR>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> Rejection </p></Font></TD>" & _
"<TD Bgcolor=""#2B1B17"", Align=""Center""><Font Color=#FCDFFF><b><p style=""font-size:18px""> " & Forms!QCForm!Txtrejection & " </p></Font></TD></TR>"

oMail.htmlbody = "Please find the ----- below ----- <br><br><hmtl> " & mailbody & " </Table></html>"

oMail.To = "abc@yahoo.com"
oMail.Send

Set oMail = Nothing
Set oApp = Nothing

End Function
 

Attachments

  • width.JPG
    width.JPG
    43.6 KB · Views: 91

Minty

AWF VIP
Local time
Today, 15:14
Joined
Jul 26, 2013
Messages
10,354
HTML by definition will scale to fit the available space, so you can normally only determine a percentage of the width of that available space. Try

table width= "75%"

Or if you want to get clever you can put your table in a table, but the HTML can get very long winded / messy. I've made some pretty complicated HTML email tables but mainly in SQL Server using CSS, and it takes quite a lot of "finessing" to get good results.

I also had a stored procedure that I could pass a table to that would encompass the data into a branded stylised template.

One caveat - Outlook is absolutely arse at displaying properly formatted HTML. Preview the mail in another reader to see the real format.
 

Onlylonely

Registered User.
Local time
Today, 23:14
Joined
Jan 18, 2017
Messages
43
I've tried but not success. It still the same as previous.
 

Minty

AWF VIP
Local time
Today, 15:14
Joined
Jul 26, 2013
Messages
10,354
Actually there is something on that page which maybe explains why it doesn't work;

Note: The width attribute is not supported in HTML5. Use CSS instead.

I did mention (unintentionally) that the SQL code I utilise is CSS based... Has anyone tried using CSS in an Outlook HTML email ?
 

Jeffr.Lipton

Registered User.
Local time
Today, 08:14
Joined
Sep 14, 2018
Messages
31
One way is to "cheat" -- build your table in Word, then copy and paste into your email

Code:
            Dim appword As Word.Application
            Dim doc As Word.Document

            Set appword = New Word.Application
            appword.Visible = True

            Set doc = appword.Documents.Open(templateFile, , True)
            With doc
                .FormFields("txtPartNumber").result =Forms!QCform!QN
                .FormFields("txtReceived").result =Forms!QCform!Qty
'  .... and so on
                .Content.Copy
            End With
            
''SEND EMAIL

            Set appOutlook = CreateObject("outlook.application")
            Set message = appOutlook.CreateItem(olMailItem)
            With message
                .Subject = "[eIncoming - Autogenerated] - Part " & Forms!QCform!Part_Number & " rejected."

'

                Set Editor = .GetInspector.WordEditor
                Editor.Content.Paste
' 
' .... set other email fields as needed
                
                .Display
                .Send
            End With

            Set appOutlook = Nothing
            
' set the clipboard to something small to avoid message
            
            clipboard.SetText strContents
            clipboard.PutInClipboard

            
            doc.Close (wdDoNotSaveChanges)
            appword.Quit
            Set appword = Nothing
 

Users who are viewing this thread

Top Bottom