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

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] & ";"
End With

strmgrBuild = strBuild + ""

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

DoCmd.SendObject acSendReport, "XH-772_4_Circulation", "PDF", strmgrBuild, strCcBuild, "", "Due Training"

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

Set rstEmail = Nothing

End Function
Who is Participating?
Helen FeddemaConnect With a Mentor Commented:
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
   End With
   Exit Sub

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

End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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:
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Helen's comment included code to resolve the issue, and mine included a link to an EE article that also provided a sample database.
All Courses

From novice to tech pro — start learning today.