Solved

Need recordset in email body in a particular format

Posted on 2008-10-29
21
413 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

747 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

13 Experts available now in Live!

Get 1:1 Help Now