Solved

Sending an email in Access 2010

Posted on 2014-04-25
4
282 Views
Last Modified: 2014-05-09
I have a form that I enter work parts into (see attatched file). From that data I am wanting to create several macros to make my life a lot easier. To order parts I send an email with the info that is in the database.

I would like to have a button when clicked would extract data from my database such as Part Number from a table called Assets and Account and Account Address from a table called Accounts and add it to an email template. The information needed are on multiple tables. I hope I have described this problem well enough.

Is that possible?
Thanks in advance.
Assets-Form.doc
0
Comment
Question by:chrisreville
4 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40024288
If you are only sending a single email at a time, the Access SendObject method should meet your needs.  You can either create a report and send the report as an attachment, or you could create the message text based upon a query (or queries) go generate the data you are interested in.
0
 
LVL 84
ID: 40024382
As Dale said, SendObject is the simplest way to send an Email from Access. To use it:

DoCmd.SendObject acSendNoObject, , , "someoneone@somewhere.com", , , "Hello", "Hi my name is Bob"

If you need to create the Body, you can use a recordset, as Dale mentions:

Dim sBody As String
Dim rst As DAO.Recordset

Set rst = Currentdb.OpenRecordset("SELECT * FROM Address WHERE ID=" & SomeIDValue)

sBody = "Please send the following parts to the address here: " & vbCrLf & vbCrLf & rst("Name") & vbCrLf & rst("Street") & vbCrLf & rst("City") & " " & rst("State") & ", " & rst("Zip") & vbcCrLf & vbCrLf

Set rst = Currentdb.OpenRecordset("SELECT * FROM Material WHERE ID=" & Me.MaterialID)

Do until rst.EOF
  sBody = sBody & rst("Material") & " " & rst("Description")
  rst.MoveNext
Loop

Open in new window

From there you'd use sBody as the Body of your message.

DoCmd.SendObject acSendNoObject, , , "someoneone@somewhere.com", , , "Hello", sBody
0
 

Author Comment

by:chrisreville
ID: 40025168
Below is the email that must be in this format. I would like to use my database to lookup a part, Then click the button that says Order Part (See Original Attachment) from there it would open up my Accounts Form so I can choose the account it is for then click Save or whatever and it take the Part Number from the Parts Form and the Ship To address and Ship to Contact from the Accounts form and open my email and fill in the email like below.


The email needs to look like this:

Please order the following part(s):

PART NUMBER:   PULL FROM PARTS FORM
QTY NEEDED:    1
SHIP TO:   My Home PULL FROM ACCOUNTS FORM
SHIP TO CONTACT:   PULL FROM ACCOUNTS FORM
REQUESTED LAND DATE:   4/25/2014

Thanks….

So it would look like this when i am ready to send.

Please order the following part(s):

PART NUMBER:   14-7080
QTY NEEDED:    1
SHIP TO: Sacret Heart Hospital
SHIP TO CONTACT:   Joe Blow
REQUESTED LAND DATE:   4/25/2014

Thanks….
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 40026211
Make a query that returns the values you need from the Parts and Accounts form, or get the values from open forms, save them to variables, then create the body of the email line by line.  Here is something similar I did:

   strCustomerName = Nz(frm![FirstNameFirst])
   strCode = Nz(frm![Code])
   strMessageSubject = strStreetAddress & ", " & strCity & " - Work Info" _
      & " - Project start date: " & frm![ProjectStart]
   
   'Create message component strings
   strMessageDate = "This message is to advise you that XYZCorp will be starting " _
      & strArticle & " " & strProjectType & " project on " & dteProjectStart _
      & " at:" & vbCrLf & vbCrLf
   Debug.Print "Message Date: " & strMessageDate
   
   strMessageAddress = strLocationCompany & vbCrLf _
      & strStreetAddress & vbCrLf _
      & strLocationCityStateZip & vbCrLf & vbCrLf
   Debug.Print "Message Address: " & strMessageAddress
   
   Debug.Print "Message text: " & strMessageText
   
   strBody = strMessageDate & strMessageAddress & strMessageText _
      & vbCrLf & vbCrLf & strInvoice
   Debug.Print "Body: " & strBody 

Open in new window


This is for a plain text email; HTML emails are more complicated.  Here is an example of creating an HTML email:

Public Sub CreateEmails()
'Created by Helen Feddema 31-Jan-2010
'Last modified by Helen Feddema 7-Feb-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim dtePickup As Date
   Dim dteSold As Date
   Dim msg As Outlook.MailItem
   Dim rstAll As DAO.Recordset
   Dim rstSingle As DAO.Recordset
   Dim strBody As String
   Dim strCompany As String
   Dim strEMail As String
   Dim strFCFNumber As String
   Dim strGrade1 As String
   Dim strGrade2 As String
   Dim strHeader As String
   Dim strHTMLBody As String
   Dim strLine1 As String
   Dim strLine2 As String
   Dim strNotes As String
   Dim strQueryAll As String
   Dim strQuerySingle As String
   Dim strSignature As String
   Dim strSubject As String
   
   Set dbs = CurrentDb
   strQueryAll = "qrySendEmails"
   Set rstAll = dbs.OpenRecordset(strQueryAll)
   strQuerySingle = "qryTempEmail"
   strSignature = "</table><br><br><font face='Book Antiqua', size=5>" _
      & "<align='left'><u>John Doe</u></font><br>" _
      & "<font face='Arial', size=3><br>" _
      & "VP of Sales and Marketing<br>" _
      & "JD Inc.<br>" _
      & "Ph: 555-112-9601<br>" _
      & "Fx: 555-112-9422<br>"
   Debug.Print "Signature: " & strSignature
   
   Do While Not rstAll.EOF
      'Create filtered recordset for this customer
      lngSupplierID = Nz(rstAll![CustomerID])
      
      If lngSupplierID <> 0 Then
         strSQL = "SELECT * FROM " & strQueryAll & " WHERE " _
            & "[CustomerID] = " & lngSupplierID & ";"
      End If
   
      Debug.Print "SQL for " & strQuerySingle & ": " & strSQL
      lngCount = CreateAndTestQuery(strQuerySingle, strSQL)
      Debug.Print "No. of items found: " & lngCount
      If lngCount = 0 Then
         strPrompt = "No records found; canceling"
         strTitle = "Canceling"
         MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
         GoTo ErrorHandlerExit
      Else
         'Create starter HTML text for this customer
         strHTMLBody = ""
         strHeader = "<p><img border='0' src='Image.jpg' " _
	    	& "width='559' height='120'></p> & "<font face='Arial', size=3>" _
            & "Please schedule the following: <br><br>" _
            & "<table width='791' border='1'>" _
            & "   <tr>" _
            & "      <td width='283'height='26' nowrap valign='bottom'" _
            & "align='left'><font face='Arial', size=3><strong>Grade</strong></font></td>" _
            & "      <td width='127' height='26'nowrap valign='bottom'" _
            & "align='left'><font face='Arial', size=3><strong>FCF Pickup #</strong></font>" _
            & "      <td width='144'height='26' nowrap valign='bottom'" _
            & "align='left'><font face='Arial', size=3><strong>Pickup Date</strong></font></td>" _
            & "      <td width='237'height='26' nowrap valign='bottom'" _
            & "align='left'><font face='Arial', size=3><strong>Notes</strong></font></td>" _
            & "   </tr>"

         Set rstSingle = dbs.OpenRecordset(strQuerySingle)
         strEMail = Nz(rstSingle![EmailAddress])
         strCompany = Nz(rstSingle![strCompany])
         strSubject = "Loads for " & strCompany
         
         'Create email for this customer
         Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEMail
         msg.Subject = strSubject
         msg.BodyFormat = olFormatHTML
         
         'Process loads per customer
         Debug.Print "Processing load(s) for " & strCompany
         strBody = ""
         
         Do While Not rstSingle.EOF
            strLine1 = ""
            strLine2 = ""
            strGrade1 = ""
            strGrade2 = ""
            strSlipNo = Nz(rstSingle![SlipNo])
            dteSold = Nz(rstSingle![DateSold])
            dtePickup = Nz(rstSingle![ScheduledPickup])
            strFCFNumber = Nz(rstSingle![FCFNumber])
            strGrade1 = Nz(rstSingle![Grade1])
            Debug.Print "Grade 1: " & strGrade1
            strGrade2 = Nz(rstSingle![Grade2])
            Debug.Print "Grade 2: " & strGrade2
            strNotes = Nz(rstSingle![Notes])
            
            'Create line of body text
            strLine1 = "<font face='Arial', size=3" _
               & "   <tr>" _
               & "      <td width='283'valign='bottom' align='left'>" & strGrade1 & "</td>" _
               & "      <td width='127' valign='bottom' align='left'>" & strFCFNumber _
               & "      <td width='144'valign='bottom' align='left'>" _
               & Format(dtePickup, "m/d/yyyy") & "</td>" _
               & "      <td width='237'valign='bottom' align='left'>" & strNotes & "</td>" _
               & "   </tr></font>"
            Debug.Print "Line 1: " & strLine1
            
            If strGrade2 <> "" Then
               strLine2 = "<font face='Arial', size=3>" _
                  & "   <tr>" _
                  & "      <td width='283'valign='bottom' align='left'>" & strGrade2 & "</td>" _
                  & "      <td width='127' valign='bottom' align='left'>" & strFJDNumber _
                  & "      <td width='144'valign='bottom' align='left'>" _
                  & Format(dtePickup, "m/d/yyyy") & "</td>" _
                  & "      <td width='237'valign='bottom' align='left'>" _
                  & strNotes & "</font></td>" _
                  & "   </tr>"
            End If
            Debug.Print "Line 2: " & strLine2
         
            strHTMLBody = strHTMLBody & strLine1 & strLine2
            rstSingle.MoveNext
         Loop
         
         strHTMLBody = strHeader & strHTMLBody & strSignature
         Debug.Print "Message HTML body: " & strHTMLBody
         
         msg.HTMLBody = strHTMLBody
         msg.Display
         
      End If
      rstAll.MoveNext
   Loop
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in CreateEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now