Solved

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

Posted on 2016-10-25
3
11 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
  • 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Use email signature images to promote corporate certifications and industry awards.
Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 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

14 Experts available now in Live!

Get 1:1 Help Now