Solved

Need recordset in email body in a particular format

Posted on 2008-10-29
21
414 Views
Last Modified: 2013-11-25
I have fainlly gotten mydata into the body of the mail and then discovered it wapped around.  So, what would I do to get the results similar to the below?  there are more columsn, but this is the general idea

BRN_NO   ACCT_NM              OFCR_CD       PLAN_NMR       DAYS_OPEN_CT      
  95            111111111               DSH                  101                     32
REDL_CD    NAME_FLG    DOB_FLG        PHONE_FL
 101             M                      M                            M

BRN_NO   ACCT_NM              OFCR_CD       PLAN_NMR       DAYS_OPEN_CT      
  95            2222222222            DSH                  101                     32
REDL_CD    NAME_FLG    DOB_FLG        PHONE_FL
 101             M                      M                            M

BRN_NO   ACCT_NM              OFCR_CD       PLAN_NMR       DAYS_OPEN_CT      
  95            7894561311            DSH                  101                     32
REDL_CD    NAME_FLG    DOB_FLG        PHONE_FL
 101             M                      M                            M
Private Sub ActualEmail()

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

Dim strSubject As String, fld As DAO.Field

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)

    strMessage = Me.txtBody & vbCrLf & vbCrLf

    Set appOutLook = CreateObject("Outlook.Application")

    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    With MailOutLook

        .To = txtMaxBranch

        .Subject = Me.txtSubject

        .Body = strMessage & vbCrLf & vbCrLf

            For Each fld In rst.Fields

              strMessage = strMessage & FS(fld.Name, 20)

            Next

            strMessage = strMessage & vbCrLf

            

            Do Until rst.EOF

              For Each fld In rst.Fields

                 strMessage = strMessage & FS(Nz(fld.Value, ""), 20)

              Next

              strMessage = strMessage & vbCrLf

              rst.MoveNext

            Loop

            

            'Still need to set up e-mail addresses, but need to get body working first.

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

         .Send

       End With

End Sub

Open in new window

0
Comment
Question by:ssmith94015
  • 5
  • 5
  • 5
  • +2
21 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 125 total points
ID: 22835832
You can't.
Any mail reader will make a word wrap on long lines, typically above 78 to 80 characters.

/gustav
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 125 total points
ID: 22835923
Agree with gustav.

Unless you are building highly typed/controlled HTML message that you know your recipients will get as HTML there is no good way to do it.
0
 
LVL 7

Expert Comment

by:UniqueData
ID: 22836020
Now what you could do is create a word document, open a new email and then attatch the word document. All through automation. You would have to build the word document with the bookmarks and name the bookmarks so they make sense and then you would open the word document when you send the email. That way you have more control over the way it is formated.
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 125 total points
ID: 22836028
Hello ssmith94015,

For reasons already stated this does not work well as the body of an email message.  However, why not
create a file (Excel or text) that has the data presented the way you like it, and then have your code include
that file as an attachment?

Regards,

Patrick
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22836035
I see I must have been channeling UniqueData there as I typed :)

Word is a possibility; I tend to prefer plain text or Excel mostly because I've never gotten comfortable
with Word's object model.
0
 
LVL 7

Expert Comment

by:UniqueData
ID: 22836327
Matthew, You dont really use much of the word object because you are opening an existing word document and just filling pointing the data to a bookmark. I have that code somewhere. I have been trying to find it. when I find it i will share.
0
 
LVL 7

Assisted Solution

by:UniqueData
UniqueData earned 125 total points
ID: 22836428
Here are a few I have used. I have this in a module. Then I call it from wherever I am in code passing the recordset.
Option Compare Database

Option Explicit
 

Private Const m_strDIR          As String = "C:\BegVBA\"

Private Const m_strTEMPLATE     As String = "Order.dot"
 

Private m_objWord               As Word.Application

Private m_objDoc                As Word.Document
 

Public Sub CreateOrderLetter(recSupp As Recordset, recItems As Recordset)
 

    ' instantiate the word application and create a new

    ' document based upon the supplied template

    Set m_objWord = New Word.Application

    Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)
 

    ' insert the customer details

    InsertTextAtBookMark "ContactName", recSupp("ContactName")

    InsertTextAtBookMark "CompanyName", recSupp("CompanyName")

    InsertTextAtBookMark "Address", recSupp("Address")

    InsertTextAtBookMark "City", recSupp("City")

    InsertTextAtBookMark "State", recSupp("State")

    InsertTextAtBookMark "ZipCode", recSupp("ZipCode")

    InsertTextAtBookMark "Country", recSupp("Country")
 

    ' now the order items

    InsertItemsTable recItems
 

    ' print the order, not using background printing

    ' otherwise code continues and we try to quit whilst still printing

    m_objWord.PrintOut Background:=False
 

    ' now save and quit

    m_objDoc.SaveAs FileName:=m_strDIR & recSupp("CompanyName") & _

        " - " & FormatDateTime(Date, vbLongDate) & ".DOC"

    m_objDoc.Close

    m_objWord.Quit
 

    ' clean up

    Set m_objDoc = Nothing

    Set m_objWord = Nothing
 

End Sub
 

Private Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)
 

    ' selects the bookmark and inserts the text

    m_objDoc.Bookmarks(strBkmk).Select

    m_objWord.Selection.Text = varText & ""
 

End Sub
 

Private Sub InsertItemsTable(recR As Recordset)
 

    Dim strTable        As String

    Dim objTable        As Word.Table
 

    ' create columns separated by tabs

    ' it's easier to convert this to a table than to create

    ' a table and worry about the table cells

    strTable = "Item" & vbTab & "Quantity" & vbCr

    recR.MoveFirst

    While Not recR.EOF

        strTable = strTable & recR("Name") & vbTab & _

            recR("ReOrderPoint") & vbCr

        recR.MoveNext

    Wend
 

    ' now insert the text, convert it to a table, and format it

    InsertTextAtBookMark "Items", strTable

    Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)

    With objTable

        .AutoFormat Format:=wdTableFormatClassic3, AutoFit:=True, _

            ApplyShading:=False

    End With
 

    ' clean up

    Set objTable = Nothing
 

End Sub

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22836441
Boy, Word for this, would that be overkill.

/gustav
0
 
LVL 7

Expert Comment

by:UniqueData
ID: 22836515
gustav, maybe but it will be formated in a way that looks good. I have not really found a way to make a body of an email "pretty". If anybody knows how to make it nicer without getting this involved I know I would like to use it.
0
 

Author Comment

by:ssmith94015
ID: 22836583
Wow, thanks for the response.  The problem is the user does not want an attachment.  Right now they copy/paste from Excel into the body but I was wondering if HTML would work, only having the 25 columms in sets of five, that is, for each row, have a return after the first five fields taht have the header and the data, and then the next line have the next five field with their data below.   Does that even sound possible?  I don't know XML/HTML/ but if it is possible, I can sure get up to speed.  I jsut don't want to go down a path that is not feasible.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22838599
An attachment _is_ the solution.
A simple csv file can be both created and read by Excel very easily.

/gustav
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22840097
You could do five different recordsets and then append the headers into the string. But the problem is that the alignment would be horrible.

The typical e-mail SW uses a variable width font such as Times New Roman or Arial or something like that.

So if you have one user who sets theirs up to use comic sans versus a user who has arial versus another who uses time new roman.

An example is in the attached word doc.
Font-Example.doc
0
 

Author Comment

by:ssmith94015
ID: 22840829
You all have given me some ideas that I am going to try today.  Sorry for the response delay, had to sleep sometime this week and last night was it.  Let you all know how it goes.  It's going to be a long day......
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22841227
>> had to sleep sometime this week and last night was it.

Been there, done that, got the t-shirt. ;-) No problem.
0
 

Author Comment

by:ssmith94015
ID: 22846168
After struggling with this I have come to the conculsion that some of your are correct in that it cannot be done  -  or if done it is MESSY.  I tried to go through some HTML ideas, but I do not know enough about it to accomplish what I need to do.  I tried concatenating with & and returning after five columns with the data beneath, worked up to a point but was UGLY and kept crashing.  User is going to have to have an excel attachment for now.  

Thank you all, this has really been a learning experience!  I am going to spread the points around as everyone had a worthwhile opinion.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22846225
Now, you don't state who to mail these attachments, but have in mind that many spam and virus filters reject e-mails with an attached xls file.

/gustav
0
 

Author Comment

by:ssmith94015
ID: 22848608
Gustav, this is in a financial institution and internally, excel files go round like May flies.  I still have not given up on the HTML idea, but simply don't have the time to learn enough to get going.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 22848914
OK, then no problem. Excel is fine for this and users know what it is about.
If at all possible, I would stick to that.

Of course, the data can be formatted nicely in HTML, but trouble may arise when users are going to copy and paste this.

/gustav
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22849557
>> but have in mind that many spam and virus filters reject
>> e-mails with an attached xls file.

Not quite correct -- most spam filters will let office docs through with no problem as long as they don't have macros/vb code in them. Those will get stripped/blocked in a heartbeat. We constantly have the problem from external entities who coded their stuff into an excel SS.
0
 

Author Comment

by:ssmith94015
ID: 22850000
Actually, I finally figured out and adapted an acceptable solution from all the suggestions I received  Using the field name and value I got it to loop through the records in the row, but eliminating the fields that have nulls.  So, rather than getting 25 fields, most of which are empty, they now get two columns of data - a very nice column with the field name and then spaced neatly right next to it the field's data - receiving only the information they need - which is about 10 fields rather than 25 with 15 empty cells..  
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22850850
I'm glad we could help.

May all your days get brighter and brighter.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

911 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

22 Experts available now in Live!

Get 1:1 Help Now