Access to outlook body questions. (1 Viewer)

rgwfly · Aug 11, 2017

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

Minty · Aug 11, 2017

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.

rgwfly · Aug 11, 2017

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..

Minty · Aug 11, 2017

Nearly - The syntax is Nz(Expression,ValueIfNull) so for your arrays it would be

Code:

  aRow(3) = Nz(rec("PersName"),"")

afiddes82 · Apr 16, 2020

@ 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?

theDBguy · Apr 16, 2020

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?

Hi. Welcome to AWF! You are replying to an old thread. To dress up the table, try using html tags.

afiddes82 · Apr 16, 2020

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

theDBguy · Apr 16, 2020

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

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!

Minty · Apr 16, 2020

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

afiddes82 · Apr 16, 2020

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!

theDBguy · Apr 16, 2020

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!

Good luck!

Access to outlook body questions. (1 Viewer)

rgwfly

Registered User.

Minty

AWF VIP

rgwfly

Registered User.

Minty

AWF VIP

afiddes82

New member

theDBguy

I’m here to help

afiddes82

New member

theDBguy

I’m here to help

Minty

AWF VIP

afiddes82

New member

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread