Sending Email

[digitaldman]

good day to all,

I am a rather new Access programmer working on a modest database. The goal of this database is to track leads (customer calls) into our call center, there are a few fields:
Customer Name
Email Address
Lead
OrderNumber
Sale (Checkbox)

The idea is that when the Sale checkbox is checked and the rep saves the record (through a command button) that Access will email to the customer a copy of our companies terms and conditions. The email body it self must include the customers name and order number. I am at a loss on how to do this. I have read several other threads regarding sending email but none of them address the latter part of my need. Adding the customers name and order number into the email it self.

 

[mph1]

Have you tried looking at Emailing From Access in the FAQ section of Tek-Tips??

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[Terpsfan]

If you want to merely send the email in the body of the email document and not as an attachment, what you are asking if very doable. What you need to do is create a recordset and as you loop through the recordset record by record, you add the fields of the recordset to a string variable. The string variable gets continually appended as you loop through records. After you have reached the end of the recordset, then you use the string variable as your email message. In your case it seems like you only have one record to deal with at a time.

I suggest using the Outlook method of sending emails as opposed to the docmd.sendobject method. Docmd.sendobject is limited in the text length and also is quirky, sometimes it works, other times it doesn't.

I will post an example for you tonight because I don't have this example at my disposal right now.

 

[Terpsfan]

Here is an example without using a recordset. I was thinking since you are only sending info regarding the current record, you don't need a recordset. The first thing you want to do is set a reference to Microsoft Outlook Object Library.

Private Function SendEmail() As Boolean
   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment
   Dim strRecipient As String
   
   
   
   
   
   strRecipient = "yourrecipientname"
   
   Dim strMessage As String
   strMessage = "Here is your order information:"
   strMessage = strMessage & vbCrLf & vbCrLf & "Customer Name " & vbTab & Me.txtCustomerName
   strMessage = strMessage & vbCrLf & "Order Number      " & vbTab & Me.txtOrderNumber
   
   
   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add(strRecipient)
      objOutlookRecip.Type = olTo

      ' Add the CC recipient(s) to the message.
    '  Set objOutlookRecip = .Recipients.Add("<yourrecipientname>")
    '  objOutlookRecip.Type = olCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Order Information"
      .Body = strMessage
      .Importance = olImportanceHigh  'High importance

     
      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
      .Send

   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Function
                

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.chkSale Then
        SendEmail
    End If
End Sub

 

[digitaldman]

Thanks for everyones help so far. i got it up and working. however, the one issue I am still having is the when I try to send the email, I get the MS pop-up that alerts me to the fact that something is trying to send an email and I have to way 5 secords to press ok to send. Is there anyway to eliminate this?

 

[Terpsfan]

That has to do with a security patch for Microsoft Outlook. There is a program called Click Yes that will send the email without the prompt.

http://www.express-soft.com/mailmate/clickyes.html