Solved

Need recordset in email body in a particular format

Posted on 2008-10-29
21
415 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

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.

Question has a verified solution.

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

Suggested Solutions

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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