CaptainSmall
New member
- Local time
- Today, 14:42
- Joined
- Sep 22, 2017
- Messages
- 8
As a low ranking member of the Space Federation, I'm looking for an automated solution to export different selections of an Access table to a unique file for each selection. The difference with each selection is the value in one of the fields of the table. There needs to be as many files exported as there are different values in that field. Example of the table:
Field 1 name: SpaceShipName (with values like Zorg7000, InterstellerQueen, etc.)
Field 2 name: Cargo (with values like Medicine, SillyString, Water, etc.)
In other words, export a file with only the Zorg7000's various items of cargo, then a different file with only InterstellerQueen's various items of cargo, etc.
After that, I need an automated way to email each spaceship's freight officer their spaceship's file. The email address can be stored in a second table or in the first table, it doesn't matter. Example if a second table:
Field 1 name : SpaceshipName
Field 2 name: EmailAddress (with values like zorgy at example.com, nolan at test.org, etc.)
In other words, email Zorgy the file listing the Zorg7000's cargo, email Nolan the file listing InterstellerQueens' cargo, etc.
Did I mention that if I don't resolve this the Galaxian President is going to be angry with me?
I need to do this without installing any utilities other than those available in Microsoft Office. It needs to be automated because there are a lot of spaceships out there. I would expect this requires VBA, partly because this will require some kind of non-hyper loop - the spaceship names can change depending on wars, obviously - and partly because the SendObject macro in Access doesn't work on the setup I use in work, which this database is really for (the examples above might not be totally realistic).
I use this sort of code at the moment for sending an email with an attachment in Access via VBA, so hopefully the solution can use this method for emailing too:
I use this sort of code at the moment for simple exporting of tables:
As you can see, this is my first post and I'd love some help please! I am flying to Saturn now but will check back in on Wednesday. Thank you.
Field 1 name: SpaceShipName (with values like Zorg7000, InterstellerQueen, etc.)
Field 2 name: Cargo (with values like Medicine, SillyString, Water, etc.)
In other words, export a file with only the Zorg7000's various items of cargo, then a different file with only InterstellerQueen's various items of cargo, etc.
After that, I need an automated way to email each spaceship's freight officer their spaceship's file. The email address can be stored in a second table or in the first table, it doesn't matter. Example if a second table:
Field 1 name : SpaceshipName
Field 2 name: EmailAddress (with values like zorgy at example.com, nolan at test.org, etc.)
In other words, email Zorgy the file listing the Zorg7000's cargo, email Nolan the file listing InterstellerQueens' cargo, etc.
Did I mention that if I don't resolve this the Galaxian President is going to be angry with me?
I need to do this without installing any utilities other than those available in Microsoft Office. It needs to be automated because there are a lot of spaceships out there. I would expect this requires VBA, partly because this will require some kind of non-hyper loop - the spaceship names can change depending on wars, obviously - and partly because the SendObject macro in Access doesn't work on the setup I use in work, which this database is really for (the examples above might not be totally realistic).
I use this sort of code at the moment for sending an email with an attachment in Access via VBA, so hopefully the solution can use this method for emailing too:
Code:
Dim strPath As String
strPath = "FileNamePathHere\" & "FileNameHere"
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
'.SentOnBehalfOfName = ""
.To = "EmailAddressHere"
''.cc = ""
''.bcc = ""
.Subject = "SubjectHere"
.HTMLBody = "BodyHere"
.Attachments.Add (strPath)
.Display
End With
Code:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "tblNameHere", "FileNamePathHere\" & "FileNameHere", True
It is posts like these that get me through the rough times! Welcome aboard Capt, we need more members like you...
