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

Sending individual emails from MS Access query record

P: 3
Hello,

I have a query with part numbers I need to get quotes on. The query record has the following: Supplier Email / RFQ / Part Number / Quantity / Description. I need to be able to send a quote request of the part number and other info to the supplier email on the record. On a daily basis, there will probably be over 750 records in the query to send as emails.

I have a sample template I came up with in Word (attached). How do I create a text email (without an attachment) that looks like the attached sample template, and contains the information from the MS Access query? It does not need to be exactly as the template, but it does need to have all the fields listed AND the capability to add more. I have read a bit into DoCmd.SendObject Method, but not sure it will work or how to exactly format the coding.

Are there different ways to do this? I am well versed in MS Access but not VBA, and must get this done. Using MS Access 2010, Outlook 2010 (both 32 bit) & Windows 10 Pro, but can upgrade MS Office to something more recent or use a different email client if it will help.

Thanks!

John
Attached Files
File Type: pdf QUOTE FORMAT EMAIL commercial.pdf (406.0 KB, 10 views)
3 Weeks Ago #1
Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
tpagmart,

Welcome to Bytes!

First, go here, which is an article on how to generate e-mails in Outlook.

Second, The below should get you into the ball park. Just create a recordset with your suppliers and send an e-mail to each of them.


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub ContactSuppliers()
  5. On Error GoTo EH:
  6.     Dim db              As DAO.Database
  7.     Dim rst             As DAO.Recordset
  8.     Dim strSQL          As String
  9.     Dim strSendTo       As String
  10.     Dim strSubject      As String
  11.     Dim strEMailBody    As String
  12.  
  13.     strSQL = _
  14.         "SELECT * FROM qryPartNumbers;"
  15.     Set db = CurrentDb()
  16.     Set rst = db.OpenRecordset(gstrSQL)
  17.     With rst
  18.         If Not (.BOF And .EOF) Then
  19.             Call .MoveFirst
  20.             Do While Not .EOF
  21.                 strSendTo = ![FB FIELD 1]
  22.                 strSubject = "RFQ: " & ![DBFIELD 2]
  23.                 strEMailBody = _
  24.                     "Hello," & vbCrLf & _
  25.                     "We need pricing and availability for the following " & _
  26.                     "part number(s):" & vbCrLf & vbCrLf & _
  27.                     "Part Number: " & ![DB FIELD 3] & vbCrLf & vbCrLf & _
  28.                     "Quantity: " & ![DB FIELD 4] & vbCrLf & vbCrLf & _
  29.                     "Description: " & ![DB FIELD 5]
  30.                 strEMailBody = strEMailBody & _
  31.                     "In your response, please provide the following " & _
  32.                     "information:" & vbCrLf & vbCrLf & _
  33.                     "Part Number:          Manufacturer:" & vbCrLf & _
  34.                     "Unit Price:           Delivery Time:" & vbCrLf & vbCrLf & _
  35.                     "Best Regards," & vbCrLf & _
  36.                     "John Doe Buyer" & vbCrLf & _
  37.                     "XYZ Corp" & vbCrLf & _
  38.                     "523-555-1234"
  39.  
  40.                 Call SendAnEMail(olSendTo:=strSendTo, _
  41.                                  olSubject:=strSubject, _
  42.                                  olEMailBody:=strEMailBody, _
  43.                                  olDisplay:=True)
  44.  
  45.                 Call .MoveNext
  46.             Loop
  47.         End If
  48.         Call .Close
  49.     End With
  50.     Call db.Close
  51.     Set rst = Nothing
  52.     Set db = Nothing
  53.  
  54.     Exit Sub
  55. EH:
  56.     MsgBox Err.Number & " - " & Err.Description
  57.     Exit Sub
  58. End Sub
Normally, we don't provide complete answers like this, but it was just easier for me to throw this together than try to describe it over several posts.

Hope this hepps!
3 Weeks Ago #2

P: 3
Hello twinnyfo,

I'm digging in to try to learn a lot more about VBA so I can make this happen. As I progress I will try to share my progress.

Thanks for the info!
3 Weeks Ago #3

ADezii
Expert 5K+
P: 8,692
  1. Let's assume you have a very simple Table (tblDemo) Structure with only 2 Records.
    Expand|Select|Wrap|Line Numbers
    1. EMail                    RFQ          Part Number    Quantity     Description
    2. SomeOne@SomeWhere.com    GT456KKYF    HYU-9964-A     150          Some Part  Description
    3. JohnDoe@aol.com          JJHDSW12I    ZZML-66-AWQ-4  200          Another Part Description
  2. The following Code, very similar to twinnyfo's, will loop thru both Records, plug in the appropriate Values for each Record, maintain the Subject and Body Format, and display both EMails in the Outlook Window.
    Expand|Select|Wrap|Line Numbers
    1. Dim oLook As Object
    2. Dim oMail As Object
    3. Dim MyDB As DAO.Database
    4. Dim rst As DAO.Recordset
    5. Dim strMsg As String
    6. Const conDBL_ROW = vbCrLf & vbCrLf
    7.  
    8. Set MyDB = CurrentDb
    9. Set rst = MyDB.OpenRecordset("tblDemo", dbOpenForwardOnly)
    10.  
    11. DoCmd.Hourglass True
    12.  
    13. Set oLook = CreateObject("Outlook.Application")
    14.  
    15. Do While Not rst.EOF
    16.   Set oMail = oLook.CreateItem(0)
    17.     With oMail
    18.       strMsg = "Hello," & conDBL_ROW & "We need pricing and " & _
    19.          "availability for the following part number(s):" & conDBL_ROW & "Part Number: " & rst![Part Number] & _
    20.          vbCrLf & "Quantity: " & rst![Quantity] & vbCrLf & "Description: " & rst![Description] & conDBL_ROW & _
    21.          "Please provide the following information: " & conDBL_ROW & "Part Number:" & String(30, "_") & _
    22.          "Manufacturer:" & String(30, "_") & conDBL_ROW & "Unit Price:" & String(33, "_") & _
    23.          "Delivery Time:" & String(30, "_") & conDBL_ROW & "Best Regards," & conDBL_ROW & "John Doe" & _
    24.          vbCrLf & "Buyer" & vbCrLf & "XYZ Corp" & vbCrLf & "523-555-1234"
    25.       .To = rst![email]
    26.       .Body = strMsg
    27.       .Subject = "RFQ: " & rst![RFQ]
    28.         .Display
    29.         '.Send      'To SEND and NOT Display
    30.     End With
    31.     rst.MoveNext
    32. Loop
    33.  
    34. Set oMail = Nothing
    35. Set oLook = Nothing
    36.  
    37. rst.Close
    38. Set rst = Nothing
    39.  
  3. To automatically SEND the EMails instead of DISPLAYING them, Comment the .Display line and UnComment the .Send line of Code.
2 Weeks Ago #4

P: 3
Hello ADezzi - I am working through it and appreciate all the help!
1 Week Ago #5

ADezii
Expert 5K+
P: 8,692
Not a problem, we are here if you get stuck.
1 Week Ago #6

Post your reply

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