+ Reply to Thread
Results 1 to 16 of 16

Converted from Excel to PDF. Attach to an Outlook E-Mail

  1. #1
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    129

    Converted from Excel to PDF. Attach to an Outlook E-Mail

    Hello,

    I currently have this code that takes an Excel sheet ----> converts to pdf -----> saves pdf to a specified location -----> pop up message confirming save with an "OK" button to confirm
    -----> clicking "OK" brings you back to Excel sheet

    On top of this I would like to extend this code to take the just converted pdf and attach it to an Outlook e-mail. Not required to populate the "To" or "Subject" or body of the e-mail
    (Outlook 2010)

    Would this be possible? Thank you very much


    Please Login or Register  to view this content.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    .
    Paste into Routine Module:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    129

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    Hi,

    Thank you for your reply. I added your code to the existing and received a Run Time 440 Automation Error. Not sure where I went wrong?


    Please Login or Register  to view this content.

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    .
    Which line in the code is being highlighted with the error ?

  5. #5
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    .
    The data that will be present in J6 and become part of the PDF file name .... is it always going to be the same term or (aside from the date) will the data in J6 change ?

    The reason for asking .... it is not easy to determine ahead of time how to code for changing data. This presents a problem deciding how to tell Excel to select the correct file
    to attach to the email.

    A good solution would be to have only one (1) PDF file in that specific folder. Then the email portion can be coded to search for ALL .pdf files ( *.pdf ). Since there will only be one
    pdf file in the folder, that will be the only file the macro will select and attach to the email.

    Your thoughts.

    (The above is separate from my previous question about which line was highlighted with the error.)

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

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    Like this.

    Please Login or Register  to view this content.
    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.

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    .

    Hey bakerman2 ... good to see you again !


    Or, if you don't want to continually move or delete the PDF file from your folder when the next one is ready to be created ... you could use the FileDialog to select which
    file /s in the folder you want to attach to the email with this macro :

    Please Login or Register  to view this content.
    See the attached example workbook.

    Also, we could change the initial path so the FileDialog opens to your folder on the G drive by editing this line:

    fDialog.InitialFileName = "C:\"

    to this : fDialog.InitialFileName = "G:\Finance\Corporate Accounting\SHIRLEY\A. Financial Planning Fee Payment Processing\"

    Also, using the FileDialog in this manner allows you to attach several different files to your email.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    129

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    Hello Logit,

    Thank you very much. The code works and I took your suggestion to change the path of pick up. The dialogue box opens repeatedly though after I select the correct pdf. Possible to have the box close once the item is selected? Also I had to go into "View Code" from the Excel sheet and run the sub in order for the Outlook program to run.
    Is there any way to have both codes run with once click of my command button? I could create a secondary command button and put your code in there but thought you might have a better suggestion? Thank you soooo much I truly appreciate your help. This is what I have...

    Please Login or Register  to view this content.

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

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    Did you even try my code ??

  10. #10
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    129

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    Hi bakerman2,
    I did thank you very much. The pdf did save to my specified location but then the message "could not create pdf file" appeared.
    I knew the Outlook part would be messy I appreciate all the help

  11. #11
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    .
    leebird

    I cant replicate the issues you are experiencing. The macros run as expected on my end. Sorry I can't advise you what may be occurring on your side.

    If you want the email to run auto after creating the PDF, add a 'call' to the email macro by inserting " Send_Email " in the PDF macro as indicated below :


    Please Login or Register  to view this content.

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

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    My bad.

    Change this line

    Please Login or Register  to view this content.
    To this

    Please Login or Register  to view this content.

  13. #13
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    .
    If you like, you can post your version of the workbook and I'll review it here.

    Remove any confidential info before posting.

  14. #14
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    129

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    That works! Thank you so much. This is perfect!

  15. #15
    Forum Contributor
    Join Date
    08-02-2015
    Location
    Canada
    MS-Off Ver
    Office 2010
    Posts
    129

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    Thank you both very very much. I really appreciate your time and code expertise

  16. #16
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Converted from Excel to PDF. Attach to an Outlook E-Mail

    You are welcome

+ 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. Replies: 0
    Last Post: 05-19-2017, 12:05 PM
  2. General: attach file in Outlook 2010 mass mail merge
    By Karnik in forum Excel General
    Replies: 3
    Last Post: 04-15-2016, 08:04 PM
  3. [SOLVED] screenshot text file and embed in outlook mail through excel with mail info
    By bmbalamurali in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 01-11-2015, 08:21 PM
  4. [SOLVED] How to prompt user to select file to attach to an E-Mail (Excel to Outlook)
    By TheLittlePrince in forum Outlook Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2013, 05:33 AM
  5. [SOLVED] attach file from folder on c:\ to outlook mail and send
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-24-2013, 11:33 AM
  6. Outlook - Save Worksheet as PDF and attach to outlook mail
    By kaurka in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2012, 10:19 AM
  7. code to attach the draft mail in new compose mail as attachment in outlook 2010
    By priya1987 in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2012, 08:38 AM

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