+ Reply to Thread
Results 1 to 17 of 17

Set Range, Create PDF, send via Outlook...1 Macro?!

  1. #1
    Registered User
    Join Date
    11-21-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Set Range, Create PDF, send via Outlook...1 Macro?!

    Hi Guys,

    basically as stated above. On a xls sheet I want to specify the area which then is turned into a pdf and directly sent (via outlook). Anything in mind?

    Many thanks
    Last edited by Konsti1989; 12-04-2012 at 12:19 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Anything in mind?
    Yes, record a macro while you carry out the actions. You'll get something like:

    Please Login or Register  to view this content.

    Which can be tidied up to:

    Please Login or Register  to view this content.

    Then you just need the code to attach it to an Outlook email. You should be able to get that from anywhere. But, to save you some time, have a look at Ron de Bruin's web site.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-21-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Tbh, i know about ron de bruin's stuff...however have no clou how I would combine the above. Jst put it at the beginning (?) doesnt work, yet it soednt feel right neither...every help appreciated!

    Many thanks again.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    I use this subroutine to send an email. I pass it the email address (a string), the subject (a string), the body text (a string), and the names of two files (again, both strings).

    So, you'd run the first routine to save the selection as a pdf file and save it somewhere and then you'd call this routine to display/send the email.

    Please Login or Register  to view this content.

    My source and invoice file names are Excel files (.xslx), hence the Left( ... ) & ".pdf" structure ... you can just pass across the full path/file name that you used to save the .pdf file.


    Regards, TMS

  5. #5
    Registered User
    Join Date
    11-21-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Hi,

    thanks a lot, both codes work finse seperately i.e. using only ur first code, a pdf is created...however when I add the code for the email, the file is not attached. Any idea what I did wrong? ( I put the path in for ".pdf").

    Also for me it didnt work to put reference to a cell for the email adress i.e. instead of putting the email adress in the code, I would like to put it on a worksheet which I reference to in the actual code...

    Many thanks

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Please Login or Register  to view this content.
    OK, I know we're in the same country, but my eyesight isn't that good.


    How about you post a sample workbook with some typical data and the code in place?


    Regards, TMS

  7. #7
    Registered User
    Join Date
    11-21-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    I will prepare it.

    besides, is .To = ws.Range("A1").Value the correct command to use the email adress which is in A1 in that case?

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Something like that. It would depend on how your code is structured and what variables are defined.

    Regards, TMS

  9. #9
    Registered User
    Join Date
    11-21-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Book1.xls
    See attached the file....right now I only have the email component from your code which works fine BESIDES that it doenst attach anything (when I put a path in where it says ".pdf").

    How would u incorporate the pdf-creater? Also the range should be A3:Z23, however it would be best when the code takes always the range A3: Z where it says "end"...any ideas? (now with the sheet )

    Thanks a lot!
    Attached Files Attached Files

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Please Login or Register  to view this content.

    Regards, TMS

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    With the variable end position:

    Please Login or Register  to view this content.

    Regards, TMS

  12. #12
    Registered User
    Join Date
    11-21-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    many thanks!

    1st one works perfect....2nd unfortunately creates the file and sets up the email perfectly, however doesnt attach it to the mail. Any ideas why that is?

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Any ideas why that is?
    No. The only difference is that the second tries to determine the end position based on the word "End" in column Z.

    Apart from that, the code should be identical. Have you edited the file name in exactky the same way in them both?


    Regards, TMS

  14. #14
    Registered User
    Join Date
    11-21-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    u are right....works fine as well! what would be the change to do for saying that it stops before the end (i.e. that "end" doesnt appear on the pdf, but the range stops just before "end")?

    cheers

  15. #15
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Please Login or Register  to view this content.

    Regards, TMS



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  16. #16
    Registered User
    Join Date
    11-21-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    Done...

    Works perfectly...thanks a lot!

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,216

    Re: Set Range, Create PDF, send via Outlook...1 Macro?!

    You're welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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