By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

For each recordset create a mail with the query as attachement

P: 2
I'm trying to create for each rs a mail item. This mail item should have a temporary query as attachement. Via TransferSpreadSheet I load my temporary Query into a Folder.
This is working. But now, after creating the mail, my attachement is always the same.

Question: What am I doing wrong, with my actual VBA?
Expand|Select|Wrap|Line Numbers
  1. Sub ExcelExportuSenden()
  2.  
  3. Dim day As Integer
  4. day = Weekday(Date, vbSunday)
  5. Dim olApp As Outlook.Application
  6. Dim toMulti, waarde As String
  7. Dim mItem As Outlook.MailItem  ' An Outlook Mail item
  8. Dim dbs As Database
  9. Dim qdfTemp As QueryDef
  10. Dim qdfNew As QueryDef
  11. Dim originalSql As String
  12. Dim Identified_name As Recordset
  13. Dim qdf As DAO.QueryDef
  14. Set dbs = CurrentDb
  15. Set olApp = CreateObject("Outlook.Application")
  16. Set mItem = olApp.CreateItem(olMailItem)
  17. Dim rs  As Recordset
  18.  
  19. Set rs = CurrentDb.OpenRecordset("Mailrecipient")  'Get name for the email recipient
  20.  
  21. If rs.RecordCount > 0 Then
  22.     rs.MoveFirst
  23.     Do Until rs.EOF
  24.         With mItem
  25.             Set mItem = olApp.CreateItem(olMailItem)
  26.             .BodyFormat = olFormatHTML
  27.             toMulti = rs![email]
  28.             waarde = toMulti
  29.             For Each qdf In dbs.QueryDefs
  30.                 If qdf.Name = "inquiry" Then
  31.                    dbs.QueryDefs.Delete "inquiry"
  32.                    Exit For
  33.                 End If
  34.             Next
  35.  
  36.             Set qdfTemp = dbs.CreateQueryDef("inquiry")
  37.             With dbs
  38.                'Run query on selected Name product manager
  39.                 qdfTemp.SQL = "SELECT * FROM [query_each_supplier] WHERE [supplier] = '" & rs![supplier] & "'"
  40.                 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "inquiry", "Q:\LU\Test\inquiry.xlsx", True
  41.  
  42.             End With
  43.  
  44.         .To = toMulti
  45.         MsgBox toMulti
  46.         .Subject = "inquiry"
  47.         .HTMLBody = ""
  48.         .Display
  49.         .Attachments.Add ("Q:\LU\Test\Anfrage_zur_Ausschreibung.xlsx")
  50.  
  51.     End With
  52.  
  53.        rs.MoveNext
  54.     Loop
  55. Else
  56.     MsgBox "No email address!"
  57. End If
  58. olApp.Quit
  59. Set olApp = Nothing
  60. Exit Sub
  61.  
  62. End Sub
Many thanks for your help!
Dec 14 '17 #1

✓ answered by NeoPa

Please understand that our rules prohibit multiple questions in a single thread. However, it is perfectly acceptable to link to one thread while asking a new question in another. Bear in mind some may be put off by that but repeating the details is also acceptable. So, we won't answer your second question here and I will edit your question post to remove the second once I've posted this.

All that said, it seems to me that the answer to your first question, about what you're doing wrong in your code, is that you create one file within your loop but always attach a separate file. This means the file you create is not being used.

I hope that's helpful and Welcome to Bytes.com.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,417
Please understand that our rules prohibit multiple questions in a single thread. However, it is perfectly acceptable to link to one thread while asking a new question in another. Bear in mind some may be put off by that but repeating the details is also acceptable. So, we won't answer your second question here and I will edit your question post to remove the second once I've posted this.

All that said, it seems to me that the answer to your first question, about what you're doing wrong in your code, is that you create one file within your loop but always attach a separate file. This means the file you create is not being used.

I hope that's helpful and Welcome to Bytes.com.
Dec 14 '17 #2

P: 2
That's it! Many thanks for your help.
Dec 15 '17 #3

NeoPa
Expert Mod 15k+
P: 31,417
I'm happy to help :-)
Dec 15 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.