Information to Email NOT as attachment

[Timoty]

I have information on a form that I want put into an email. However, I can't do the way that I usually do it because a good number of people are carrying blackberries and some of the ones with older phones can't view attachmnets. Therefore, I want to be able to bring the information directly (or indirectly) into the body of the email. My several feeble attempts have failed. This is what I have at this time. Any ideas would be appreciated.

Private Sub Command36_Click()
Dim stDocName As String
Dim EmailApp, NameSpace, EmailSend As Object

DoCmd.OutputTo acReport, "Email", acFormatTXT, "h:\email.txt"

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.getNameSpace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.To = "persons name here"
EmailSend.subject = "EMERGENCY"
'EmailSend.Message = "h:\email.txt" (THIS FAILED)
'EmailSend.Attachments.Add "h:\email.txt" (THIS WORKS BUT I CAN'T USE ATTACHMENTS)
EmailSend.Display

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

Kill "h:\email.txt"

End Sub

 

[KenHigg]

How are you generating the email.txt file?

 

[Timoty]

There is a report based on the same query that the form is. When the person clicks a buton on the form the 'on click' event causes the above code to run. Initially, the code created a txt file from the email report (based on the same query as the form). The email.txt file was then added to the email as an attachment. However, I need the text to be placed write into the message body of the email.

 

[KenHigg]

Maybe this would work - Instead of the "h:\email.txt" build a text string from the fields on the form (providing it's a single record form), and use it. Something like:

dim mystr as string

mystr = me.txtOne & " / " & me.txtTwo

DoCmd.OutputTo acReport, "Email", acFormatTXT, mystr

???

 

[Timoty]

Same issue

This won't some my problem though because I am still going to end up with a text file attachment as opposed to puilling the text right into the email message body. I have aached the database to give you a better look. If is very messy as I am just trying ideas out. I had designed a very fancy Outlook form that worked great until the first person tried to receive the information on a blackberry. Did I mention that I hate blackberrys.

 

[KenHigg]

My bad, I was thing you had used another method. Trysomething like:

Dim strMyText As String
    
strMyText = Me.ID & vbCrLf

strMyText = strMyText & Me.Date

DoCmd.SendObject , , , , , , , strMyText

Of course fill in the blanks as needed

 

[Timoty]

Thanks, I'll give it a try and let you know.

 

[Timoty]

Dumb question: How do I get it to Line feed. vbLf isn't doing it?

 

[boblarson]

See if this works for you.

1. I changed your path to a table so you can change paths easily.

2. I put in code to read the text file and output it to the body of the email.

3. I changed your email address to look in a table. Right now it only does it for one email address but you could iterate through and get multiples if you wanted.


4. I left the attachment but you can remove it if you want.

See the attached revised database.

 

[Timoty]

Thaks Bob. That is exactly was I was trying to do. I will take a good long hard look to see what you have done here so that I can better myself. I keep telling my employer that there are people who do this much better than me but in the end they just tell me that they are confident I will figure it out.

Thanks KenHigg. I learned a few things from your contributions as well.

 

[boblarson]

Glad we could be of assistance.

 

[Timoty]

Perplexed

Bob, I am somewhat perplexed. The DB is working well now but I like to understand things.

What does this code actually do?

Open "h:\email.rtf" For Input As #1
Do Until EOF(1)
Line Input #1, strTemp
strBody = strBody & strTemp & vbCrLf
Loop
Close #1

Secondly, is their a way to modify the section:
strEmail = DLookup("EmailAddress", "Addresses")
and
EmailSend.To = strEmail

so that the email is sent to two or more email recipients instead of just the first in the table addresses?

Thanks again for your time.

 

[boblarson]

This

Open "h:\email.rtf" For Input As #1
Do Until EOF(1)
   Line Input #1, strTemp
   strBody = strBody & strTemp & vbCrLf
Loop
Close #1

Opens the text file that has been generated by the OutPutTo code and then it iterates through, line by line building a text string of the values so that it can then use that string in the email body.

Secondly, is their a way to modify the section:
strEmail = DLookup("EmailAddress", "Addresses")
and
EmailSend.To = strEmail

so that the email is sent to two or more email recipients instead of just the first in the table addresses?

Yes, as I had mentioned in my post, I had set it so it would only do the one but you can use a recordset to iterate through and get all of them:

    Dim stDocName As String
    Dim strPath As String
    Dim strTemp As String
    Dim strBody As String
    Dim strEmail As String
    Dim rstEmail As DAO.Recordset
    Dim EmailApp As Object
    Dim NameSpace As Object
    Dim EmailSend As Object
    
    Set rstEmail = CurrentDb.OpenRecordset("tbl_EmailAddresses")

    Do Until rstEmail.EOF
        strEmail = strEmail & rstEmail("EmailAddresses") & ";"
        rstEmail.MoveNext
    Loop

    If Right(strEmail, 1) = ";" Then
        strEmail = Left(strEmail, Len(strEmail) - 1)
    End If


    strPath = DLookup("TextPath", "tbl_PathInfo")
    
    
    DoCmd.OutputTo acReport, "Email", acFormatTXT, strPath

    Open strPath For Input As #1
    Do Until EOF(1)
        Line Input #1, strTemp
        strBody = strBody & strTemp & vbCrLf
    Loop
    Close #1
    Set EmailApp = CreateObject("Outlook.Application")
    Set NameSpace = EmailApp.getNameSpace("MAPI")
    Set EmailSend = EmailApp.CreateItem(0)

    EmailSend.To = strEmail
    EmailSend.subject = "EMERGENCY"
    EmailSend.Attachments.Add strPath
    EmailSend.Display
    EmailSend.Body = strBody

    Set EmailApp = Nothing
    Set NameSpace = Nothing
    Set EmailSend = Nothing

    Kill strPath
    rstEmail.Close
    Set rstEmail = Nothing

 

[KenHigg]

If you put the info in the body of the email do you still need the attachment?

 

[boblarson]

That I left up to him to remove or keep, so I didn't modify that part at all.

 

[Timoty]

Thanks again. I am truely out of my league here. I appreciate you taking the time to explain it to me. No point in just copying and pasting. I like to understand things.

 

[KenHigg]

I was just thinking if that was so then the entire drill of generating the rtf was moot hence parsing it out was moot as well... ?

(Still and interesting method )

 

[Timoty]

Error

Sorry to carry this on but I have received the following error message that I am not sure how to fix.

Compile Error:
User-defined type not defined

The area highlighted is:
Dim rstEmail As DAO.Recordset

I have attached a screenshot.

 

[KenHigg]

You need to make sure your references are set. In vba code window do tools->references and check DAO object library.

 

[Timoty]

What happens if DAO object library isn't an option?