Send Multiple csv attachments by email

[shaunacol]

I have been reading up on this all day but no closer to getting anything that works. I know I can not use sendobject to attach more than one attachment to an email, but I need to attach 6....I expect there is some code that would loop through but my coding ability is very limited. All the examples I find on line are really complicated and I cant understand them. I just need something simple that sends these 6 attachments to a set email address. Can anyone help me please?

[June7]

Where will the file names come from? Will they be different for each transmittal? If the names are always the same, maybe just have 6 add lines, like:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
    .BodyFormat = olFormatRichText
    .To = "email address"
    ''.cc = ""
    ''.bcc = ""
    .Subject = "text here"
    .HTMLBody = "text here"
    .Attachments.add ("path\filename1")
    .Attachments.add ("path\filename2")
    .Attachments.add ("path\filename3")
    .Attachments.add ("path\filename4")
    .Attachments.add ("path\filename5")
    .Attachments.add ("path\filename6")
    ''.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
    .send
End With

[shaunacol]

That looks really simple and I understand it thank you! My attachments are coming from 6 different queries so I somehow have to say 'send Qry_Session as a CSV and im not quite sure how to do that. Thanks again.

[June7]

You first have to export the queries to CSV or Excel file then attach each file.

[shaunacol]

OK thanks, I have just been looking for some code to do this and am using the below code but nothing happens

DoCmd.TransferSpreadsheet acExport, 3, _
"Qry_ExportSession", "C:\Documents", True

[June7]

Need file name. This worked for me:

DoCmd.TransferSpreadsheet acExport, , "AirportsQuery", "C:\Temp\Test.xlsx"

[shaunacol]

Thank you so much. I just managed to save it to my dropbox account which then means I don't have to send an email at all, I can just get the master database to collect it from dropbox (I hope!) Thanks again

[shaunacol]

A strange thing has happened, if I try to save multiple different queries as excel files using the code above it only recognises the first one and saves it successfully. Then it just seems to ignore the other 5. Am I supposed to reset a value or something? that's very weird.

DoCmd.TransferSpreadsheet acExport, , "Qry_ExportCashUp", "C:\Users\Dropbox\TableCashUp.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Qry_ExportClient", "C:\UsersDropbox\TableClient.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Qry_ExportOrder", "C:\Users\Dropbox\TableOrder.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Qry_ExportOrderline", "C:\Users\Dropbox\TableOrderline.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Qry_ExportPayment", "C:\Users\Dropbox\TablePayment.xlsx"
DoCmd.TransferSpreadsheet acExport, , "Qry_ExportSession", "C:\Users\Dropbox\TableSession.xlsx"

[June7]

Maybe code is executing too fast. Review http://www.fmsinc.com/MicrosoftAcces...idDoEvents.asp

However, a test with my db did not have that issue.

[shaunacol]

Its very strange indeed. I read the article and inserted a "WaitSeconds (5)"command but it doesn't change anything. It works fine with the first one and then no others. Not even if I only have 2 in there. Ive just written a macro and that seems to be working a lot better. So odd.

[shaunacol]

Following on from my export issues above, my macro does work but now im struggling as I want the 'output field' of the macro to be taken from my form (or table or query would do). (I am using ExportWithFormatting) and exporting 6 queries as above. All 6 of my files export beautifully. However, as above we have different sites, so I would need to have the files named different things to distinguish between them (and also avoid overwriting). Alterntively, having them saved to their on folder would work too. I am reluctant to send out 10 different copies of the database and am hoping that instead of the current file output (that works fine) which is C:\Users\Dropbox\Session.xls, I can perhaps rename the output file according to its location. Something like C:\Users\Dropbox\[Location] & Session.xls

I have tried a few variations of this in my macro including referring to the full form name and text box but it doesn't seem to work. I cant go back to using VBA because, as above, it just doesn't work for me for some reason. Can anyone help please? Thank you

[shaunacol]

Ive been reading that this is not possible in a macro, is this true?

[June7]

Possibly. I don't use macros, only VBA. Your posted code and my suggestion are VBA code. How is macro involved?

[shaunacol]

im afraid the VBA code doesn't work for me. It was executing the first docmd but not the other 5 (so only exporting one doc) and now it does nothing at all. So I had to create a macro which works perfectly and exports all 6, but now have this new issue which I think I could have solved much easier if the VBA worked for me. Ive even tried creating a new database and it doesn't work in there either (the VBA code that is) I have no idea why. Shalll I post something in the macros section since this is a new issue?

[June7]

I just attempted to build a dynamic file name in macro argument and you are right, it is not possible. You can try starting another post but I don't think that will be fruitful. Really need to find out why the VBA fails.

If you want to provide db for testing, follow instructions at bottom of my post.