Macro for writing a doc and sent it as attachment to multiple contacts within excel

I've the following problem and thought there should be a way to automate it to a certain degree.
We must collect a lot of documents once per year from our suppliers we work together.

The program team did this manually til now which took A LOT OF TIME and isn't really efficient.
I've made a macro in excel that pulls a text from a cell which is the message text for the mail. It replaces some keywords with information in other cells like supplier name, resource name etc...

Please Login or Register  to view this content.

The only thing that's missing (which would be the icing on the cake):
Is it possible before sending the mails to recipients defined in a cell (have a look at the screen) that excel pulls information from another cell and writes down the information from there, also replacing keywords and attach that to the specific supplier?

\1

E.g. I just want to sent a mail to the mail address in A which also includes a word attachment which pulls information from email text says something like "dear valued supplier, please confirm that the resources: replace_resource_name) are still working under a contract with you. Print, sign and sent the document back to us via mail as a proof of employment." replace_resource_name should be the names written in D.
So I need either once code before mine from above or another solution which does the trick.

Every supplier need their own attachment with their resource names in it.

Is that even possible or am I thinking "too easy"?

I'm using Office 2013. If you need more information please let me know.

Thanks beforehands.

Greg, yeah maybe I'm not good at describing what I need. ^^

Your code seems to work as you intended. It sends me an email with the text.
I just need that it's not written in the mail directly but attached as a doc.
So I need the macro to pull the text which is written in the mail by cell J2 and the attachment needs to be written in the form you already achieved.

Is that better?

Hi again,

Thanks for the prompt feedback.

See if the following version does what you need:

Please Login or Register  to view this content.

The values of the new constants can be altered to suit your own requirements.

The sATTACHMENT_TEXT_CELL is a single cell which is used to temporarily store the value of the text string which is inserted into the attachment (.pdf) file. The contents of the cell are cleared after use, so the cell can be located in any convenient position, or on another worksheet if appropriate.

Same problem as before with the umlaut!


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

I receive a "compile error: Variable not defined" on sATTACHMENT_CELL

Hi again,

Sorry! A "clever" change that wasn't made throughout the code!

Try:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Hi again,

Ok - thanks for that - I thought that you wanted the Body Text to be inserted in the attachment instead of in the email itself - sorry!

I think the following version should do what you want:

Please Login or Register  to view this content.

The sCOLUMN_BODY Constant has been included so that the Body Text can now be included in the email.


Hope this helps - as always, please keep me informed.

Regards,

Greg M

Thanks again!

I still get an empty mail with the attachment though.
I've even edited "J" to be "J2" but still empty...

Can we also throw into the mix that the subject should also be derived from cell K2?
Oh man I'm asking too much, I know. ^^;

Hi again,

The body of the email was correctly inserted when running the code on this pc.

The Subject text is now taken from Column(K) and inserted into the email.

I'm attaching my version of the workbook which appears to work correctly for me - try it on your pc and see what happens.

Please keep me informed!

Regards,

Greg M

Hi again,

See if the attached workbook does what you need.

The common subject and common body texts are entered on the new "Common Details" worksheet. I've also moved the "AttachmentText" cell from the original worksheet to the common worksheet.


Row 1 on the original worksheet can now be used as a header row with appropriate descriptions


If you want to add more supplier names to the list just change the value in the following statement to suit:

Please Login or Register  to view this content.

I would suggest that you display the emails rather than send them automatically - this gives you the opportunity to check them for mistakes before they are sent - to do this, just replace the .Send command with the .Display command

Please Login or Register  to view this content.

I'm a bit busy right now, so I haven't been able to add a logo, but it shouldn't be a major problem to insert one into the AttachmentText cell - I'll see what I can do later.


Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

Hey Greg, I hope you have more time now.

Could you look into the matter?

Thanks in Regards

Is someone else able to help out?
We just need to insert some pictures into the pdf as well as maybe in the mail itself.

It's still the untouched workbook Gary provided me with.
I attached it once more, just in case.

What I need is simple (at least it sounds simple ):

- The possibility to insert pictures via HTML code before the pdf's gets generated
- The possibility to insert pictures via HTML code before the mail is generated <- Just in case we have to send the mail with another logo (our organisation has 3 different logos)

I hope that helps.