469,352 Members | 1,661 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

How to send Email with Attachment(s) from within MS Access?

106 100+
Hi,
I would like to send emails with attachments from within MSACESS 2003.

I have used Microsofts example that allows to send an email with an attahcment to many recipients.
http://support.microsoft.com/?id=318881#appliesto

What I would like to do is to send Many Attachments to many Recipients. I am failing miserably from the word go.

My main table that stores email address is tbl_Accident_CaseHandler
I filter the emails to select the required email address with qryCaseHandler
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Accident_CaseHandler.CaseHandlerID, tbl_Accident_CaseHandler.AccidentID, tbl_Accident_CaseHandler.ContactID, tbl_Accident_CaseHandler.SupplierID, tbl_Accident_CaseHandler.CaseHandlerName, tbl_Accident_CaseHandler.CaseHandlerPhone, tbl_Accident_CaseHandler.CaseHandlerEmail AS EmailAddress, tbl_Accident_CaseHandler.CaseHandlerNotes
  2. FROM tbl_Accident_CaseHandler
  3. WHERE (((tbl_Accident_CaseHandler.AccidentID)=[Forms]![AccidentClaims]![AccidentID]) AND ((tbl_Accident_CaseHandler.ContactID)=[Forms]![AccidentClaims]![txtContactID]));
  4.  
I modified microsofts Module to include my queries and form but even in the most basic form, i get the error '3061 Too Few Parameters. Expected 2.

I have not got near modifying the attachments section. :)

My attachment paths are stored in qryMailingAttachment as DocPath
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_AccDocuments.DocumentID, tbl_AccDocuments.AccidentID, tbl_AccDocuments.ContactID, tbl_AccDocuments.DocPath, tbl_AccDocuments.DocDescription, tbl_AccDocuments.SendAsAttachment
  2. FROM tbl_AccDocuments
  3. WHERE (((tbl_AccDocuments.AccidentID)=[Forms]![AccidentClaims]![AccidentID]) AND ((tbl_AccDocuments.ContactID)=[Forms]![AccidentClaims]![txtContactID]) AND ((tbl_AccDocuments.SendAsAttachment)=True));
  4.  
These queries are producing the required results.
I am not sure where to go from here.
Thanks in advance for excellent support that is always provided on BYTES.

p.s. Is there a method to collect emails within MSACESS

Micrsofts Module (Modified)
Expand|Select|Wrap|Line Numbers
  1. Sub SendMessages(Optional AttachmentPath)
  2.  
  3. 'Information Taken from the followinh microsoft website
  4. 'http://support.microsoft.com/?id=318881#appliesto
  5. 'Add Microsoft Outlook Object 11.0 Library
  6.  
  7. 'Addition for Attachments
  8. 'http://en.allexperts.com/q/Using-MS-Access-1440/2010/2/Sending-Multiple-Attachments-via.htm
  9.  
  10.   Dim MyDB As Database
  11.   Dim MyRS As Recordset
  12.   Dim objOutlook As Outlook.Application
  13.   Dim objOutlookMsg As Outlook.MailItem
  14.   Dim objOutlookRecip As Outlook.Recipient
  15.   Dim objOutlookAttach As Outlook.Attachment
  16.   Dim TheAddress As String
  17.  
  18.   Set MyDB = CurrentDb
  19.   Set MyRS = MyDB.OpenRecordset("qryMailingList")
  20.   MyRS.MoveFirst
  21.  
  22.   ' Create the Outlook session.
  23.   Set objOutlook = CreateObject("Outlook.Application")
  24.  
  25.   Do Until MyRS.EOF
  26.   ' Create the e-mail message.
  27.   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
  28.   TheAddress = MyRS![EmailAddress]
  29.  
  30.      With objOutlookMsg
  31.         ' Add the To recipients to the e-mail message.
  32.         Set objOutlookRecip = .Recipients.Add(TheAddress)
  33.         objOutlookRecip.Type = olTo
  34.  
  35.         ' Add the Cc recipients to the e-mail message.
  36.         If (IsNull(Forms!SendEmail!ccAddress)) Then
  37.         Else
  38.            Set objOutlookRecip = .Recipients.Add(Forms!SendEmail!ccAddress)
  39.            objOutlookRecip.Type = olCC
  40.         End If
  41.  
  42.         ' Set the Subject, the Body, and the Importance of the e-mail message.
  43.         .Subject = Forms!SendEmail!Subject
  44.         .Body = Forms!SendEmail!MainText
  45.         .Importance = olImportanceHigh  'High importance
  46.  
  47.         'Add the attachment to the e-mail message.
  48.         If Not IsMissing(AttachmentPath) Then
  49.            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
  50.         End If
  51.  
  52.         ' Resolve the name of each Recipient.
  53.         For Each objOutlookRecip In .Recipients
  54.            objOutlookRecip.Resolve
  55.            If Not objOutlookRecip.Resolve Then
  56.              objOutlookMsg.Display
  57.            End If
  58.         Next
  59.         .Send
  60.       End With
  61.       MyRS.MoveNext
  62.    Loop
  63.    Set objOutlookMsg = Nothing
  64.    Set objOutlook = Nothing
  65. End Sub
Dec 18 '10 #1

✓ answered by tasawer

Hi Adezzi,

I closed my eyes and said it 10 times... but guess what it did not work. Sorry.
its probably not your code because the error remains the same.. 3061 too few parameters. Expected 2.

I have experienced this error in my sql commands when the command is expecting a value (Expected 1) or 2 (Expected 2) but does not get it.

Perhaps, the error is in my queries!
Nevertheless, when I put the following code behind click property of command button, desired values are shown.
Expand|Select|Wrap|Line Numbers
  1. Me.MainText = Me.MessageBody & vbCrLf & vbCrLf & Me.Footer
  2.  
  3.     DoCmd.OpenQuery "qryCaseHandler", acViewNormal
  4.     DoCmd.OpenQuery "qryMailingAttachment", acViewNormal
  5.     Debug.Print Me.MainText
  6.     Debug.Print Me.Subject

7 12367
ADezii
8,800 Expert 8TB
Just subscribing for now...
Dec 18 '10 #2
ADezii
8,800 Expert 8TB
I created some Code for you which hopefully will be helpful (say that fast 10 times!). It Sends Multiple Attachments to Multiple Recipients via Outlook, based on your specifications. Any questions, feel free to ask.
Expand|Select|Wrap|Line Numbers
  1. 'Provides the Send Mail automation. Send an E-Mail and
  2. 'Attachments from Access via Outlook to Multiple Recipients
  3. Dim oLook As Object
  4. Dim oMail As Object
  5. Dim olns As Outlook.NameSpace
  6. Dim MyDB As DAO.Database
  7. Dim rstAddr As DAO.Recordset        'To hold E-Mail Addresses of Recipients
  8. Dim rstAtt As DAO.Recordset         'To hold Path of all Attachments
  9. Dim strBuild As String
  10.  
  11. Set oLook = CreateObject("Outlook.Application")
  12. Set olns = oLook.GetNamespace("MAPI")
  13. Set oMail = oLook.CreateItem(0)
  14.  
  15. Set MyDB = CurrentDb
  16. Set rstAddr = MyDB.OpenRecordset("qryCaseHandler", dbOpenForwardOnly)
  17. Set rstAtt = MyDB.OpenRecordset("qryMailingAttachments", dbOpenForwardOnly)
  18.  
  19. With oMail
  20.   With rstAddr
  21.     Do While Not .EOF
  22.       strBuild = strBuild & ![EMailAddress] & ";"       'Build Recipients List
  23.         .MoveNext
  24.     Loop
  25.   End With
  26.     .To = Left$(strBuild, Len(strBuild) - 1)            'Strip Trailing ';'
  27.  
  28.     .Body = "Text in the Body of the E-Mail"
  29.  
  30.     .Subject = "Some Interesting Subject"
  31.  
  32.   With rstAtt
  33.     Do While Not .EOF
  34.       oMail.Attachments.Add Trim(![DocPath])
  35.         .MoveNext
  36.     Loop
  37.   End With
  38.  
  39.     .Display          'Displays the Outlook Screen instead of automatically Sending
  40.                       'the Email. You can change this to .Send to Auto Send
  41. End With
  42.  
  43. Set oMail = Nothing
  44. Set oLook = Nothing
  45.  
  46. rstAtt.Close
  47. rstAddr.Close
  48. Set rstAtt = Nothing
  49. Set rstAddr = Nothing
Dec 18 '10 #3
tasawer
106 100+
Hi Adezzi,

I closed my eyes and said it 10 times... but guess what it did not work. Sorry.
its probably not your code because the error remains the same.. 3061 too few parameters. Expected 2.

I have experienced this error in my sql commands when the command is expecting a value (Expected 1) or 2 (Expected 2) but does not get it.

Perhaps, the error is in my queries!
Nevertheless, when I put the following code behind click property of command button, desired values are shown.
Expand|Select|Wrap|Line Numbers
  1. Me.MainText = Me.MessageBody & vbCrLf & vbCrLf & Me.Footer
  2.  
  3.     DoCmd.OpenQuery "qryCaseHandler", acViewNormal
  4.     DoCmd.OpenQuery "qryMailingAttachment", acViewNormal
  5.     Debug.Print Me.MainText
  6.     Debug.Print Me.Subject
Dec 19 '10 #4
ADezii
8,800 Expert 8TB
Try a different approach and see what happens.
  1. Resolve the Parameters initially.
  2. Change the SQL of the Query.
  3. Now, you should be able to create a Recordset based on the Query.
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String
    2. Dim qdf As DAO.QueryDef
    3.  
    4. strSQL = "SELECT tbl_Accident_CaseHandler.CaseHandlerID, tbl_Accident_CaseHandler.AccidentID, " & _
    5.          "tbl_Accident_CaseHandler.ContactID, tbl_Accident_CaseHandler.SupplierID, " & _
    6.          "tbl_Accident_CaseHandler.CaseHandlerName, tbl_Accident_CaseHandler.CaseHandlerPhone, " & _
    7.          "tbl_Accident_CaseHandler.CaseHandlerEmail AS EmailAddress, tbl_Accident_CaseHandler.CaseHandlerNotes " & _
    8.          "FROM tbl_Accident_CaseHandler " & _
    9.          "WHERE tbl_Accident_CaseHandler.AccidentID = " & [Forms]![AccidentClaims]![AccidentID] & " AND " & _
    10.          "tbl_Accident_CaseHandler.ContactID= " & [Forms]![AccidentClaims]![txtContactID] & ";"
    11.  
    12. Set qdf = CurrentDb.QueryDefs("qryCaseHandler")
    13.  
    14. qdf.SQL = strSQL
  4. P.S. - I assumed that the ID Fields are Numeric.
Dec 19 '10 #5
tasawer
106 100+
Hi Adezzi,

Here is an update,

As I suspected, I added the following lines to your code
Expand|Select|Wrap|Line Numbers
  1.   Dim lngAccidentID As Long
  2.   Dim lngContactID As Long
  3.   Dim strSQlEmail As String
  4.   Dim strSqlAttachments As String
  5.  
  6.   lngAccidentID = Forms!AccidentClaims!AccidentId
  7.   lngContactID = Forms!AccidentClaims!txtContactID
  8.  
  9.   strSQlEmail = "SELECT tbl_Accident_CaseHandler.CaseHandlerEmail " & _
  10.         "FROM tbl_Accident_CaseHandler " & _
  11.         "WHERE (AccidentID= " & lngAccidentID & " AND ContactID= " & lngContactID & " );"
  12.  
  13.   strSqlAttachments = "SELECT tbl_AccDocuments.DocPath " & _
  14.         "FROM tbl_AccDocuments " & _
  15.         "WHERE (AccidentID= " & lngAccidentID & " AND ContactID= " & lngContactID & " AND SendAsAttachment=-1);"
  16.  
and I edited these lines..
Expand|Select|Wrap|Line Numbers
  1.     Set MyDB = CurrentDb
  2.     Set rstAddr = MyDB.OpenRecordset(strSQlEmail, dbOpenForwardOnly)
  3.     Set rstAtt = MyDB.OpenRecordset(strSqlAttachments, dbOpenForwardOnly)
and now it works a treat. I am not sure why it will not read the queries. do you?
Dec 19 '10 #6
tasawer
106 100+
Great men think alike....
Dec 19 '10 #7
ADezii
8,800 Expert 8TB
You need to properly Evaluate the Query Parameters, prior to Opening a Recordset based on the Query, as such:
Expand|Select|Wrap|Line Numbers
  1. 'Form must be Open in order for Parameters to be properly evaluated
  2. Dim qdf As DAO.QueryDef
  3. Dim prm As Parameter
  4. Dim rst As DAO.Recordset
  5.  
  6. Set qdf = CurrentDb.QueryDefs("qryCaseHandler")
  7.  
  8. For Each prm In qdf.Parameters
  9.   prm.Value = Eval(prm.Name)
  10. Next
  11.  
  12. Set rst = qdf.OpenRecordset
  13.  
  14. rst.MoveLast
  15.  
  16. MsgBox rst.RecordCount
Dec 19 '10 #8

Post your reply

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

Similar topics

13 posts views Thread by joe215 | last post: by
6 posts views Thread by Alexander Deruwe | last post: by
1 post views Thread by Devonish | last post: by
3 posts views Thread by JaffaCakes | last post: by
14 posts views Thread by John | last post: by
1 post views Thread by Mel via AccessMonster.com | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.