+ Reply to Thread
Results 1 to 20 of 20

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

  1. #1
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

    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.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

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

    Hi there,

    I may have totally misunderstood your requirements, but see if the following code does what you need:

    Please Login or Register  to view this content.
    The highlighted values may be altered to suit your own requirements.


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

    Regards,

    Greg M



    P. S. When I tried to include the umlaut on the "o" in "leverantorsgranskning", I could not upload my reply - that's why I replaced it with "oe"!
    Last edited by Greg M; 01-27-2017 at 08:49 AM. Reason: P. S. added

  3. #3
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    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?

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

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

    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

  5. #5
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

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

  6. #6
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

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

    Edit: Sorry I received some error messages and might clicked one time too often.
    Last edited by Fraggoso; 01-27-2017 at 10:56 AM.

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

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

    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

  8. #8
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    Holy crab, this works! Awesome Greg!
    The only thing is that the pdf looks weird (it's attached).

    If there also the possibility to add in logos etc... before it get's to pdf so it looks authentically?
    Oh yeah the Messagebody is empty. It was derived from J2. Any possibility to bring that back?

    I'm really thankful, Greg. =)

    Edit:
    Okay, if I expand M the pdf also expands and looks nice/right.
    Last edited by Fraggoso; 01-27-2017 at 12:07 PM.

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

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

    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

  10. #10
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    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. ^^;

  11. #11
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

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

    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
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    Thank you Greg.
    Even if I use your excel table and put information in it and run I don't get any email message as well as subjects inserted into the mail... :/

    What's also weird: Under my sent tab I see that the mail was also sent to another mail address all tough I don't know if it's a valid one. What's weird is, that this mail address wasn't entered under A.
    That didn't happened with my old code from the initial post.
    Last edited by Fraggoso; 01-30-2017 at 04:52 AM.

  13. #13
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    Hey Greg it's working! :D
    I don't know what I did on my end but I used your workbook as a basis and now it works!

    Only three minor things I still need:

    1. Is there a possibility to only have 1 body as well as 1 subject cell only?
    I don't want to copy cells over cells because the documents we need are the same to everyone as well as the subject.

    2. Is it possible to add graphics like our company logo to the attachment before it gets saved us a pdf?

    3. For convenience and better understanding it would be awesome if A1 - M1 could be disabled so we can add in headlines. Is that possible?

    Thanks again man you helped me (and us ^^) A LOT! =)

    Edit:
    I just saw that I don't need to enter the Mailbody in HTML anymore. I had to write it down in HTMl before and I could replace certain things like suppliername.
    I tried it with sSupplierName but that didn't work. I was also able to include graphics in the HTML Body and it was displayed correctly.
    And I can't get formatting working like bold in the body. I can see it in the excel table but when I sent the mail the formatting get's overridden.
    Last edited by Fraggoso; 01-31-2017 at 06:37 AM.

  14. #14
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

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

    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
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    Everything is working Greg, thanks for that and all your help here!
    How can I switch suppliernames for example in the mailbody cell just in case we need that? I tried with sSupplierName but it didn't worked for me.
    Also my team just reminded me, that we sometime sent the mails from our shared mailbox. Is it possible to specify a "sent in behalf of" cell under Common Details?

    Also thanks for the info regarding to display the mails rather than sending them out.

    Regards

    Edit:
    Is there a way to bring back HTML formatting for the body as well as the pdf? That would solve all graphics problems as we had that in the initial code.
    I could just add HTML formatting and everything was displayed accordingly.
    Last edited by Fraggoso; 02-01-2017 at 08:07 AM.

  16. #16
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    Hey Greg, I hope you have more time now.

    Could you look into the matter?

    Thanks in Regards

  17. #17
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    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.

  18. #18
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

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

    Maybe best to post the workbook you're currently working with holding all code that's used so far (and is working I assume)
    Then make a list of the requirements you have left to complete the file.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  19. #19
    Registered User
    Join Date
    01-27-2017
    Location
    Germany
    MS-Off Ver
    2013
    Posts
    12

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

    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.
    Attached Files Attached Files

  20. #20
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

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

    Don't have time to work it out in your bigger file but try this for starters.
    Change filepath and picturename in code.
    Had to put code in textfile because this site doesn't like Html-code.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need help writing Macro for shading rows multiple colors
    By Vortex69 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-30-2014, 12:25 PM
  2. Help with Macro to email attachment to multiple recipients
    By markharris2004 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-01-2014, 08:48 AM
  3. [SOLVED] Excel Macro to import contacts and place them on a particular sheet
    By rad2085 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-03-2014, 01:45 AM
  4. Need help in writing a macro for drawing multiple names
    By Emba in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-24-2013, 04:47 PM
  5. Macro to send email with multiple attachment
    By excelcheck123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2012, 05:00 PM
  6. Not able to select multiple contacts to email from a Shared Contacts
    By champs in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 03-10-2009, 02:08 AM
  7. writing macro for multiple cells
    By jcarstens in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-28-2007, 02:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1