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?
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
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?

0
 
Fritz PaulAuthor Commented:
Yes, and with or without the quotes, I get the same message.
0
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:
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
 
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
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.