Setting variables from current record for use in email subject and body

[Steve3]

Hello, I have a parts db that I am trying to send a request to re-order email when the parts quantity is low. From the form the user opens a sub form to edit the parts details and if it needs to be ordered the can hit the button and it will send out the email.

I am trying to put the item name from the Item field and put it in the email subject and then pull the Item, Part Number and Quantity to use in the emails body. I have been scouring the web for possible answers, the email gets sent but only with the text for the code. I have tried using Dlookup and SQL expressions but I don't have a where criteria, I just want to pull from the current record that is opened in the sub form.
Parts is the Table.
Item, PartNumber and Quantity are the fields/columns

Private Sub ReOrderEmail_Click()
 
    Dim olApp As Object
    Dim objMail As Object
    Dim Mailsql As String
    Dim Subsql As String
 
    ' Puts date in reordered date field
    Me.txtReOrderedDate = Date
 
    Dim rs As DAO.Recordset
 
    Subsql = CurrentDb.OpenRecordset("SELECT Item FROM Parts")
    Mailsql = CurrentDb.OpenRecordset("SELECT Item,Quantity FROM Parts")
 
    'Sends Email
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
 
     If Err Then
        Set olApp = CreateObject("Outlook.Application")
     End If
 
    Set objMail = olApp.CreateItem(olMailItem)
 
    With objMail
      .To = "[e-mail]"
      .Subject = "Please Reorder" & Subsql
      .Body = Mailsql
      .send
    End With
 
   MsgBox "The email has been sent."
 
rs.Close
 
Set rs = Nothing
 
 
End Sub
 

This will be sent to same person everytime, and just need a simple email for them to see to re-order more parts.
It has changed numerous times, but this is where I currently am at, thanks in advance for your help.

[zmbd]

If you use Bytes.com search tool with EMAIL and one of the moderator/experts names (NeoPa, Rabbit, ZMBD, etc...) you will turn up a treasure-trove of threads covering this very topic in its coat of many colours.

You'll also find an invaluable source of information in our insight's section:
Bytes > Sitemap > Microsoft Access / VBA Insights


Let us get to your specific post:
Ok, there are a few minor problems with your code; however, I need a bit of information about your form and record set.

1) From your post it appears that the form is bound to a table/query (?), if so, then you can simply use the current values in the control for your email. Based on the post you've already given us that Item, PartNumber and Quantity are the fields/columns in the form's recordset and if these are bound to controls on the form and current record then the code only need to reference them for their current values, example:

 zOrderItem = Me.Item
zPartNumber = Me.PartNumber
zQnty = Me.Quantity
 

You can then build your message string text using the variables. (It's not strickly required to use the variable approach; however, find the code easier to write)

2) Multiple use of CurrentDb isn't considered pest practice. This potentially opens multiple pointers to the current database and allocates memory for each. Instead it is a preferred method to use something like:

Dim zSQL As String
     Dim zDB As DAO.Database
     Dim zRS As DAO.Recordset
 
     ' ...other code
 
     'Setup the recordset
     Set zDB = CurrentDb
     zSQL = "SELECT Item FROM Parts"
     Set zRS = zDB.OpenRecordset(zSQL, dbOpenDynaset)
 

You should also close and release objects etc... that you've opened:

If Not isnothing(zRS) Then
   zRS.Close
   Set zRS = Nothing
End If
   'note we didn't open the current database
   '    in code so we're only releasing the pointer
If Not isnothing(zDB) Then Set zDB = Nothing
'
'and close and release any other objects too

3) Given that you are using outlook as the email client, what appears to be a fairly short text message, then a widely available code snipette is from:
DoCmd.SendObject Method
Frankly, I use a variation of this function to send error reports from my projects

Public Function CreateEmailWithOutlook( _
    MessageTo As String, _
    Subject As String, _
    MessageBody As String)
 
    ' Define app variable and get Outlook using the "New" keyword
    Dim olApp As New Outlook.Application
    Dim olMailItem As Outlook.MailItem  ' An Outlook Mail item
 
    ' Create a new email object
    Set olMailItem = olApp.CreateItem(olMailItem)
 
    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = MessageTo
        .Subject = Subject
        .Body = MessageBody
        .Display    ' To show the email message to the user
    End With
 
    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing
 
End Function
 

Build your string and pass to this function the other parameters voila the email appears. NOW THERE IS A CATCH TO THIS ONE - If the user cancels the email, there can be an error so I use a variation that does not display the email message to the user and a version can be found at the above link. I use a message box with yes/no buttons to confirm the desire to send the email. The MsgBox displays the recipient(s) email and the text...

BOL

-z

[Steve3]

That did the trick, for whatever reason I wasn't having very good luck assigning values using me."field" for the current recordset, able to get it working now. Thank you for you assistance, I will check out the Insights section as well.