CODEWITHSUNDEEP
vbams-accessoutlook
Alex Poulton
Alex Poulton

Reputation: 23

Integrating Access and Outlook with VBA for regular reporting

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()));

Upvotes: 1

Views: 798

Answers (1)

Fionnuala
Fionnuala

Reputation: 91306

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

Upvotes: 2

Related Questions