Integrating Access and Outlook with VBA for regular reporting

Question

I'm looking to put together some VBA code in access to send out a bunch of emails at once. These emails each have multiple attachments and multiple recipients.

I have one table in Access where the Primary Key is a list of "Email Report Names" each column contains an email address that this Email Report goes to.

In another table I also have the "Email Report Names" and each column is the filepath on the harddrive of a file to be attached to that email.

I also have two Queries that call on these tables and filter by the Email Report Name which is filled into the criteria field by a custom function.

I've got the outlook coding working fine to send emails from Access. But I'm totally clueless how to make it loop through the different "Email Report Names" then pull the relevent info out of those queries to fill in the recipient lists and attachment lists.

Sorry to not include any example code, but I'm really out to sea on this and don't even know where to start.

If there are any solutions that are pure VBA with no SQL that would be ideal. I don't know any SQL yet so I'd have a hard time modifying the code as neccessary even if I do get it initially working.

Thanks,

SELECT FilePaths.MailItem, FilePaths.FilePath1, 
       FilePaths.FilePath2, FilePaths.FilePath2, 
       FilePaths.FilePath4, FilePaths.FilePath5, 
       FilePaths.FilePath6, FilePaths.FilePath7,
       FilePaths.FilePath8, FilePaths.FilePath9, 
       FilePaths.FilePath10, FilePaths.FilePath11, 
       FilePaths.FilePath12, FilePaths.FilePath13, 
       FilePaths.FilePath14, FilePaths.FilePath15, 
       FilePaths.FilePath16 
FROM FilePaths 
WHERE (((FilePaths.MailItem)=EmailItemSelect()));

At the least, post the sql of the two queries "that call on these tables" — Fionnuala
It is often a sign of a design fault when you have numbered field names (field1, field2 etc). — Fionnuala
There are two tables. The first column in both is MailItems which is the primary key in both. In one table each column after the first is FilePath1, FilePath2, etc. In the other table each column after the first is EmailAddress1, EmailAddress2, etc. I could reformat these any way that makes this easier. I set it up this way so that one variable could be put into the "EmailItemSelect()" function to have the two queries pull up the neccessary info. — Alex Poulton
That is quite confusing. Would it be correct to say you wish to send attachments FilePath1 to FilePath16 to EmailAddress1 to EmailAddress<unknownnumber>? — Fionnuala

Fionnuala Fionnuala · Accepted Answer · 2012-08-27T19:58:06

The best way to set up your tables is on these lines:

EmailAddresses

ID 
EmailAddress
OtherDetails

Filepaths

ID
MailItem
FilePath

EmailaddressFilepath

FilePathID
AddressID

I am not quite sue what mailitem is, so I left it in.

The table EmailaddressFilepath contains a match for which address is to receive which items.

EmailAddresses

ID     EmailAddress    OtherDetails
1      [email protected] Joe Bloggs

Filepaths

ID    MailItem    FilePath
1     Help        z:\docs\help.doc
2     More help   z:\docs\morehelp.doc

EmailaddressFilepath

FilePathID    AddressID
   1              1
   2              1

You will then need some code

Dim rs As Recordset
Dim db as Database

''Something like
sSQL="SELECT EmailAddress, Filepath " _
    & "FROM ( EmailaddressFilepath " _
    & "INNER JOIN EmailAddresses ON " _
    & "EmailaddressFilepath.AddressID = EmailAddresses.ID) " _
    & "INNER JOIN Filepaths ON EmailaddressFilepath.FilePathID = Filepaths.ID " _
    & "WHERE FilePathID=1"


Set rs = db.Openrecordset(sSQL)

'You now have a recordset with email addresses and attachments
'there would be other approaches, but this will do for now.

 Do While Not rs.EOF
     sEmail=rs!Email
     'set up outlook email

     Do While rs!Email=sEmail
     ''Attachment
         sAttach=rs!Filepath 
         rs.MoveNext
         If rs.Eof Then
            exit loop
         End if
     Loop
 Loop

Integrating Access and Outlook with VBA for regular reporting

1 Answers