0 votes
in VBA by Beginner (36 points)
Hi guys

I have a task that requires me to email and attach unique documents to different people. The files I want to attach are all in a specific folder (so that solves the filepath)

I had a poke around and saw this (https://wellsr.com/vba/2018/excel/excel-vba-send-email-with-attachment/) but that solution has everyone getting the same thing. What we require is Person A getting attachment A and person B getting attachment B.

I am very sure there is a solution to this that does not involve making a draft in Outlook, copying it multiple times and then copy paste the email address and dragging the attachments (yes, I have been doing that this whole time!!!)
by Expert (916 points)
+2
May be creating a helper column and in that column you insert the full path for each attachment .. and of course in the same row there is information about the email you will send the email.
by Beginner (36 points)

Hi there; thanks for the idea for the helper column. I tried it and I think I'm nearly there.

With the following code:

myMail.Subject = "Files for Everyone"
Would it be possible to change it to a fixed cell ie G2 (fixed for every recipient)?
 
I tried to use
myMail.Subject = Range(G2).Value

but that seems to fail. Any ideas?

Thanks again!

 

by Expert (916 points)
+1
Put the reference to G2 between quotation marks Range("G2").Value
by Beginner (36 points)
Brilliant!!!! Got the To, CC, BCC and subject thing working. Now to get the part about attachements working.

Thanks again!
by Beginner (36 points)
edited by
+1
Thanks to all for helping! I think I saved my mouse and keyboard from too much clicking!
by Expert (916 points)
You're welcome. Glad I can offer little help.

1 Answer

0 votes
by Beginner (25 points)
thinking you have a list what email you're going to send for each. Then make a loop for all files should be attach, put it into other loop for each who will receive these. I mean the loop for file that attached must be next before the loop for person next. because you need send one by one. Hope can help you in this case.

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...