Send email via access with table included

**onlylonely** · 11-22-2017, 08:01 AM

Hi Guys,

I would like to use form to send an email with table on the email body.
May i know how to include table into the email body?

For example:

Hi All,

Please review 123 and feedback to supplier for repeating reject.

Part	Rej Qty	Supplier
123	12	Apple

Code:

Public Function Email3()Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")


Set oMail = oApp.CreateItem(olMailItem)
oMail.Subject = "[eIncoming] - Failed 100% Inspection on " & PartNumber & ""
oMail.Body = "Hi All," & vbCrLf & vbCrLf & "Please review " & PartNumber & " and feedback to supplier for repeating reject." & vbCrLf & vbCrLf & "Regards, " & vbCrLf & "abc"
oMail.To = "abc@abc.com"
oMail.Send
Set oMail = Nothing
Set oApp = Nothing

**June7** · 11-22-2017, 08:06 AM

Requires HTML code tags to construct table structure and Outlook automation to send, not DoCmd.SendObject. Review http://www.codekabinett.com/rdumps.p...ss-VBA-outlook and https://excellenttips.wordpress.com/...rosoft-access/

**onlylonely** · 11-22-2017, 05:41 PM

Originally Posted by June7

Requires HTML code tags to construct table structure and Outlook automation to send, not DoCmd.SendObject. Review http://www.codekabinett.com/rdumps.p...ss-VBA-outlook and https://excellenttips.wordpress.com/...rosoft-access/

Hi June,

Thanks for your reply.
I'm following exactly one of those. But still couldn't get it.
Can you please help me to take a look?

Code:

Option Compare DatabasePublic Sub SendMail()


    Dim myMail      As Outlook.MailItem
    Dim myOutlApp   As Outlook.Application
    Dim att         As Outlook.Attachment


    ' Creating an Outlook-Instance and a new Mailitem
    Set myOutlApp = New Outlook.Application
    Set myMail = myOutlApp.CreateItem(olMailItem)


    With myMail
        ' setting the recipient
        .To = "abc@abc.com"
        
        ' defining a subject for the mail
        .Subject = "An email with CSS formatting and embedded image created by Outlook automation"
        
        
        <html>
        <body>
        <p> Morning world! </p>
        </body>
        </html>
        ' Setting BodyFormat and body-text of the mail
        .BodyFormat = olFormatHTML
        .HTMLBody = GetHTMLText
        
        ' Adding an attachment from filesystem
        'Set att = .Attachments.Add("D:\tmp\DummyBarcode.jpg")
        ' Setting the PR_ATTACH_CONTENT_ID property to "DummyBarcode.jpg"
        'att.PropertyAccessor.SetProperty "http://schemas.microsoft.com/mapi/proptag/0x3712001F", "DummyBarcode.jpg"
                
        ' sending the mail
        .Send
        ' You can as well display the generated mail by calling the Display-Method
        ' of the Mailitem and let the user send it manually later.
    End With


    ' terminating the Outlook-Application instance
    myOutlApp.Quit


    ' Destroy the object variables and free the memory
    Set myMail = Nothing
    Set myOutlApp = Nothing


End Sub

**June7** · 11-22-2017, 07:49 PM

If you put the HTML code in VBA then it all must be enclosed within quote marks and populate a variable as shown in the second link.

strHTML = "<html><body><p> Morning world! </p></body></html>"

Your code is mixing the two examples. GetHTMLText is from the first link. Pick one to follow.

**onlylonely** · 11-22-2017, 11:08 PM

Originally Posted by June7

If you put the HTML code in VBA then it all must be enclosed within quote marks and populate a variable as shown in the second link.

strHTML = "<html><body><p> Morning world! </p></body></html>"

Your code is mixing the two examples. GetHTMLText is from the first link. Pick one to follow.

Hi June,

I'm now stuck with something.
My expectation is like below attachment send via email.
But it only appear to have 1 row when send to my email.
Appreciate if you could take a look on it.

**June7** · 11-23-2017, 02:16 AM

This works for me:

Code:

Dim myMail      As Outlook.MailItem
Dim myOutlApp   As Outlook.Application
Dim att         As Outlook.Attachment
Dim strHTML As String
Dim rst As DAO.Recordset
Set myOutlApp = New Outlook.Application
Set myMail = myOutlApp.CreateItem(olMailItem)
Set rst = CurrentDb.OpenRecordset("SELECT PartNumber, Qty FROM TABLE1", dbOpenSnapshot)
With myMail
    Do Until rst.EOF
        strHTML = strHTML & "<tr><td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rst![PartNumber] & "</td>"
        strHTML = strHTML & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rst![Qty] & "</td></tr>"
        rst.MoveNext
    Loop
    .HTMLBody = "<!DOCTYPE html><html><body><table><tr><td>PartNumber</td><td>Qty</td></tr>" & strHTML & "</table></body></html>"
    .To = "abc@abc.com"
    .Subject = "An email with CSS formatting and embedded image created by Outlook automation"
    .BodyFormat = olFormatHTML
'    .send
    .display
End With

**onlylonely** · 11-23-2017, 05:32 AM

Originally Posted by June7

This works for me:

Code:

Dim myMail      As Outlook.MailItem
Dim myOutlApp   As Outlook.Application
Dim att         As Outlook.Attachment
Dim strHTML As String
Dim rst As DAO.Recordset
Set myOutlApp = New Outlook.Application
Set myMail = myOutlApp.CreateItem(olMailItem)
Set rst = CurrentDb.OpenRecordset("SELECT PartNumber, Qty FROM TABLE1", dbOpenSnapshot)
With myMail
    Do Until rst.EOF
        strHTML = strHTML & "<tr><td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rst![PartNumber] & "</td>"
        strHTML = strHTML & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & rst![Qty] & "</td></tr>"
        rst.MoveNext
    Loop
    .HTMLBody = "<!DOCTYPE html><html><body><table><tr><td>PartNumber</td><td>Qty</td></tr>" & strHTML & "</table></body></html>"
    .To = "abc@abc.com"
    .Subject = "An email with CSS formatting and embedded image created by Outlook automation"
    .BodyFormat = olFormatHTML
'    .send
    .display
End With

Thanks June! This is what i'm looking for!
But how to make the PartNumber and Qty inside the table ?
I mean inside the box.

**June7** · 11-23-2017, 01:14 PM

The headers are within the table, just not formatted. Use the same formatting codes in the <td> tag if you want.

**onlylonely** · 11-25-2017, 05:38 PM

Originally Posted by June7

The headers are within the table, just not formatted. Use the same formatting codes in the <td> tag if you want.

Thanks June7 ! I got it !

Send email via access with table included

Thread Tools

Send email via access with table included

Similar Threads

How To Send Bulk Email With There Relevant Data From Ms Access Table, Access 2007

Send email using CDO, get email addresses from table

How can I send an email from access???

VBA to Send email from Access

Send email from Access DB Table

Posting Permissions