Results 1 to 5 of 5
  1. #1
    Ehmke66 is offline Novice
    Windows Vista Access 2003
    Join Date
    Dec 2010
    Posts
    3

    Email from report to Email body

    Below is a piece of code that I am using to input a report (rptBlackberryLeads) into the body of an email. The procedure works but has to big flaws that I can’t seem to fix.


    • The report is 2 pages long and the email only captures the first page. There are 2 supreports in the original report.
    • How do I program this so that it will logon to a particular PST file in Outlook then send the email and close outlook.


    Any help would be greatly appreciated.



    Private Sub CloseoutTurnover_Click()

    Const ForReading = 1, ForWriting = 2, ForAppending = 3

    Dim fs, f
    Dim RTFBody, strTo
    Dim MyApp As New Outlook.Application
    Dim MyItem As Outlook.MailItem

    'DoCmd.OutputTo acOutputReport, "Report1", acFormatRTF, "Report1.rtf"
    DoCmd.OutputTo acOutputReport, "rptBlackberryLeads", acFormatHTML, "rptBlackberryLeads.htm"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.OpenTextFile("rptBlackberryLeads.htm", ForReading)
    'Set f = fs.OpenTextFile("Report1.rtf", ForReading)
    RTFBody = f.ReadAll
    f.Close

    Set MyItem = MyApp.CreateItem(olMailItem)
    With MyItem
    .To = "Ehmke66@email.com"
    .CC = "Ehmke66@email.com"
    .Subject = "Dispute Update"
    .HTMLBody = RTFBody


    End With
    MyItem.Display

    End Sub

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    the email is not capturing anything. You are writing your variable contents to the mail item's properties. That's the medium.

    So if you're not getting everything, then the problem is in the OUTPUTTO command or the READALL method from the fso.

    have you checked this first?

    also, I'm not sure outlook can send a message instantaneously, although with the option checked it does. But code runs faster than that, and your session would close before the message would be able to get through the mail server.

  3. #3
    Ehmke66 is offline Novice
    Windows Vista Access 2003
    Join Date
    Dec 2010
    Posts
    3
    Thanks I understand.

    One more question is there a better way to take a report and insert it into the body of a email. I'm still trying to sort out the OUTPUTTO command or the READALL methods with not much success.

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    you know I'm not really sure. I dont' like the output method and I don't use it much anyway.

    but as far as reading an entire textfile, I use LOF and I/O:
    Code:
    fNum = FreeFile
    
             Open myFile For Input As #fNum
                variable = Input$(LOF(1), 1)
             Close
    and i'm sure it matters what the report looks like. If it's nothing but a query with a different interface, you might as well just stick the dataset in it, wouldn't you think?

    I doubt you can put the object in there, but the purpose of why you're doing it will really yield you the correct method to use.

    for instance, if you need to send data, forget the report "fancy look" and use vba to shift the data into the email.

  5. #5
    Ehmke66 is offline Novice
    Windows Vista Access 2003
    Join Date
    Dec 2010
    Posts
    3

    Help

    The part that is so frustrating is that DoCmd.OutputTo is outputing exactly what I want. The only issue is that instead of doing it in one file it creates 4 files one for each page of the report, (rptBlackberryLeadsPage . rptBlackberryLeadsPage2, rptBlackberryLeadsPage3, rptBlackberryLeadsPage4) and only the first file is inserted into the body of the email. How would I insert all of the files into the email body or program access so that one continuous report is generated in one file?

    P.S. as you can tell I am new to VBA.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. rename report and email
    By maddaddy in forum Reports
    Replies: 11
    Last Post: 08-12-2011, 06:17 AM
  2. Send in Email Body vice Attachment
    By cassidym in forum Programming
    Replies: 2
    Last Post: 10-20-2010, 06:21 AM
  3. Export Contents of a Report into Email Body
    By Nosaj08 in forum Reports
    Replies: 4
    Last Post: 05-27-2009, 09:05 AM
  4. Field Name into Subject/Body of an email?
    By Stanggirlie in forum Programming
    Replies: 0
    Last Post: 01-05-2009, 11:51 AM
  5. Email field name into subject/body?
    By Stanggirlie in forum Access
    Replies: 0
    Last Post: 01-02-2009, 11:07 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums