Can I output data from a query / table / form by email as apposed to attaching?

[Corwin Moyne]

I have an Access Database that is used as an ordering system. At the minute, when the user hits the re-order button, the details required are passed to a sub which then passes these details into the body of an email and sends it. This works great but the problem is, one email equals one order. One of my users has now requested that multiple orders can be made on one click of the button. I have added a check box to the form and ideally I would like to loop through the table and add the info to the body of the email where the check box is marked true. Is this possible?

[ADezii]

Yes, this should be possible but will need much greater detail. Table structure, Field Types, sample Data, E-Mail Client...

[Poet]

Assuming you're using Outlook and already have an idea of how to generate the body of the e-mail...

Sure, using Ron de Bruin's Outlook code for Excel and retrofitting it to Access: http://www.rondebruin.nl/win/s1/outlook/bmail4.htm

The basic steps would be:

1. Include reference to Microsoft Outlook Object Library in the Access VBA IDE.
2. Augment Ron's Mail_small_Text_Outlook function to include strBody in the function's arguments, so you can create the body outside of the mail function.
   

   Public Sub mailSend(Optional strTo As String, Optional strCC As String, Optional strBBC As String, _
       Optional strSubject As String, Optional strBody As String, Optional varAttachmentFilepath As Variant, Optional boolSend As Boolean, _
       Optional boolHTMLBody As Boolean, Optional strSendFromEmail As String)
   ' send an e-mail message (and optional attachment) via outlook
   ' adapted from code by ron de bruin of www.rondebruin.nl
    
   Dim objOutlook As Object
   ' object for outlook application
    
   Dim objMessage As Object
   ' object for outlook mail message
    
   Set objOutlook = CreateObject("Outlook.Application")
   ' reference/open outlook
    
   Set objMessage = objOutlook.CreateItem(0)
   ' create a new outlook mail message
    
   With objMessage
   ' working with the new outlook mail message, ...
    
       .To = strTo$
       ' set the recipients
    
       .CC = strCC$
       ' set the carbon copy recipients
    
       .BCC = strBCC$
       ' set the blind carbon copy recipients
    
       .Subject = strSubject$
       ' set the subject
    
       If boolHTMLBody = True Then
       ' if user requests to have the e-mail sent as an html e-mail, ...
    
           .BodyFormat = olFormatHTML
           ' set the body format of the e-mail to html
    
           .HTMLBody = strBody$
           ' set htmlbody of e-mail
    
       Else
       ' if user does not want to have the e-mail sent as an html e-mail, ...
    
           .Body = strBody$
           ' set the body
    
       End If
    
       If strSendFromEmail$ <> vbNullString Then .SentOnBehalfOfName = strSendFromEmail$
       ' if user provided a from e-mail, set the sentonbehalfof e-mail
    
       If IsArray(varAttachmentFilepath) Then
       ' if the attachment filepath is an array, ...
    
           If IsEmpty(varAttachmentFilepath) = False Then
           ' if the attachment filepath array is not empty, ...
    
               For Each objAttachmentFilepath In varAttachmentFilepath
               ' looping through each attachment filepath in the array, ...
    
                   If CStr(objAttachmentFilepath) <> vbNullString Then
                   ' if the attachment filepath, converted to a string, is not blank, ...
    
                       .Attachments.Add objAttachmentFilepath
                       ' add the attachment filepath to the array
    
                   End If
    
               Next objAttachmentFilepath
               ' continue looping through the attachment filepath array
    
           End If
    
       Else
       ' if the attachment filepath is not an array, ...
    
           If IsMissing(varAttachmentFilepath) = False Then
           ' if an attachment filepath is possibly provided, ...
    
               If varAttachmentFilepath <> vbNullString Then
               ' if an attachment filepath is provided
    
                   .Attachments.Add varAttachmentFilepath
                   ' add the attachment
    
               End If
    
           End If
    
       End If
    
       If boolSend = True Then
       ' if user wants to send the messsage, ...
    
           .Send
           ' send the message
    
       Else
       ' if user does not want to send the message, ...
    
           .Display
           ' display the message
    
       End If
    
   End With
    
   Set objMessage = Nothing
   ' clear for memory
    
   Set objOutlook = Nothing
   ' clear for memory
    
   End Sub

3. Create function to generate the body text from the table, query, whatever you're wanting to mail.
4. Call Mail_small_Text_Outlook (or the modified mailSend), including the body generated from the 3rd step.
5. Profit.

[zmbd]

Corwin Moyne:

Poet has provided some nice code; however, it (IMHO) isn't usable for your application "as is" and will need some considerable modification.

However, if you can post the code that you are currently using to create and send your email, along with the information that ADezii has requested, I'm sure that we can provide some guidance/direction.

When you post your code please select the text and click on the [CODE/] formatting button to place the required [code] tags around it (that way the code is in the nice code box - (^_^) thnx) In general, any formatted (think table) or other code (such as SQL, etc...) should be formated this way.