macro for email automation

Hi Thomas Zucker-Scharff,

thank you very much for the detailed note. here is what i have built taking from various sources.

what i am looking for is if i have the customer name or number in column F ( filename) it have to pick to and cc from the master sheet. i have tried with text vlookup but its not working.

thank you again
send-mac.xlsm

I'm sorry, but it is against my policy to open unknown macro files.  I would be happy to look at your code if you past it in use the code tags.  On another note, I am not a macro maven and personally think the best way to go about much of this is to use Visual basic (VBA) instead.  I am not sure what you mean by the master sheet, a different worksheet? I got a lot of help from EE user Nick67.

Why don't you want to use the specialized Mail Merge software?

For me it didn't meet my needs.  I needed to attach 4 different as attachments that would mostly be different for each email.  So my script checks for the existence of certain files and if they exist attaches them to the email, if they don't exist it attaches a blank form to the email.  This was among the hardest part to code and the least likely to be supported by any off the shelf mail merge.

Actually, this is easy with Mail Merge with Attachments. You can have a combo of formula and macro in Excel that checks the file for existence and returns the name of the blank form file or the actual file name. This macro check if the file exists:

Function FileExists(path As String)
    FileExists = Dir(path) <> ""
End Function

Select allOpen in new window

And the formula whould be something like:

=IF(FileExists(A1),A1,"blank.pdf")

Select allOpen in new window

Now you can easily save the resulting Excel file into CSV and perform the MailMerge.

DISCLAIMER: I recommend the tool because I'm one of the developers, so feel free to ask any further questions.

I'm game.  This is the first utility I have seen that purports to be able to do anything near what I need.  The first question that does come up, is that I wrote the script so I wouldn't have to delineate the names of the files, it creates them on the fly.  So would your plugin handle it if I have a name of a person (last, first), designate a directory, and query to see if the file named "last, firstinitial.docx" exists?  So my excel file has each persons' email address, last name, firstname, from email, email body (in html).  As it iterates through the lines it checks 4 separate directories for 4 different files.  If they exist it attaches the file to an email sent to that person and personalized with "Dear [Firstname]".  If it doesn't exist it attaches a blank file for them to fill out.  Can this be sent on behalf of another person?  

The VBA script I ended up with does not save a file to a csv then use it to do a mailmerge but rather does the mailmerge using the script.

So would your plugin handle it if I have a name of a person (last, first), designate a directory, and query to see if the file named "last, firstinitial.docx" exists?

Yes, sure. It provides macros for that purpose like {TO#FIRST}, ... For example: "{TO#LAST}, {TO:1#FIRST}.docx".
And yes, you can specify any Outlook account and even any custom sender.

Great! Can I try it before I recommend it to my boss?  (Although he may say he doesn't see any reason to spend money since we already have a solution)

Of course, you can download ReliefJet Essentials and try it for free.

I may be a little dense today (haven't eaten), but  I don't see how to do more than one attachment.  I also don't see how to make it select a certain group of email addresses to run on.  Does it just take all my contacts?  Is there a way to send to a subset of say 160?

I want to do what my script does, although yours looks more elegant.   I want to send the same message, personalized, to a list of members of our center.  Each member will receive 4 attachments with the personalized email:
1. a filled out survey from 2 years ago or a blank (docx)
2. a filled out survey from 1 year ago or a blank (docx)
3. a blank survey to fill out (docx)
4. a table of publications and the shared facilities they used - already generated - or a blank (docx)

You can refer to YouTube Video Tutorial: it shows how to create the required data table. You can just copy-paste all required contacts or extract them any other way supported by Excel. I believe, the video answers all your questions.

Thanks!  It looks good I will check it out.  I have reservations about the following:

I want to be able to check if a file exists in a directory called responses and then skip that record if it does exist (if the person has responded to an initial email I don't want to send out a reminder).  This is rather important since if members who responded receive a reminder email, they generally get upset.  I previously would just delete their name from the list before running the script, but this became hard to keep up with.

I generate the names of the files on the fly.  This means I do NOT have to have a list of users and the names of the attachments they should receive.  Instead they will receive an attachment with the naming policy of <lastname>, <firstinitial>.docx in the appropriate folder.  So when I want to send an attachment for the survey 2 years ago, it looks in folder ../members/2014 survey/, one year ago - ../members/2015 survey/, etc.  The naming of the files is constant.

The basic logic of the script is as follows:
define who is the sender
define bcc/cc
Ask how many attachments
Depending on the number of attachments, ask what the directories are
declare naming policy
declare blank as blank.docx
check if someone has responded, if so skip to next
check for attachment existence in directory 1, if exists, then attach, else attach blank
check for attachment existence in directory 2, if exists, then attach, else attach blank
check for attachment existence in directory 3, if exists, then attach, else attach blank
check for attachment existence in directory 4, if exists, then attach, else attach blank
send email, bypassing outlook security

I want to be able to check if a file exists in a directory called responses and then skip that record if it does exist

This can be done with the same FileExists macro I mentioned above.

The general path to success is the following:
1. Create the required Excel table with all required fields in it (using formulas and macros if needed).
2. Save the ready to use table to CSV file.
3. Run the tool specifying the CSV and the message template.

Thank you Thomas

Alexei,

I will try this out.