Pass data field value to html table

megatronixs

Registered User.
Local time
Today, 18:42
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.
 

Users who are viewing this thread

Back
Top Bottom