create an html table in outlook custom form 2010 using vba in MsAccess

Status
Not open for further replies.

DAVID POWELL

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Outlook 2010
access 2010

I have in the past opened a "outlook custom form" from Access.
and fill fields on the custom form with data.

What I need:.
Is it possible to create an HTML table on that Outlook custom form from Access?

I know how to create html table from access and load the data on an standard email...
just did not know if
VBSCRIPT was going to allow me to do that on an Outlook custom form ?

Thanks
fordraiders
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
You can do it using script - but you should just be able to change the type of new item and use the same steps as before, calling the form this way:
Set Items = Application.GetDefaultFolder(olFolderInbox).Items
Set Item = Items.Add("ipm.note.name")
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
BTW, when i read it the first time, i thought the Access code added the HTML to a standard form. After re-reading it, I'm thinking i read it wrong the first time - that you have the custom form part down but need to add the HTML code:
item.htmlbody = "<p>Whatever</p>"
if you create the table and assign it to a string before getting to the body field, use
item.htmlbody = stringName
This generally makes easier to read/understand code - i don't think it speeds it up any though.

if the message format is not html by default, use this before adding the body:
item.BodyFormat = olFormatHTML
 

DAVID POWELL

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Diane, Thanks for responding so quickly.
I'm currently in my Access app,
Looping through a access subform grabbing each row and placing the data in an html table on a standard email..

I was afraid the color coding of the cells in the html table may not work on a custom form.
and
so you are saying use the same concept and point to the custom form..


' EMAIL GOING TO managers

strhtml = "<HTML><Body><table border='1' width='50%'><tr><th>Sku</th><th>PRODUCT_DESCRIPTION</th><th>QTY</th>" & _
"<th>TARGET_PRICE</th><th>COMPETITOR_PRICE</th><th>TARGET_GP</th><th>CURRENT_PRICE</th><th>CURRENT_GP</th><th>VENDOR_GUIDELINE_GP</th><th>APPROVED_PRICE</th><th>APPROVED_GP</th></tr>"

' Initialize Mailbody
MAILBODY = strhtml



Dim gp As String

With rst
Do While Not .EOF

gp = .Fields(6)

If gp = "0.00" Then
MsgBox "Yes", vbCritical
End If


If gp >= "1.00" And gp <= "2.00" Then
MAILBODY = MAILBODY & "<tr><td>" & rst("SKU") & "</td><td>" & rst("PRODUCT_DESCRIPTION") & "</td><td>" & _
rst("QTY") & "</td><td>" & Format(rst("TARGET_PRICE"), "Currency") & "</td><td>" & Format(rst("COMPETITOR_PRICE"), "Currency") & _
"</td><td bgcolor=""Blue"">" & "No Gp" & "</td><td>" & Format(rst("CURRENT_PRICE"), "Currency") & "</td><td>" & Format(rst("CURRENT_GP"), "Percent") & "</td><td>" & Format(rst("VENDOR_GUIDELINE_GP"), "Percent") & "</td><td>" & Format(rst("APPROVED_PRICE"), "Currency") & "</td><td>" & Format(rst("APPROVED_GP"), "Percent") & "</td></tr>"
End If

.MoveNext
Loop

End With

''' ...code...creating email..

Set appOutLook = Outlook.Application

Set MailOutLook = appOutLook.CreateItem(olMailItem)
change the above to point to the custom form as i have before ?

Thanks
David
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Yes - change
Set MailOutLook = appOutLook.CreateItem(olMailItem)
to
Set MailOutLook= Items.Add("ipm.note.name")

Because you are using items.add, you will need to set a folder (even though it is going to be sent and doesn't really need a folder) - maybe the outbox instead of inbox (i didn't test it)
Set Items = Application.GetDefaultFolder(olFolderinbox).Items
 

DAVID POWELL

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Diane, still have a problem displaying HTML part in custome form.

I'm getting my textboxes filled on my custom form...but the html table and text is not being displayed.
Here are the key components to the code:


' variables for the email here
Dim strForm As String
Dim objFolder As Folder
Dim objItem As Object 'mailitem
Dim objPage As Variant 'Outlook.Pages

strForm = "IPM.Note.RsvpRedbookApproval"
Set objOL = CreateObject("Outlook.Application")
Set olns = objOL.GetNamespace("MAPI")
Set objFolder = olns.GetDefaultFolder(olFolderInbox)
Set objItem = objFolder.Items.Add("IPM.Note.RsvpRedbookApproval")
Set objPage = objItem.GetInspector.ModifiedFormPages("DsmEmail")


strhtml = "<HTML><Body><table border='1' width='50%'><tr><th>Sku</th><th>PRODUCT_DESCRIPTION</th><th>QTY</th>" & _
"<th>TARGET_PRICE</th><th>COMPETITOR_PRICE</th><th>TARGET_GP</th><th>CURRENT_PRICE</th><th>APPROVED_PRICE</th></tr>"

MAILBODY = strhtml


MAILBODY = MAILBODY & "<tr><td>" & rst("SKU") & "</td><td>" & rst("PRODUCT_DESCRIPTION") & "</td><td>" & _
rst("QTY") & "</td><td>" & Format(rst("TARGET_PRICE"), "Currency") & "</td><td>" & Format(rst("COMPETITOR_PRICE"), "Currency") & _
"</td><td bgcolor=""Red"">" & "No Gp" & "</td><td>" & Format(rst("CURRENT_PRICE"), "Currency") & "</td><td>" & Format(rst("APPROVED_PRICE"), "Currency") & "</td></tr>"


mybody = "Thank you for calling our Quotes Hotline.<br>" & _
"Your Pricing Escalation request has been sent to your Leader for review.<br><br>"

mybody = mybody & "Request #: " & price_escNum & " <br>" & _
"Quote #: " & esc_Quote & "<br>" & _
"Sharepoint #: " & SHAREPOINTID & "<br>" & _
"Price Requested Until : " & PRICE_REQUESTED & "<br>" & _
"Account #: " & esc_AccountNumber & "<br>" & _
"Account Name: " & esc_AccountName & "<br><br>"

mybody2 = "</table><p>Sincerely,<br><br>"
mybody2 = mybody2 & "The CSP Quotations Team.</p>"

' Set MailOutLook = appOutLook.CreateItem(olMailItem) ' commented out
Set MailOutLook = Items.Add("IPM.Note.RsvpRedbookApproval")

objItem.GetInspector.ModifiedFormPages("DsmEmail").Controls("txtPricingEscalationId").Value = price_escNum


With objItem
.SentOnBehalfOfName = "donotreply@redtions.com"
' .BodyFormat = olFormatRichText
.To = CALLER_NAME_EMAIL '"david.powell@grainger.com""
.CC = CALLER_DSM_EMAIL
'.CC = "redtions@xxxxx.com" ' sellers manager
.BCC = "redtions@xxxx.com"
.Subject = "Pricing #" & price_escNum & " has been escalated to your leader "
.BodyFormat = olFormatHTML
.HTMLBody = mybody & MAILBODY & mybody2
' .Display True
.Send
End With
 

DAVID POWELL

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Diane, I found part of my problem, I was not displaying the html table in the "Message" page.
I renamed (P.2) TO DsmEmail"
and that is where i'm trying to display the html table..unless you can tell me its possible to display html table on the other pages? p.2, or p.3 or p.4 etc... ?
 
Status
Not open for further replies.
Top