Add multiple attachments from a recordset to email through MS Outlook.

I am making an application to send multiple attachments to multiple recipients through MS Outlook.

The part where I create the multiple recipients works fine, but the part where I create the multiple attachments give the following error.

I make use of recordsets based on select queries.

I attach the exports of both queries.

Please see if you can spot the problem and give a solution

Error message:
Run-time erroe '-693498701 (d6aa0cb3)':
Operation is not supported for this type of object.

See in the code below where this message refers to.

This is the code:

Sub SendMessage(DisplayMsg As Boolean)
' Adapted from

    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment

        ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

        ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
'   Add the To recipient(s) to the message.

    Dim MyDb As Database

    Dim MyEmails As Recordset
    Set MyDb = DBEngine.Workspaces(0).Databases(0)
    Set MyEmails = MyDb.OpenRecordset("qryEmail")
    Do Until MyEmails.EOF
        Set objOutlookRecip = .Recipients.Add(MyEmails!EMailAddress)
    objOutlookRecip.Type = olTo

'    Set the Subject and Body of the message.
    .Subject = "Hi, This is an Automation e-mail test with MSAccess"
    .Body = "This is the body of the message. Also note the attachment." & vbCrLf & vbCrLf

'       Add attachments to the message.

' xxxxxxxxxxxxxxxxxxxxxxxxxxxxx THIS IS WHERE THE PROBLEM BEGINS

'The following lines work
'     Set objOutlookAttach = .Attachments.Add("D:\TOPASS\Demo1\rptOutput1.pdf")
'     Set objOutlookAttach = .Attachments.Add("D:\TOPASS\Demo1\rptOutput1_2.pdf")

'but when I want to read the attachment paths from a select query i get mentioned error message.
    Dim MyDb As Database
    Dim MyReports As Recordset
    Set MyDb = DBEngine.Workspaces(0).Databases(0)
    Set MyReports = MyDb.OpenRecordset("qryReports")
    Do Until MyReports.EOF
'>>>>>>>>>>>>>>>>>>>>>>>>> NEXT IS THE LINE WHERE THE ERROR OCCURS  (Operation is not supported for this type of object.)
         Set objOutlookAttach = .Attachments.Add(MyReports!ReportName)  

             ' Resolve each Recipient's name.
             For Each objOutlookRecip In .Recipients

             ' Should we display the message before sending?
             If DisplayMsg Then
             End If
          End With
          Set objOutlook = Nothing
End Sub
Fritz PaulAsked:
Who is Participating?
Patrick MatthewsConnect With a Mentor Commented:
I'm not sure why, but when I change:

         Set objOutlookAttach = .Attachments.Add(MyReports!ReportName)  



the code works for me.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
From looking at teh Excel file I have a question:
Does the value in MyReports!ReportName have the quotes wrapped around the path stored in the data?

Fritz PaulAuthor Commented:
Yes, and with or without the quotes, I get the same message.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Patrick MatthewsCommented:

So, you are saying that even when you remove the singlequotes and doublequotes in the report names in your query you still get that error?  That shouldn't happen so long as D:\TOPASS\Demo1\rptOutput1.pdf and D:\TOPASS\Demo1\rptOutput1_2.pdf are valid paths.

BTW, if you automate Outlook a lot from other apps, you might want to have a look at my article here:

Fritz PaulAuthor Commented:
OK Thanks, I got the answer, although I don't understand the reason.

I  changed the line
         Set objOutlookAttach = .Attachments.Add(MyReports!ReportName)


        RepName = MyReports!ReportName
        Set objOutlookAttach = .Attachments.Add(RepName)
Jeffrey CoachmanMIS LiasonCommented:
<.Attachments.Add(MyReports!ReportName) >
...Well, you cannot email an Access Report directly.

You must email the report in a certain format

But first, a little cleanup...
Try not to "Recycle" your object names
So make the first one "MyDb1"
...and the second occurrence: "MyDb2"

2. You don't appear to be cleaning up your Recordsets, properly...
Set rst=Nothing

3. Use a standard naming convention for your Objects and variables
Dim strReportName as string

That all being said...
Try something like the attached sample.
I am sure that based on what you have so far, you can adapt this working code to function properly in your database.



Fritz PaulAuthor Commented:
This also works, Whish I knew why not my initial alternative.
Thanks a lot.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.