Results 1 to 12 of 12

Thread: I need help - Macro to print page to pdf and send to a person on a list

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    6

    Cool I need help - Macro to print page to pdf and send to a person on a list

    Hi peeps

    I have a 70+ paged worksheet that I would manually print to pdf attach to an email and send out.

    Is there a macro that can send a specific page of a worksheet to a specified email with specific subject line and email body based on a table?

    Page Email Address CC Subject Email Body
    1 Test1@Test.co.nz Test1@Test.co.nz Invoice attached Hi John, please see test
    2 Test1@Test.co.nz Test1@Test.co.nz Invoice attached Hi John, please see test
    3 Test1@Test.co.nz Test1@Test.co.nz Invoice attached Hi John, please see test
    4 Test1@Test.co.nz Test1@Test.co.nz Invoice attached Hi John, please see test
    5 Test1@Test.co.nz Test1@Test.co.nz Invoice attached Hi John, please see test


    I have been able to do a bit of code to convert an excel sheet to a pdf and send it via gmail but I dont know what the next steps are

    Any feedback would be greatly appreciated.

    Cheers

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: I need help - Macro to print page to pdf and send to a person on a list

    Welcome to vbforum

    >I have been able to do a bit of code to convert an excel sheet to a pdf and send it via gmail but I dont know what the next steps are

    Why not use Outlook? It is much easier to automate Outlook.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,383

    Re: I need help - Macro to print page to pdf and send to a person on a list

    you can send via gmail using cdo.message, then you do not need outlook installed

    there are many examples in this and the vb6 forums
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    6

    Re: I need help - Macro to print page to pdf and send to a person on a list

    Hi Siddarth

    I think I have been fixed on gmail because I use it at work. I actually could use Outlook. Can I use the free outlook online or do I have to buy the desktop version?

  5. #5
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: I need help - Macro to print page to pdf and send to a person on a list

    Well you need the desktop version (MS Outlook - Paid Version). Else do what Pete Suggested. Use CDO
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  6. #6

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    6

    Re: I need help - Macro to print page to pdf and send to a person on a list

    Hi Sidarth

    I have bought and installed Outlook. I read a post and tried using the code that was already written. Its pretty good really and would now just need help on how to personalise the Subject line, be able to send it to more then one person and the email body.

    At the bottom of the thread below is a downloadable spreadsheet.

    http://www.excelguru.ca/forums/showt...Range-as-a-pdf

    Is this code good or would you suggest something better. Ive tested it and it does what I want it to do but just want to be able to personalise it a bit more using the table

    Cheers

  7. #7
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: I need help - Macro to print page to pdf and send to a person on a list

    The code by Ken is good. Go ahead with that
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  8. #8

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    6

    Re: I need help - Macro to print page to pdf and send to a person on a list

    How do I alter his code so that I can personalise the subject line, cc in another person and personalise the body of the email?

  9. #9
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,383

    Re: I need help - Macro to print page to pdf and send to a person on a list

    what is the code you are using now for the bit you want to personalize?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  10. #10

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    6

    Re: I need help - Macro to print page to pdf and send to a person on a list

    Hi,

    I am currently using the code that was posted on teh website below.

    http://www.excelguru.ca/forums/showt...Range-as-a-pdf

    It does work in its current format but want to adjust it so that I can personalise the subject line and the body of the email using the table.

    Cheers

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,383

    Re: I need help - Macro to print page to pdf and send to a person on a list

    i am sure it is very simple, but i have no idea what information you want to put in the subject line, or where it is to obtained from

    just change the subject and body lines to get the persons name from the worksheet, something like
    Code:
    msg.subject = cells(rw, 4)
    msg.body = cells(rw, 5) & " please pay immediately"
    change to the appropriate object variable for the email message, the body may also be msg.htmlbody and rw is the same variable as you use to get the row for the email address
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    New Member
    Join Date
    May 2017
    Posts
    6

    Re: I need help - Macro to print page to pdf and send to a person on a list

    Thanks Westcon

    I wanted to use a table to personalise each email. Ill try what you have put and get back to you.

    Cheers

Posting Permissions

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



Click Here to Expand Forum to Full Width