473,573 Members | 2,817 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Send Specific Query Results As Body Of Email To Seperate Addresses

1 New Member
I am trying to use the following post results (http://www.thescripts.com/forum/thread189759.html) and add another requirement. I need to send the results to just the email addresses that the query specifies for each record... Essentially this is a make shift Ordering Tool and I want to be able to notify the receiver of the order and its specifics.

q_Order_Detail_ 4email consists of
[Contact Email],[ID],[Product_1],[Quantity_1] and [Product_PO_1]
Example:
Hello@mail.com,956,Modem,1000 ,xyz123
Hello2@mail.com,957,USB,500,zy x321

In this case I need for two emails to be generated with one line of data in the results on each email.

Expand|Select|Wrap|Line Numbers
  1. Function GetOrderBody() As String
  2. On Error GoTo GetOrderBody_Err
  3.  
  4. Dim dbs As DAO.Database
  5. Dim rst As DAO.Recordset
  6. Dim strInfo As String
  7.  
  8. Set dbs = CurrentDb
  9. Set rst = dbs.OpenRecordset("q_Order_Detail_4email", dbOpenForwardOnly, dbReadOnly)
  10.  
  11. While Not rst.EOF
  12.  
  13. strInfo = strInfo & rst!ID & vbTab
  14. strInfo = strInfo & rst!Product_1 & vbTab
  15. strInfo = strInfo & rst!Quantity_1 & vbTab
  16. strInfo = strInfo & rst!Product_PO_1 & vbCrLf
  17. rst.MoveNext
  18. Wend
  19.  
  20. GetOrderBody = strInfo
  21.  
  22. GetOrderBody_Exit:
  23.     Exit Function
  24.  
  25. GetOrderBody_Err:
  26.     MsgBox Error$
  27.     Resume GetOrderBody_Exit
  28.  
  29. End Function
Expand|Select|Wrap|Line Numbers
  1. Public Function EOT_OPR_SendEMail()
  2.  
  3. Dim db As DAO.Database
  4. Dim MailList As DAO.Recordset
  5. Dim MyOutlook As Outlook.Application
  6. Dim MyMail As Outlook.MailItem
  7. Dim Subjectline As String
  8. Dim BodyFile As String
  9. Dim fso As FileSystemObject
  10. Dim MyBody As TextStream
  11. Dim MyBodyText As String
  12.  
  13.  
  14. Set fso = New FileSystemObject
  15.  
  16.  ' First, we need to know the subject.
  17.  ' We can't very well be sending around blank messages...
  18.  
  19. Subjectline$ = "Equipment Ordering Tool - Order Placed"
  20. 'Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
  21.                  "We Need A Subject Line!")
  22.  
  23.  ' If there's no subject, call it a day.
  24.  
  25. If Subjectline$ = "" Then
  26.     MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
  27.         "Quitting...", vbCritical, "E-Mail Merger"
  28.     Exit Function
  29. End If
  30.  
  31.  ' Now we need to put something in our letter...
  32.  
  33. BodyFile$ = "\\CO1860-IMRPTNG\Automation\EOT-Order_Placed_Recipient.txt"
  34.  
  35.  ' If there's nothing to say, call it a day.
  36.  
  37. If BodyFile$ = "" Then
  38.     MsgBox "No body, no message." & vbNewLine & vbNewLine & _
  39.          "Quitting...", vbCritical, "I Ain't Got No-Body!"
  40.     Exit Function
  41. End If
  42.  
  43.  ' Check to make sure the file exists...
  44. If fso.FileExists(BodyFile$) = False Then
  45.     MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
  46.            "Quitting...", vbCritical, "I Ain't Got No-Body!"
  47.     Exit Function
  48. End If
  49.  
  50.    ' Since we got a file, we can open it up.
  51.     Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
  52.  
  53.    ' and read it into a variable.
  54.     MyBodyText = MyBody.ReadAll
  55.  
  56.    ' and close the file.
  57.     MyBody.Close
  58.  
  59.    ' Now, we open Outlook for our own device..
  60.     Set MyOutlook = New Outlook.Application
  61.  
  62.  
  63.  ' Set up the database and query connections
  64.  
  65.     Set db = CurrentDb()
  66.  
  67.     Set MailList = db.OpenRecordset("q_Order_Detail_4email", dbOpenForwardOnly, dbReadOnly)
  68.  
  69.  ' now, this is the meat and potatoes.
  70.  ' this is where we loop through our list of addresses,
  71.  ' adding them to e-mails and sending them.
  72.  
  73.     Do Until MailList.EOF
  74.  
  75.         ' This creates the e-mail
  76.  
  77.         Set MyMail = MyOutlook.CreateItem(olMailItem)
  78.  
  79.             ' This addresses it
  80.             MyMail.To = MailList("Contact Email")
  81.  
  82.             'This gives it a subject
  83.             MyMail.Subject = Subjectline$
  84.  
  85.             'This gives it the body
  86.             MyMail.Body = MyBodyText
  87.  
  88.  
  89.             'If you want to send an attachment
  90.             'uncomment the following line
  91.  
  92.             'MyMail.Attachments.Add "c:\dbgout.txt", olByValue, 1, "My Displayname"
  93.             'MyMail.Attachments.Add "c:\dbgout.txt", olByValue, 1, "My Displayname2"
  94.  
  95.             ' To briefly describe:
  96.             ' "c:\myfile.txt" = the file you want to attach
  97.             '
  98.             ' olByVaue = how to pass the file.  olByValue attaches it, olByReference creates a shortcut.
  99.             '      the shortcut only works if the file is available locally (via mapped or local drive)
  100.             '
  101.             ' 1 = the position in the outlook message where to attachment goes.  This is ignored by most
  102.             '      other mailers, so you might want to ignore it too.  Using 1 puts the attachment
  103.             '      first in line.
  104.             '
  105.             ' "My Displayname" = If you don't want the attachment's icon string to be "c:\myfile.txt" you
  106.             '      can use this property to change it to something useful, i.e. "4th Qtr Report"
  107.  
  108.  
  109.  
  110.             'This sends it!
  111.             MyMail.Send
  112.  
  113.             'Some people have asked how to see the e-mail
  114.             'instead of automaticially sending it.
  115.             'Uncomment the next line
  116.             'And comment the "MyMail.Send" line above this.
  117.  
  118.             'MyMail.Display
  119.  
  120.  
  121.  
  122.     'And on to the next one...
  123.     MailList.MoveNext
  124.  
  125. Loop
  126.  
  127.  'Cleanup after ourselves
  128.  
  129. Set MyMail = Nothing
  130.  
  131.  
  132. 'Uncomment the next line if you want Outlook to shut down when its done.
  133. 'Otherwise, it will stay running.
  134.  
  135. 'MyOutlook.Quit
  136. Set MyOutlook = Nothing
  137.  
  138. MailList.Close
  139. Set MailList = Nothing
  140. db.Close
  141. Set db = Nothing
  142.  
  143. End Function
Any help would be greatly appreciated.
Jan 22 '08 #1
1 6546
MMcCarthy
14,534 Recognized Expert Moderator MVP
I don't think you need to be that complicated. Try this instead....

Expand|Select|Wrap|Line Numbers
  1. Function GetOrderBody() As String
  2. On Error GoTo GetOrderBody_Err
  3. Dim dbs As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim strInfo As String
  6.  
  7.     Set dbs = CurrentDb
  8.     Set rst = dbs.OpenRecordset("q_Order_Detail_4email", dbOpenForwardOnly, dbReadOnly)
  9.  
  10. Do Until rst.EOF
  11.  
  12.     strInfo = strInfo & rst!Id & vbTab
  13.     strInfo = strInfo & rst!Product_1 & vbTab
  14.     strInfo = strInfo & rst!Quantity_1 & vbTab
  15.     strInfo = strInfo & rst!Product_PO_1 & vbCrLf
  16.     DoCmd.SendObject acSendNoObject, , , rst![Contact Email], , , "Order Details", strInfo
  17.     rst.MoveNext
  18. Loop
  19.  
  20. GetOrderBody_Exit:
  21.     Exit Function
  22.  
  23. GetOrderBody_Err:
  24.     MsgBox Error$
  25.     Resume GetOrderBody_Exit
  26.  
  27. End Function
Feb 4 '08 #2

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

Similar topics

13
2722
by: Wescotte | last post by:
Here is a small sample program I wrote in PHP (running off Apache 1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data base is using DB2 V5R3M0. The client is WinXP machine using the iSeries Client Access Driver ver 10.00.04.00 to connect to the database. The problem is that executing the exact same SQL select statement more...
2
3311
by: serendipity | last post by:
Hi, I'm not sure if this is possible as i've googled everywhere, but i have a select query that returns a customer record with their associated sales orders. I would like to automate a process which sends an email reminder to each customer in the database, that has outstanding orders. This email reminder should have the results of the query...
6
11168
by: DigitalRick | last post by:
I have been running CDONTS in my ASPpages to send emails to me sent from my guestbook. It had been working fine untill I upgraded to Server 2003 (I am also running Exchange 2003) all locally. I will include the code I originally used. I understand I should switch from CDONTS to CDO mail but after several sttempts I am finding a very hard...
8
7247
by: james | last post by:
I am trying to use Filestream to read a file ( .DAT) that contains values in HEX that I want to convert to text. I know the different offset addresses for each portion of the data I am trying to retrieve. But, I am having problems actually reading the data for the number of Bytes that I need to get from each address. Here is some of the code I...
10
24365
by: Mike Charney | last post by:
Is there a simple way to send SMTP email from Access VBA? Mike m charney at dunlap hospital dot org
1
2316
by: divya | last post by:
I have a form which has a textarea,name - txtTo where he adds email addresses.Now when he clicks on sendemail I want to open a mailto link with addresses taken from textarea. Example I added in the text area these three addresses divyasanam@yahoo.com,ewew@yah.com,gjh@yahoo.com and now when I click on send email a ComposeMail window in...
5
2151
by: Mike | last post by:
I have a page with a textbox that a user can enter in mutliple email addresses such as: user1@yahoo.com;user2@yahoo.com;user3@gmail.com; and so on, I then have a foreach loop to get all of the emaill addresses and send emails out. the problem is that all of the email addresses the email is being sent to is showing in the section so...
2
2475
by: kennykenn | last post by:
Hi, Ive producd code to send an email after capturing info off a form,it works fine locally but when i put it live it doesnt work! the code is stopin at 'msg.send' any ideas, here the code! <%
4
1993
by: chris | last post by:
I need to maintain a list of subscribers to an email list for a "newsletter" that will be sent via a web form probably once a month. I anticipate low numbers--tens to maybe one hundred subscribers at the most. Just curious what the best way to code this is. Should I just loop through the addresses and send one-off emails to each, or is it...
0
7760
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7679
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...
0
7996
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. ...
0
8191
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7760
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...
0
8049
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...
0
3724
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3723
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1029
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...

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.