Mail Merge to Email With Attachment

[KevNik]

Hi Folks, I've been trying to find VBA code to do the following task.
I have 130 unique files that I need to email to 130 unique email recipients. I do not want to individually create 130 emails (one at a time) and attach each unique file to each email. I have a document written that will go to all 130 email recipients with the file that is unique to them.

I have tried the Doug Robbins code on the mvps site but I guess I'm not tying the pieces together. Has anyone solved this challenge with VBA? My apologies if this is a repeat, I've searched this forum for quite some time trying to find an answer and only found a thread that had the same file going to a bunch of emil recipients. :banghead:

[KevNik]

I should add...I used Mr Excel's macros to sift through a huge file by salesperson, create a new worksheet named that salesperson, write the records for each onto the new worksheet, save it, close it, and move on to the next sales person. This created a boatload of files (>500). I then did the same macro using the sales manager and narrowed the file count to 130+.

I then went into the directory and had a macro write out every file in the directory by 1) FilePath\FileName, 2) FilePath, and 3) Filename (3 columns). I have added the email address, first name, last name to the file in addtional columns.

I want to look into each email address and using the boilerplate message text for the body of the email, look at the file name, match it to the unique email address, and then attach the unique file to an email message that is sent to the email address with the unique file for that one sales person.

[Frosty]

I don't have a lot of specific experience in this. As you say, cobbling the code together is not terribly difficult. I think the bigger deal is that you begin to look like a spammer (i.e., high-volume emails with just enough differences to look tailored to the recipient).

There are not terribly expensive products which do this (I seem to see Constant Contact as one of them, and I know there are others).

But you may spend hours upon hours researching the right way to do this, specifically for you... only to find out that whatever environment you're in has blocked this (I recently had a client request this, I spent a few hours boning up on the vagaries--after telling them I thought it was a bad idea--only to find that it wasn't going to fly with their corporate exchange server).

There are a number of little tricks involved (html emails vs. rich text format emails, whether you have graphics involved, etc), but the bigger picture is: this is what spammers do. At the end of the day, automating this process brings you closer to a spammer, and has a decent chance (greater or lesser) of getting you or your corporate email domain blacklisted.

10 years ago, not as big a deal. These days, if this is a one-off.... I'd probably compose the 130 emails (or automate enough to get you into the draft folder, and then manually do the rest to send them in chunks of 10 at a time, and then wait a couple minutes). If it's not a one-off, you're probably better served long-term not to investigate this process, but rather pay for a service (I've used none of these services, nor would I recommend a specific one if I had-- just recommending that direction in terms of solution).

Hope this helps. If not-- disregard :)

[KevNik]

Frosty - thanks for the guidance! I tend to believe you're probably correct on the spam even though this is an internal company distribution. We have a group that normally does this but, for whatever reason in this instance, they've chosen to sit on the sideline. I have found a couple of tools that will 'schedule' chunks of emails over a period of time and that's probably the route I'll take. Thanks again!

[the Core]

Dear KevNik and Frosty,
for some ice skating organisation I tried, just like KevNik, to send over 100 emails with unique data in both the attachment and message. It's absolutely not spam as everyone replies and nobody reports it.

Too bad Office 2010 doesn't offer the ability. The first VBA script I ever saw was Doug Robbins. I copied, read, understood and changed it. It works, but not the way I want as anybody should be able to use it. After many more searches I came here on this topic.

@KevNik: would you still like to know how Doug Robbins script works? It requires few steps and is pretty easy, but devious.

We should have a conversation together. I don't know what you want with the macro, but what I'm looking for (now) is:
1) the script should be ran from within Word
2) personal data should be read from Excel
3) the attachment should be updated (without being saved)
4) the message itself should be updated (without being saved)
I'm going to fix this and just registered at this forum to find some help. Maybe we could work together?

Regards,
Cor

[Frosty]

@kevnik depending on the size of the organization, internal may or may not be a problem. In my case, it was a very big company, and they had multiple exchange servers on both the east and west coasts. However, it may very well be fine for your purposes to code the whole thing.

@Cor you seem pretty sure of yourself when you say it's "absolutely not spam because everyone replies and no one reports it." I can only suggest to you that you may not have as complete an understanding as you think of all the pieces at work to make that statement. However, it's up to you whether or not to take advice, obviously.

Personally, I have too many mis-givings about this process to give even the code I put together. But a google search on VBA and Outlook will give enough samples to put it together (it did for me).

As a note: Microsoft does provide almost this functionality... You can do mail merges with email output... But (if I recall correctly), you can't attach things and once you click send it's gone. It's a pretty locked down function.

Best of luck to you both.

[KevNik]

Dear KevNik and Frosty,
for some ice skating organisation I tried, just like KevNik, to send over 100 emails with unique data in both the attachment and message. It's absolutely not spam as everyone replies and nobody reports it.

Too bad Office 2010 doesn't offer the ability.
[I use Office 2007, Windows 7]
The first VBA script I ever saw was Doug Robbins. I copied, read, understood and changed it. It works, but not the way I want as anybody should be able to use it. After many more searches I came here on this topic.

@KevNik: would you still like to know how Doug Robbins script works? [Yes] It requires few steps and is pretty easy, but devious.

We should have a conversation together. I don't know what you want with the macro
[The message was the same for all the unique recipients but the file attached is unique to each],
but what I'm looking for (now) is:
1) the script should be ran from within Word
[I dropped the code into Word VBA]
2) personal data should be read from Excel
[I have the file set up, first name, last name, email address, etc.]
3) the attachment should be updated (without being saved)
[What does "without being saved" mean?]
4) the message itself should be updated (without being saved)
[Same question as in #3]
I'm going to fix this and just registered at this forum to find some help. Maybe we could work together?
[Definitely looking to work through this. I had a deadline of last Friday so spent all of Thursday creating individual emails offline in Outlook, attaching the files and going online, and sending them 10-15 emails at a time. It was a huge pain in the backside - automation rules!] :bug:

Regards,
Cor

[KevNik]

@the Cor, After last Thursday's email creation nightmare I have no desire to manually create 100's of emails each time I need to send a group of unique files. It turns out that this won't be a "one and done" exercise so I am anxious to solve it. I can see that I will need to do this many times in the future. I look forward to working with you to solve this challenge! -KevNik

[KevNik]

Well, I've been tackling this project and copied the code into the Word VBA Editor. Pressing F8 takes me through the first 5 lines of code and then stops at line 6

Sub emailmergewithattachments()
Dim Source As Document, Maillist As Document, TempDoc As Document
Dim Datarange As Range
Dim i As Long, j As Long
Dim bStarted As Boolean
Dim OutlookApp As Outlook.Application
Dim Item As Outlook.MailItem
Dim mysubject As String, message As String, title As String
Set Source = ActiveDocument

the variable declaration then gives a "Compile error. User-defined type not defined."

I'm at a loss 5 lines into the code. :dunno What am I missing?

[Frosty]

You need to add a programmatic reference to the Outlook Object model (early-binding) or use GetObject/CreateObject (late-binding).

I'm going to attach my sample project, although it has some specifics in it as it was the test I set up for the corporate client/friend.

Use at your own risk.

It was originally a .dot which I saved as a .docm... so the toolbar stuff won't work the same, but I've left the code. You can save back as a .dot to see the full functionality.

[KevNik]

You need to add a programmatic reference to the Outlook Object model (early-binding) or use GetObject/CreateObject (late-binding).

I'm going to attach my sample project, although it has some specifics in it as it was the test I set up for the corporate client/friend.

Use at your own risk.

It was originally a .dot which I saved as a .docm... so the toolbar stuff won't work the same, but I've left the code. You can save back as a .dot to see the full functionality.

@Frosty, Thanks a bunch! I'm going on vacation starting tonight through next week (not really vacay I'm moving my daughter's apartment back from up north). I will play with this over the next week. Thanks again for the file I'll give it a try.

[toxigal]

You need to add a programmatic reference to the Outlook Object model (early-binding) or use GetObject/CreateObject (late-binding).

I'm going to attach my sample project, although it has some specifics in it as it was the test I set up for the corporate client/friend.

Use at your own risk.

It was originally a .dot which I saved as a .docm... so the toolbar stuff won't work the same, but I've left the code. You can save back as a .dot to see the full functionality.

Thanks so much! finding this made my life a lot easier.

[gmayor]

You could use http://www.gmayor.com/ManyToOne.htm in One to One mode which will create merged messages with merged or common attachments. The only proviso is that the data must be an Excel worksheet.

[karuneshwara]

You need to add a programmatic reference to the Outlook Object model (early-binding) or use GetObject/CreateObject (late-binding).

I'm going to attach my sample project, although it has some specifics in it as it was the test I set up for the corporate client/friend.

Use at your own risk.

It was originally a .dot which I saved as a .docm... so the toolbar stuff won't work the same, but I've left the code. You can save back as a .dot to see the full functionality.