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.
Thread starter Similar threads Forum Replies Date
Diane Poremsky Create a New Message using an HTML File or Stationery New Slipstick.com Articles 0
Diane Poremsky Create a New Message using an HTML File or Stationery New Slipstick.com Articles 0
R Using Outlook 2007 I have somehow turned on something that now I see the HTML tags when I create a new email. Using Outlook 3
S Create a clickable custom column field Outlook VBA and Custom Forms 0
O Create a custom contact form - questions before messing things up... Outlook VBA and Custom Forms 4
C Can't create Outlook data file Outlook Wishlist 0
L automaticaly create a teams meeting with a sync Using Outlook 0
D Can Exchange Admin Center create a pst for users email/contacts/calendar? Exchange Server Administration 0
S Create A Search Folder That Looks For Message Class? Outlook VBA and Custom Forms 0
F How to create phone number as links in notes of Contacts Using Outlook 2
Nessa Can't create new appointment Using Outlook 1
A Create date folder and move messages daily Outlook VBA and Custom Forms 1
C Create new Message with shared contacts & BCC'ing recipients Outlook VBA and Custom Forms 0
O Multiple email accounts - hesitate to create a new profile Using Outlook 3
G Can't create Folder Groups in Outlook 2013 Using Outlook 0
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
F Delete/create/reset Exchange mailbox on Outlook.com Using Outlook.com accounts in Outlook 3
R Can not create folder to store specific emails in in Outlook for Mac Using Outlook 1
W Create Search Folder excluding Specific Email Addresses Using Outlook 5
A Outlook macro to create search folder with mail categories as criteria Outlook VBA and Custom Forms 3
K VBA BeforeItemMove event create rule to always move to its folder. Outlook VBA and Custom Forms 4
JackBlack What tools do you use to create the signature for email? Using Outlook 3
Rupert Dragwater How to create a new email with @outlook.com Using Outlook.com accounts in Outlook 32
F Should a new email account also create new contacts Using Outlook 2
R Outlook add-in to create new contact from an email. Using Outlook 0
Tanja Östrand Outlook 2016 - Create Macro button to add text in Subject Outlook VBA and Custom Forms 1
Q Script to create a pst file for Archiving Using Outlook 1
Jennifer Murphy Can I create a Rule with Or'd conditions? Using Outlook 1
D Outlook macros to create meeting on shared calendar Outlook VBA and Custom Forms 10
G How do I create a custom pick list in VB for an outlook automated email? Outlook VBA and Custom Forms 1
L Create Custom Form Using Outlook 0
Diane Poremsky Create a Task when a Message is Flagged New Slipstick.com Articles 0
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
Diane Poremsky Create a custom field for Outlook messages New Slipstick.com Articles 0
Mark White vba to create a shared mailbox folder Outlook VBA and Custom Forms 3
Diane Poremsky Automatically create a task when sending a message New Slipstick.com Articles 0
Diane Poremsky Create a Custom Numbering Field for Outlook messages New Slipstick.com Articles 0
Diane Poremsky Create a Series of Tasks using VBA New Slipstick.com Articles 0
J create .pst data file for new gmail account Using Outlook.com accounts in Outlook 1
Diane Poremsky Create an Organizational Forms Library New Slipstick.com Articles 0
Diane Poremsky Create an Organizational Forms Library in Exchange 2013 or Office 365 New Slipstick.com Articles 0
Jeanne Goodman Create Printout from Multiple Shared Calendars Outlook VBA and Custom Forms 8
Diane Poremsky Manually create a POP3 account in Outlook 2007 New Slipstick.com Articles 0
T Create Rule For Secondary E-Mail Address In Outlook 2016 Using Outlook 4
B Macro To Create Rule To Export From Certain Folder Email Information in one workbook multiple sheets Outlook VBA and Custom Forms 0
Diane Poremsky Create Appointments Using Spreadsheet Data New Slipstick.com Articles 0
A VBA to create meeting from template from a time slot selected in someone's calendar Outlook VBA and Custom Forms 5
G Create an Appointment at the Contact's Address From Email Outlook VBA and Custom Forms 0
Diane Poremsky Use VBA to create an Outlook Search Folder for Sender New Slipstick.com Articles 0
Diane Poremsky Create a Task from an Email using a Rule New Slipstick.com Articles 0
Similar threads


















































Top