Access to outlook body questions.

R

rgwfly

Registered User.
Local time
Yesterday, 22:48
Joined
Jun 7, 2016
Messages
49
After searching I finally came up with a script to put table information in the body of an outlook email. I have a few questions to enhance this.
1, The last field aRow(8) needs to have a percent format.
2, I would like at somepoint to be able to add another table to the same email. I suume this may be a bit difficult.
Anyway this does work pretty well as it stands. Open to improvements though.
I also thought this script may help someone else who struggled with the code that works.

Thanks
Code: 
Option Compare Database
Private Sub btnEmail_Click()
'On Error GoTo Errorhandler
    
    Dim olApp As Object
    Dim olItem As Variant
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim strQry As String
    Dim aHead(1 To 8) As String
    Dim aRow(1 To 8) As String
    Dim aBody() As String
    Dim lCnt As Long
    'Create the header row
    aHead(1) = "ProduOrd"
    aHead(2) = "Desc"
    aHead(3) = "PersoName"
    aHead(4) = "HoursPosted"
    aHead(5) = "OpTextDescr"
    aHead(6) = "Plan_Hrs"
    aHead(7) = "Booked"
    aHead(8) = "Perf"
    lCnt = 1
    ReDim aBody(1 To lCnt)
    aBody(lCnt) = "<HTML><body><table border='2'><tr><th>" & Join(aHead, "</th><th>") & "</th></tr>"
    'Create each body row
    strQry = "SELECT * From tblTimePostPrevDay"
    Set db = CurrentDb
    Set rec = CurrentDb.OpenRecordset(strQry)
    If Not (rec.BOF And rec.EOF) Then
        Do While Not rec.EOF
            lCnt = lCnt + 1
            ReDim Preserve aBody(1 To lCnt)
            aRow(1) = rec("ProdOrd")
            aRow(2) = rec("Description")
            aRow(3) = rec("PersName")
            aRow(4) = rec("HrsPosted")
            aRow(5) = rec("OpTextDesc")
            aRow(6) = rec("PlanHrs")
            aRow(7) = rec("booked")
            aRow(8) = rec(Format("perf", "percent"))
            aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
            rec.MoveNext
        Loop
    End If
    aBody(lCnt) = aBody(lCnt) & "</table></body></html>"
    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.CreateItem(0)
    olItem.display
    olItem.To = "[EMAIL="productionmanager@yahoo.com"]productionmanager@yahoo.com[/EMAIL]"
    olItem.Subject = "Production Status Report"
    olItem.htmlbody = Join(aBody, vbNewLine)
    olItem.display
End Sub
 
That looks pretty neat.

You can easily add another table by simply repeating your whole code and concatenate a second HtmlBody string.

As a word of warning - you may want to wrap the individual field (array) elements in a Nz(Field ,"") functions to avoid empty results which will mess your formatting up.
 
Thanks Minty,
I actually did run into the null field error. I fixed it in the query to not be null.
But your suggestion is much better.
So the field would look like:
'rec(nz("planHrs","")'?
Still a bit of a newby..
 
Nearly - The syntax is Nz(Expression,ValueIfNull) so for your arrays it would be
Code: 
  aRow(3) = Nz(rec("PersName"),"")
 
@ rgwfly were you able to add a second table to the email? if so could you share code? Also does any one know how to make the table prettier?
 
afiddes82 said:
@ rgwfly were you able to add a second table to the email? if so could you share code? Also does any one know how to make the table prettier?
Click to expand...
Hi. Welcome to AWF! You are replying to an old thread. To dress up the table, try using html tags.
 
Thanks @theDBguy, I will try HTML tags, what I am really wondering is how to add a second table to the same email as referenced above..
"You can easily add another table by simply repeating your whole code and concatenate a second HtmlBody string. "
I don't know how to do it, and its driving me crazy.. Thanks Andy
 
afiddes82 said:
Thanks @theDBguy, I will try HTML tags, what I am really wondering is how to add a second table to the same email as referenced above..
"You can easily add another table by simply repeating your whole code and concatenate a second HtmlBody string. "
I don't know how to do it, and its driving me crazy.. Thanks Andy
Click to expand...
Hi Andy. I didn't review the code, but let's say you had a table like so:

strTable = "<table><tr><td>Hello World!</td></tr></table>"

Then, to add another table, you just repeat the above.

strNewTable = "<table><tr><td>Hello Again</td></tr></table>"

Then, in the email, you can include both tables.

.HTMLBody = strTable & strNewTable

Hope that helps...

PS. If you need more help, may I suggest posting a new thread to give more people a chance to assist you. Cheers!
 
To keep tabs on things and make your life easy for debugging, create three variables;

Dim sHTMLTable1 as string
Dim sHTMLTable2 as string
Dim sFinalHTML as string

Then create the strings;

sHTMLTable1 = " blah blah blah clever table 1"

more code

sHTMLTable2 = " blah blah blah clever table 2"

then finally

sFinalHTML = sHTMLTable1 & sHTMLTable2

and sFinalHTML is what you chuck into your outlook email
 
Keep getting type mismatch error when I try to put the two tables into the email..
I am going to create a new post, as suggested above.. thanks!
 
afiddes82 said:
Keep getting type mismatch error when I try to put the two tables into the email..
I am going to create a new post, as suggested above.. thanks!
Click to expand...
Good luck!
 
You must log in or register to reply here.

Similar threads

D
  • Question Question
Send an outlook email to each record in the table in a table format
Replies
4
Views
1,325
Donnie1ben
D
D
  • Question Question
End if without block if Error
2
Replies
22
Views
3,661
Donnie1ben
D
U
Export blob OLE Object Field Item instead of Attachment Field
Replies
9
Views
1,003
jdraw
jdraw
U
Solved Exporting Image Files from Table - Having File Naming Issues
Replies
11
Views
684
GaP42
G
M
Solved VBA access to outlook email with table: aligning table cells
Replies
5
Views
2,809
miacino
M

Users who are viewing this thread

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