473,889 Members | 1,654 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Send Access report in body of Outlook email

24 New Member
Is there a way to insert data/information from a report into the body of an Outlook email message?

I want the VBA code to pull the data from my report, input it into the body of my email message, and then send, without using any attachments.

Thanks for any assistance!
Oct 5 '12 #1
11 33502
twinnyfo
3,653 Recognized Expert Moderator Specialist
ryaan,

There are several ways to do this, but hte best is to use a recordset to pull the specific data from your database, then build the Body Text of your e-mail and then send it, using the SendObject method. There are other ways, also, but just depends on your preferences.

What have you tried so far?
Oct 5 '12 #2
ryaanmichael
24 New Member
Hi Twinnyfo,

I've tried the recordset, but I can't get it to work. I've got a report called "TripCSA" that contains all the data I want to include. I want to take all that data and put it in my Outlook email body.

Thanks in advance,
Ryan
Oct 5 '12 #3
twinnyfo
3,653 Recognized Expert Moderator Specialist
You will need to provide examples of your report and queries underneath that report. What about the recordset could you not get to work?

You will need to provide moreinformaiton about your database and your desired result before any of us can even begin to assist you with this. Depending on your report, this could be rather simple, or rather complex.... But since we don't even know what your report looks like, or the data behind it, I can't estimate that.
Oct 5 '12 #4
ryaanmichael
24 New Member
Hi Twinnyfo,

My report data is gathered from a query called "qryCurrentTrip ". All the information for the query is pulled from my table "tblCurrentTrip ". The query ("qryCurrentTri p") filters all the data in the "Name" field which is equal to "th98". The report shows only records with "th98" in the "Name" field (usually only about 5-10 records). The report contains 5 fields: "tripNumber ", "Name", "City", "State", and "Date". As of now, every time I need to send out this report, I just copy the report data and paste it in my email. But I know there has to be a way to click a "send" button and an email will generate with my report data in the body.

Thanks,
Ryan
Oct 5 '12 #5
twinnyfo
3,653 Recognized Expert Moderator Specialist
What have you tried so far and what is not working about it? This should be relatively straightforward , since you neither have many rows or collumns, but the approach will always be the same.

Create a recordset in your code that pulls the data from qryCurrentTrip, and count how many records your have.

To build the e-mail body, create your headers for each field, then cycle through your records one at a time, inserting tabs between the fields for formatting purposes.

Then all you need is to designate the e-mail address(es) and subject line and you should be good to go. You will have to play with the formatting until it suits your liking, but that is an outline for how you would do it. I'll be glad to help if you come across snags, but I won't write the code for you...
Oct 5 '12 #6
zmbd
5,501 Recognized Expert Moderator Expert
ryaanmichael
Asking for full code is against site rules. However, as twinnyfo has started the quest and it's your first post... (read the FAQ) in the future the thread might be deleted.

Of Major importance... what is your email client?
If Outlook, then there are built in methods. (open access, press [F1] and search the help file for send object)
If others then you may need to use the SMTP methods.

If you are already knee-deep in code, please post it (remember to format it using the <CODE/> button) along with an explanation of any errors and what is or is not working.

In either case, there are tons of thread here in BYTES that address this very question and have some very useful code (in-fact, I just changed one of my codes to use part of one I found here! :) ) A quick use of the search will turn up the threads for you.
Oct 5 '12 #7
ryaanmichael
24 New Member
Hi Twinnyfo,

Thank you so much for your continued assistance. I'm not understanding this recordset thing. At first it seemed like a really simple task. Anyways, here is my non-working code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub sentRPT_Click()
  2.  
  3. Dim openTrip As Recordset
  4. ' "rptOpenTrip" references the Report I want to include in my Outlook email
  5. ' My report gathers data from my query "qryCurrentTrip" when the values in "Name" field = "th98"
  6. Set openTrip = CurrentDb.OpenRecordset("rptOpenTrip")
  7.  
  8. '***********************************
  9. Set objOutlook = CreateObject("Outlook.application")
  10. Set objEmail = objOutlook.CreateItem(olMailItem)
  11.  
  12. 'Components of Outlook Email
  13. With objEmail
  14.     .To = "TO ADDRESS"
  15.     .Subject = "Subject Test"
  16.     .HTMLBody = openTrip
  17.     .Display
  18. End With
  19. '***********************************
  20. End Sub
Oct 5 '12 #8
twinnyfo
3,653 Recognized Expert Moderator Specialist
Ryaan,

Although you declared a recordset, you must also first declare the database from which the recordset will get its information. Also, when you tried to set your recordset, you simply used the report name, which is an invalid means of establishing that recordset.

If you look below, you will see that I declare my variables, and in order to create the recordset, I must write a short SQL query that will pull data from the database (line 12) using your query as the source.

I begin bulding the E-mail content in line 15, in which you may add whatever text you want. The vbCrLf's are line feeds, so you don't have one huge block of text. You will have to play with the spacing of lines 16-17 to enable them to match up with your fields.

In lines 19-25, we are putting the content of your report (not the report itself, but the data behind it) into the e-mail body. The "Chr(9)" is to insert a [Tab] character to align the columns. In between lines 26 and 27 you could also add some more text, like your signature block or contact information.

I have always preferred to use the DoCmd.SendObjec t Method, because it's very easy to use (even though we are not sending an object). I know there are some other featurees of creating an Outlook Object which may be helpful, but in this particular case, it does not seem necessary to create those objects.

I hope this helps to get you closer to your goal.....

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub sentRPT_Click()
  5. On Error GoTo EH
  6.     'Recordset variables
  7.     Dim db As Database
  8.     Dim rstOpenTrip As Recordset
  9.     Dim strSQL As String
  10.     Dim strSubject, strBody, strAddresses As String
  11.     Set db = CurrentDb()
  12.     strSQL = "SELECT * FROM qryCurrentTrip;"
  13.     Set rstOpenTrip = db.OpenRecordset(strSQL, dbOpenDynaset)
  14.     If Not rstOpenTrip.EOF Then
  15.         strBody = "blah blah blah" & vbCrLf & vbCrLf
  16.         strBody = strBody & "NAME     Field2    Field3 ....etc." & vbCrLf
  17.         strBody = strBody & "==================================" & vbCrLf
  18.         rstOpenTrip.MoveFirst
  19.         Do While Not rstOpenTrip.EOF
  20.             strBody = strBody & rstOpenTrip("Name") & Chr(9) & _
  21.                 rstOpenTrip("Field2") & Chr(9) & _
  22.                 rstOpenTrip("Field3") & Chr(9) & _
  23.                 rstOpenTrip("etc.") & vbCrLf
  24.             rstOpenTrip.MoveNext
  25.         Loop
  26.     End If
  27.     strSubject = "Here's my report!"
  28.     strAddresses = "supercoder@bytes.com"
  29.     DoCmd.SendObject acSendNoObject, , acFormatTXT, strAddresses, , , strSubject, strBody, True
  30.     Exit Sub
  31. EH:
  32.     MsgBox Err.Number & " " & Err.Description
  33.     Exit Sub
  34. End Sub
Oct 5 '12 #9
ryaanmichael
24 New Member
Thank you so much for your time! I am going to try this out and let you know how it goes.

Thanks again,
Ryan
Oct 5 '12 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

3
5970
by: Chuck Farah | last post by:
I am unsuccessfullly trying to display an outlook email from a vb web forms (.net) outlook 2002 #1. is it possible to use outlook client email from a web form using the outlook object model within vb .net for web forms? #2. If not what is recommended (SMTP has problems if the address are not correct). I also have used the Mailto: but am having problems passing the body with formating
1
2684
by: Brian | last post by:
I have a project that I built that takes data from a recordset and then send an email to appropriate person. This process is using Access 2K and Outlook 2K. The email is going out as a RTF email and I want to format some of the Fonts sizes and attributes like Bold or Italic. I can format the dates and numbers, but can't seem to get the Font Size and Color to change in code. Can someone please give me some tips on how to get this done. ...
1
10071
by: joshua | last post by:
Is there a way to set the FROM name (the name the recepient sees as the one who sent the email) when using Access to create an Outlook email message? (Using MS-Office 2000 SP3) We set up an anonymous account to send survey messages to our customers in the company-wide address book, but can I set the message to be sent from that account in code? It always defaults to the normal (default) "From" name Outlook sender. Here's my code so...
0
2155
by: VP | last post by:
g'day, i am posting a problem i have encountered with creating an email using outlook through some basic c# code. the problem arises when using different email editors in outlook. At the moment i am using outlook 2000. Outlook can be configured to use the default outlook email editor or use ms word as the email editor in outlook. The sample code below simply creates an email with a hyperlink. The problem I am facing is that when...
8
9640
by: tymperance | last post by:
I have a report that I currently email directly from Access as an attachment using a macro. I need to make it be the body of the email instead. Can that be accomplished without extensive VBA programming? Thanks!
3
1742
by: Robert S | last post by:
A friend is thrying to send me an Access database attached to an email. I am using Outlook but when the email arrives the attachemnt is missing. Any suggestions, please? When changed to Excel it arrives OK. Thanks, Robert
5
9197
AllusiveKitten
by: AllusiveKitten | last post by:
Hi All I was just wondering if it was possible to send a report as the email body. I am wanting to send an automatic email to multiple people which will be personalised with their names etc. Thank you all AK
10
9996
by: Dan2kx | last post by:
Hello to all, i have a new interesting problem to solve.... i have a report that i would like to email to a list of users, the report is a full page document and is personalised to each user (basically) more than one user can have the same email address so i will create a recordset to list all of the users (who need the email) and loop through the users and send to the associated email address, i have a few problems 1)currently the...
1
1908
by: shalskedar | last post by:
My Query is Can we create an exe file specifically for Ms Access Report because In my Database(Acess 2003)I need to send 1 of the Report as an attachment via Ms Outlook. If I try to send this Report as attachment,it opens with Ms Excel.But i require the same format as that of the Access.Thus I need to do this using Access. Can any one let me know how to convert this Report to an exe file so that i can send it as an attachment or is there...
10
9101
by: neelsfer | last post by:
i would like to send a specific report "RaceresultsR", to the email addresses listed in a query called Rt_EmailQ and the field name in query is called "Email" The code below only show a blank "To" block to add addresses manually please help DoCmd.SendObject acReport, "RaceresultsR", "HTML(*.html)", "", "", "", "Race results", "", True, ""
0
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11203
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10896
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9612
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7151
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6029
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4650
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3257
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.