Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 58
  • Last Modified:

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] & ";"
.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
jaspence
Asked:
jaspence
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )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:

https://www.experts-exchange.com/articles/4316/Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html
0
 
Helen FeddemaCommented:
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
 
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now