Automating adding attachments to email based on MS Access query

[Scoricco]

Hello,

I have a question that hopefully someone may be able to help me with. I’m trying to come up with a solution by using a VBA code or some other method. I’ve tried googling what I’m trying to do with a few posts that are similar to what I’m trying to do. https://bytes.com/topic/access/answers/943464-send-access-report-body-outlook-email

Currently I have a manual process that I’m trying to automate. We have a MS Access 2010 query that shows a filtered view from a master list of chemicals to give me a site specific list of chemicals that are in use. From that list, I manually copy and paste a single PDF file relevant to each in use chemical into an email and send that email to a couple of email address.

I’m trying to figure out a way to automate this process, as we have a number of different sites which use a varied list chemicals. I’d like to see if I can use some code based off content from a query to pull some PDF files into an email and then send it.

I do already have a lovely bit of VBA code that uses .AddAttachment to be able to pull external files into an email so that it can be sent. The difference is for what the existing code is used for there is a consistent set of files each and every time. Because for what I’m trying to design this list of chemicals is inconsistent I’m wondering how to approach it.

I can add a field into the database for the constant URL location of where the PDF is located which might be useful.

Here is my existing code that works where things are consistent I’ve tried to trim it where possible imagine multiple lines of 'msgXX.AddAttachment ("Z:\Z.pdf"),

Dim msgXX
Set msgXX = CreateObject("CDO.Message")
Set msgXX.Configuration = cdoConfig
msgXX.AddAttachment ("Y:\ZZZ.pdf")
msgXX.To = "[e-mail], [e-mail]"
msgXX.From = "[e-mail]"
msgXX.Subject = "ZZZ Site Chemical list"
msgXX.TextBody = "Hello." & vbCrLf & " " & vbCrLf & "Automated update of compliance update for this month."
msgXX.send
 

Kind Regards

[jforbes]

Here is an example of someone that did something very similar, How to attach several files to e-mail message (objMessage.AddAttachment)

It opens a saved Query and attaches all the files that are returned by the Query. You might want get your list a different way, but the basics of attaching multiple documents are pretty much the same.

[Scoricco]

Thanks a bunch. I'll check it out.