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 http://snipplr.com/view/10041/vba-send-outlook-email/

    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")
   
    MyEmails.MoveFirst
    Do Until MyEmails.EOF
        Set objOutlookRecip = .Recipients.Add(MyEmails!EMailAddress)
        MyEmails.MoveNext
    Loop
    MyEmails.Close
    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")
   
    MyReports.MoveFirst
    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)  
         MyReports.MoveNext
    Loop
    MyReports.Close


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

             ' Should we display the message before sending?
             If DisplayMsg Then
                 .Display
             Else
                 .Save
                 .Send
             End If
          End With
          Set objOutlook = Nothing
          Me.SetFocus
   
End Sub
qryReports.xls
qryEmail.xls
Fritz PaulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
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?

0
Fritz PaulAuthor Commented:
Yes, and with or without the quotes, I get the same message.
0
Patrick MatthewsCommented:
fritzpaul,

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:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html

Patrick
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Patrick MatthewsCommented:
I'm not sure why, but when I change:

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

to

         .Attachments.Add(MyReports!ReportName)  

the code works for me.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)

to

        RepName = MyReports!ReportName
        Set objOutlookAttach = .Attachments.Add(RepName)
0
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...
1.
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...
res.Close
Set rst=Nothing

3. Use a standard naming convention for your Objects and variables
http://en.wikipedia.org/wiki/Leszynski_naming_convention
http://www.xoc.net/standards/default.asp
ex:
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.

;-)

JeffCoachman



Access-EEQ26914572Send-Multiple-.mdb
0
Fritz PaulAuthor Commented:
This also works, Whish I knew why not my initial alternative.
Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.