Email with table

O

Onlylonely

Registered User.
Local time
Today, 08:51
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
 
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:
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: 123
The methods I suggested will give a tabular layout but without the table grid lines
 
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
 
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: 106
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.
 
I've tried but not success. It still the same as previous.
 
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.
Click to expand...

I did mention (unintentionally) that the SQL code I utilise is CSS based... Has anyone tried using CSS in an Outlook HTML email ?
 
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
 
You must log in or register to reply here.

Similar threads

C
Invalid Use of Null
2
Replies
20
Views
1,042
chuckcoleman
C
D
Solved Sending email using outlook
Replies
7
Views
976
donkey9972
D
U
Send emails to Unique users in Range
Replies
5
Views
1,250
Donnie1ben
D
T
Use a Form Text Box for Email Address in VBA Function
Replies
7
Views
1,472
usama_hawas
U
R
Problems with linked table from compacted excel pivot table
Replies
1
Views
487
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom