By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,133 Members | 1,092 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,133 IT Pros & Developers. It's quick & easy.

Send Specific Query Results As Body Of Email To Seperate Addresses

P: 1
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,zyx321

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
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
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

Post your reply

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