Solved

Adding multiple attachments in automated email from MS Access to MS Outlook

Posted on 2016-10-25
3
34 Views
Last Modified: 2016-11-16
Please help, all I want to do is to add code that would allow me to attach files from network specific locations. The code below works to attach the  XH-772_4_Circulation report from Access using the DoCmd.SendObject, but I also want to attach the file at the network location: \\BORG\UserShar\Nadcap\Operator Training\Current Procedures\PE-4014.pdf

Public Function sendEmail()
Dim MyDB As DAO.Database
Dim rstEmail As DAO.Recordset
Dim rstCcEmail As DAO.Recordset
Dim strBuild As String
Dim strSQL As String
Dim strCcSQL As String
Dim strmgrBuild As String

Set MyDB = CurrentDb

strSQL = "SELECT Supervisor_Name " & _
"FROM Due_Supervisor_Email;"

strCcSQL = "SELECT Member " & _
"FROM Due_Member_Email;"

Set rstEmail = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
Set rstCcEmail = MyDB.OpenRecordset(strCcSQL, dbOpenForwardOnly)

With rstEmail
Do While Not .EOF
strBuild = strBuild & ![Supervisor_Name] & ";"
.MoveNext
Loop
End With

strmgrBuild = strBuild + "jspenc@gmail.com"

With rstCcEmail
Do While Not .EOF
strCcBuild = strCcBuild & ![Member] & ";"
.MoveNext
Loop
End With

DoCmd.SendObject acSendReport, "XH-772_4_Circulation", "PDF", strmgrBuild, strCcBuild, "jeff.spencer@gmail.com", "Due Training"

' File location that I want attached: \\BORG\UserShar\Nadcap\Operator Training\Current Procedures\PE-4014.pdf

rstEmail.Close
Set rstEmail = Nothing

End Function
0
Comment
Question by:jaspence
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points (awarded by participants)
ID: 41858943
You can't attach external items using the SendObject method. You'll have to use automation for that. Here's an EE article that shows how, along with a sample database:

https://www.experts-exchange.com/articles/4316/Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html
0
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 250 total points (awarded by participants)
ID: 41863942
You need the file name, not just the path (unless you want to iterate through a folder, and attach all files -- let me know if you need code for that).  Here is some code to attach a single attachment to an outgoing email:

Private Sub SendMailAttachment
'Created by Helen Feddema 5-19-2000
'Last modified 16-May-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim msg As Outlook.MailItem
   Dim strFileName As String
   Dim strFilePath As String
   
   strFilePath = Application.CurrentProject.Path
   strFileName = strFilePath & "\Contacts.csv"
   
   'Save Contacts table as a text file in same folder as database
   DoCmd.TransferText acExportDelim, "Contacts Export Specification", _
      "tblContacts", strFileName, True
      
   'Create new mail message and attach text file to it
   Set msg = appOutlook.CreateItem(olMailItem)
   With msg
      .To = "John Doe"
      .Subject = "Contacts file you requested"
      .Body = "This file was exported from tblContacts on " & _
         Date & "." & vbCrLf & vbCrLf
      .Attachments.Add strFileName
      .Display
   End With
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 84
ID: 41889421
Helen's comment included code to resolve the issue, and mine included a link to an EE article that also provided a sample database.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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