Pass data field value to html table

M

megatronixs

Registered User.
Local time
Today, 15:15
Joined
Aug 17, 2012
Messages
719
Hi all,

I have code that will create an email and prepare it for sending.
It will create a table inside the email and fill it in with some text and underscore characters to be replaced by data from the database. So far the data has to be done manually. I would like to know if it is possible to use the values from some fields inside the select record in the current form. So, if Me.Status would be "New" it should pass this to the table in the email.
So far I have the below code:

Code: 
[SIZE=3][FONT=Calibri]Private Sub Command280_Click()  'send email with table[/FONT][/SIZE]
[SIZE=3][FONT=Calibri] Dim objItem As Object[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim oMail As Outlook.MailItem[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Set oMail = objItem[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim oApp  As Object[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Dim BIN As String        ‘field Me.BIN[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim Name As String   ‘field Me.Name[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim Rating As String   ‘field Me.Rating[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim Status As String   ‘field Me.Status[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Set oApp = CreateObject("Outlook.application"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set oMail = oApp.CreateItem(olMailItem)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  oMail.HTMLBody = "<html> <head> <Font Size= 1><style> table, th, td </style> </head> <body> <br><br><br><br><br><br><p>Dear reader,</p><p>it is important that the table is not altered in any other way.<br>Changes can only be made for the status.</p><p>Regards</p> <TABLE><TR><TD>Rating:</TD><TD>-----</TD></TR><TR><TD>BIN nr:</TD><TD>-----</TD></TR><TR><TD>Name:</TD><TD>-----</TD></TR><TR><TD>Status:<TD> & Me.Status &</TD></TABLE> </body> </html>"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   oMail.Subject = "Status on case with BIN nr: "[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   oMail.To = ""[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   oMail.BodyFormat = 3[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   oMail.Display[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]Debug.Print Risk_Rating[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Set oMail = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Set oApp = Nothing[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]

Greetings.
 
Hi,

Is there not an easier way to do? maybe a small sample for the above code (I'm still learning and the link is more confusing in how to implement it).

Greetings.
 
Is it just from the form you want to send email? Or for multiple records? If it is only form, you do not need Recordset. Simply this should cut it.

Code: 
Private Sub Command280_Click()  'send email with table
    Dim objItem As Object
    Dim oMail As Outlook.MailItem
    Set oMail = objItem
    Dim oApp  As Object

    Dim BIN As String       ‘field Me.BIN
    Dim NameStr As String       ‘field Me.Name
    Dim Rating As String       ‘field Me.Rating
    Dim Status As String       ‘field Me.Status
    
    Set oApp = CreateObject("Outlook.application"
    Set oMail = oApp.CreateItem(olMailItem)
    
    oMail.HTMLBody = "<HTML><HEAD><Font Size= 1><style> table, th, td </style> </HEAD> <BODY><br><br><br><br><br><br><p>" & _
                     "Dear reader,</p><p>it is important that the table is not altered in any other way.<br>" & _
                     "Changes can only be made for the status.</p><p>Regards</p>" & _
                     "<TABLE><TR><TD>Rating:</TD><TD>" & [B][COLOR=Red]Rating [/COLOR][/B]& "</TD></TR><TR>" & _
                     "<TD>BIN nr:</TD><TD>" & [COLOR=Red][B]BIN [/B][/COLOR]& "</TD></TR><TR><TD>Name:</TD>" & _
                     "<TD>" & [B][COLOR=Red]NameStr [/COLOR][/B]& "</TD></TR><TR><TD>Status:<TD> & [B][COLOR=Red]Status[/COLOR][/B]  &</TD></TABLE> </BODY> </HTML>"
    
    oMail.Subject = "Status on case with BIN nr: " & [COLOR=Red][B]BIN[/B][/COLOR]
    oMail.To = ""
    oMail.BodyFormat = 3
    oMail.Display

    Debug.Print Risk_Rating

    Set oMail = Nothing
    Set oApp = Nothing
End Sub
 
WOOOW,

Big thanks :-) it is workig great (and it was simpler than I could think of)

Greetings.
 
You must log in or register to reply here.

Similar threads

U
Export blob OLE Object Field Item instead of Attachment Field
Replies
9
Views
671
jdraw
jdraw
U
Solved Exporting Image Files from Table - Having File Naming Issues
Replies
11
Views
359
GaP42
G
N
VBA for populating a subform from data in a listbox
Replies
12
Views
712
June7
June7
D
Solved How to sort values (the highest first)? If duplicates values found, the sorting is based on the saved table for those values
Replies
13
Views
891
jdraw
jdraw
S
Join or match data on text within string from two tables
Replies
2
Views
591
sxschech
S

Users who are viewing this thread

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