Solved

Get row headers in email for data from ACCESS 2003

Posted on 2008-10-29
3
176 Views
Last Modified: 2013-11-25
I am working on getting a email automation out of our database.  The first part of the body is standard text depending on how many days an account has been opened and then below that in the email are the various rows with customer data the manager has to review (they do NOT want an attachment, they want the data in the e-mail itself)  I have managed to get to the point where the body and rows are in the email, but how do I get the column headers for the rows information?  right now,just the data appears, not the column titles.
Private Sub ActualEmail()

Dim strMessage As String, txtMaxBranch As String, strTo As String

Dim strSubject As String

Dim appOutLook As Outlook.Application, MailOutLook As Outlook.MailItem

Dim strAddress As String, rstEmail As DAO.Recordset

txtMaxBranch = DMax("BR_NMR", "tblAMLUploadedToday")

Set db = CurrentDb

Set rst = db.OpenRecordset("qryEmail")
 

Set appOutLook = CreateObject("Outlook.Application")

Set MailOutLook = appOutLook.CreateItem(olMailItem)

'strAddress = Me.txtEmailAddress

rst.MoveFirst

            strMessage = Me.txtBody & vbCrLf & vbCrLf

            Set appOutLook = CreateObject("Outlook.Application")

            Set MailOutLook = appOutLook.CreateItem(olMailItem)

            With MailOutLook

                .To = txtMaxBranch

                .Subject = Me.txtSubject

                .Body = strMessage

Debug.Print strMessage

' *****************

Do Until rst.EOF

If strMessage = "" Then

strMessage = rst("BR_NMR") & "," & rst("OFCR_CD") & "," & rst("PLAN_NBR") & "," & rst("DAYS_OPEN_CNT") & "," & rst("APPL_TYP_CD") & "," & rst("ACCT_NBR") & vbCrLf & _

    "," & rst("REL_CD") & "," & rst("NM_LINE_1") & "," & rst("NM_LINE_2") & "," & rst("NM_LINE_3") & "," & rst("TAX_ID_NBR") & "," & rst("CIF_NBR") & vbCrLf & _

    "," & rst("NAME_FLG") & "," & rst("TAX_ID_FLG") & "," & rst("DOB_CIF_FLG") & "," & rst("DOB_ACCT_FLG") & "," & rst("GOVMT_ID_FLG") & "," & rst("ADDR_FLG") & vbCrLf & _

    "," & rst("PO_BOX_FLG") & "," & rst("PHONE_FLG") & "," & rst("OFCF_CD_FLG") & "," & rst("OPEN_DT") & "," & rst("OWNR_CD") & "," & rst("MOTHER_NM_FLG") & vbCrLf & _

    "," & rst("OCCUPATION_FLG") & "," & rst("EMPLR_FLG") & vbCrLf

Else

strMessage = strMessage & rst("BR_NMR") & "," & rst("OFCR_CD") & "," & rst("PLAN_NBR") & "," & rst("DAYS_OPEN_CNT") & "," & rst("APPL_TYP_CD") & "," & rst("ACCT_NBR") & vbCrLf & _

    "," & rst("REL_CD") & "," & rst("NM_LINE_1") & "," & rst("NM_LINE_2") & "," & rst("NM_LINE_3") & "," & rst("TAX_ID_NBR") & "," & rst("CIF_NBR") & vbCrLf & _

    "," & rst("NAME_FLG") & "," & rst("TAX_ID_FLG") & "," & rst("DOB_CIF_FLG") & "," & rst("DOB_ACCT_FLG") & "," & rst("GOVMT_ID_FLG") & "," & rst("ADDR_FLG") & vbCrLf & _

    "," & rst("PO_BOX_FLG") & "," & rst("PHONE_FLG") & "," & rst("OFCF_CD_FLG") & "," & rst("OPEN_DT") & "," & rst("OWNR_CD") & "," & rst("MOTHER_NM_FLG") & vbCrLf & _

    "," & rst("OCCUPATION_FLG") & "," & rst("EMPLR_FLG") & vbCrLf & vbCrLf

End If

Debug.Print strMessage

rst.MoveNext

Loop
 

DoCmd.SendObject , , , strTo, , , strSubject, strMessage, 1  'Move outside the loop
 

'

''***********************

                 .Send

               End With

'skip_email:

'        rst.MoveNext

'    Loop

'    rst.Close

'    Set rst = Nothing
 

End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22833997
You can get the actual name of the field like this:

rst("BR_NMR").Name

0
 

Author Comment

by:ssmith94015
ID: 22834128
would this be the first line in the message part that holds the row data?
0
 

Author Closing Comment

by:ssmith94015
ID: 31511272
I figured out the placement, thank you.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now