Vba code to create pdf

Then, if the file was created, the macro shows a confirmation message with the file path and name. The previous macro creates a default name with a time stamp, based on the active sheet name. It prompts you to select a folder for the saved PDF file, and you can change the default name, if you prefer something different.

In the macro below, the default name is based on the values in cells A1, A2 and A3 on the active sheet. The PDF file is automatically saved in the current folder -- you are not prompted to choose a folder, and cannot change the default name. The PDF file is automatically saved in the current folder, with no prompts. However, if a file with that name already exists in the current folder , a message asks if you want to overwrite the file. Click Yes or No in the message box. In one of the Excel forums on the Microsoft website, someone posted the following solution to the problem.

You shall be finding first name, last name, title, contact person email , company name, company website, company address, phone number. Other jobs related to ms access email report vba access convert report excel vba , vba code create pdf file access report , access access report vba , access select report email Before deciding to create a PDF from your worksheet and emailing it, it should be noted that this code is specifically for Windows versions of Excel onwards and only when Outlook is your email client.

Create a new email in desired format, stationery and save it as Outlook template. Now, we can access the email properties with the CreateItem method. This will give access to … Topic: Create report from excel data using vba. I have created a VBA macro that runs through every line of the invoice data worksheet, filling the template with the required data and then saving the invoice template sheet as a PDF using the name field as the PDF name e.

This creates up to individual PDF invoices. I want to make an email that must be sent to a string of people that have to aprove the pdf file before it will be sent to a customer.

But there are many advanced things you can only do with VBA, like creating a spreadsheet report with all of your PC information. Why Send Email From Excel? Second problem: using 2 digit year like 19 is confusing. Is it , or , or ? Excel will fail on this one.

Path End If Next This loop will produce the address of the latest file in that folder. If there are other files that pdf files, or if the date format is wrong or missing in the file name, will fail, file naming consistency is required.

Hi, I am new to this VBA code, however I managed to create an excel user form from looking google comments. I have 2 questions: 1. I created a separate sheet to house my temporary data that is being populated for emailing the PDF submission, however when this sheet is hidden, my code does not work, how can I get this sheet hidden always but make the code work to create the PDF attachment and for the email to work.

How do I get the pdf file name to be saved as the users name which they would have stated in cell D5. How do I insert a message box into this code which prompts people to answer any blank boxes before it proceeds to save and email the file, when the commandbutton is clicked? I am trying to save an excel spreadsheet as a PDF, while naming the document based on cell contents of the excel document.

Then I want to attach that PDF to an email, and reference an email look up list to send that email to. This reference workbook would be dynamic and allow for us to change the customer email information and assigned internal person to CC. Hi Philip, I hope you can help me with this? I am trying to automate an excel worksheet supplier invoice to save as a PDF.

If you or any of your readers have any ideas, that would be super. Regards Pat. The test. You just need to test. Thank you for this very helpful VBA coding. If possible, which codes should be deleted? To change this destination edit the code and find the variable DestFolder, and change that value to whatever folder you want.

Silly me. I should know by now that this is THE place to find it. I will need to use this macro for at least 14 sheets at a time. I think I am reading that I can get this macro to work like this: 1. Create PDF for each sheet at the same time. Is this correct. And if so, how might I accomplish this? If Instr ExcludeList, Sheet. Me again. Do I use this new code along with the code already there but basically create a loop so it goes through the main code for each worksheet except the ones I want to exclude?

I think I get it now. Can you please give me some help there? Hi Bob, SendAllSheets just starts a loop that activates one sheet at a time and calls the original code for emails. Can you please direct me to where I can loop through all sheets in the workbook and send to separate recipients noted in cell H1 of each respective sheet?

Hi Ashley, Please take a look at this topic , last message has a code that can loop through all sheets to send that sheet by mail. This is great! Thank you! One question regarding the month file name. I am using this as a novice to send a PDF Invoice with the name of the company in a merged cell C9; however, the code always skips the first word in the name e.

Any help would be greatly appreciated. Format date as xx-xx-xxxx instead. Hi, thank you very much for the code. I was wondering if there is any way that when i save the PDF some parts from the excel will not be saved to it and not seen. Cheers, Catalin. Perhaps the path or file name are invalid? An alternative to adding the degug.

You can read more about Debugging VBA. Regards Faith. This code does what you want. Hi There. This code is amazing and exactly what I needed, thank you. Please can you help me with a problem that I am having with the sending. When I enter and email address to send the PDF to, the email gets sent back to me and not the email address that I typed in. I have no idea what I am doing wrong? Let us know if you managed to make it work Catalin. Thank you for the above, however, when I try to incorporate it I get an error.

So far for the code below, it creates the designated spreadsheets as separate PDFs. Add PdfFile. Hi Peter, In your code, you are using thisWb.

Same full path should be provided for attachments, not just PdfFile:. Add PdfFile Should be:. Add thisWb. Wonderful post. This was very helpful. I also want the macro to loop through all Sheets in the workbook to email it to the email on each sheet.

Please help. Close True End With. Make sure you change the extension from. See this topic for a code that will send all sheets, look for SendAllSheets procedure. If you need more help, let us know. I have a template in outlook that contains the body of the text my signature and details of what i am sending. Is there away in the code to open up this template and not the standard new email template? Hi Matthew, Use this:. HtmlBody This will append the existing signature to the text you want to write in the body section.

Make sure you display the message before sending, to allow signature loading. I am using the code below which I modified and it works very well. While it is working I need it to work better. I also would like to add a line to populate the body of the email with a greeting, a message and a thank you.

Any help is appreciated. Hi, On your last lines of code, siply add the body:. To write multiple lines, use a html line break: break lines in text You can use any html formats, to set the font size, bold or any other formats needed. Hi, Can the code send say, 20 sheet to 20 different people by executing the code once. If you could share this code i would be hugely beneficial with this code.

Hi Subhasis, When sending one sheet, you want to send the email to all emails from all those 20 sheets? Hi Thanks for the reponse. My requiremet is below: Each sheet will have seperate email address. There should be a loop to Create PDF for each sheet and create email with this attachment and pick the email address from the cell.

The it loops back to second sheet and does the same as above. This goes on till all the sheet is completed. Hi, Yes, you can add any valid file instead of that pdf some files are not accepted by outlook: file type is not accepted, or file size is too big , simply change the attachment link to your file. Close End With. It needs a specific structure designed, please upload to forum sample files so we can help you.

If the password is in a cell, there is no real place to hide, even if you set the sheet to very hidden with vba. If someone knows the location of the cell, it can be obtained from any open workbook, even with a simple formula referencing the cell. Want to start by saying that I absolutely love this code. Thank you so much for Posting. I am relatively new to VBA so if you are able to help I would greatly appreciate it.

I have gotten every thing to work for my application except for some line spacing in my body. Please see my code below. Range "F72". Hi, The code by default will print the active sheet. If you select more than one sheet, all selected sheets will be printed into a single PDF file. To select more than 1 sheet use: ThisWorkbook. Worksheets Array "Sheet1", "Sheet2". Select No other change is needed, the existing code for publishing to PDF will also work for this scenario. The document may be open, or an error may have been encountered when saving.

Hi Stephanie, Can you upload a sample file with your code so we can test it? Looks like the file name is wrong, but I will know for sure only after seeing the code in action. You can sign-up to our forum , create a new topic and upload your file. I just uploaded it — thank you! I love this VBA! Only when the fil already exist the user needs to have a message. Do you know how? Hi Pier, instead of this part of the code: With Application. The issue I have is that the macro coding is specific to my user name i.

Is there some way that the coding can pick up the user name of anyone that I may share the file with? This would also allow me to share updated versions of my file with others without having to have specific coding for each user on each update. Thank you for your assistance and sharing your knowledge with us. Hello this VBA is amazing it has helped out a lot, but now i would like to only save as. How can i achieve this.

Hi Blake, You can set your sheet print area, to be 1 page only, only print able area is sent to pdf. Hi, thanks of this code, its great! I want to be able to save the PDF file in a specific folder and have the file name automatically inputted with certain cell names. Any suggestions on how to modify the code? Hi Sen, See this comment for how to create the file name based on sheet cells.

Hi, this code is great! You are skilled. Here is what I am trying to do with this code. I want to print the files to pdf, but send an email, without an attachment to someone else. Basically giving them a status of the file. So we create the worksheet and save it currently. Now, a script to automatically email a pre-defined email. Any suggestions? Hi Mike, Look in code after the line:. Add Remove it, or type an apostrophe at the beginning of this line to disable it, the code will send emails without attachments.

Thank you so much for this code! Thank you for doing this! When I run the macro, it prompts me to pick a location to save to, then it successfully creates and names the file and saves it there. Then it opens up an e-mail with the correct subject line and recipient and body just like I wanted it to. But there is no attached file. When I go back to the Excel window I have this:.

Verify the path and file name are correct. The string PDFFile is the file that it just successfully saved a few microseconds ago… how can it not find the file it literally just created?

I check afterward and the file is definitely created. I got it working! And now it works! Sidenote: this solves the mystery of why the overwrite check was never working.

It was always overwriting the file even when it should have been prompting me about the duplicate. Hi Celeste, To send All sheets, use another simple procedure that will call each sheet. I wonder if I added one of those commands in the wrong place? Can you elaborate for me? Thank you!! I am hoping that I can add a feature to have the code ask which sheets to copy and email and or, at least just copy to specific sheets tabs.

Since I am a beginner, i assume there is a place in the above code that I can type in the worksheet names, is that true? InputBox "Type the name of the Sheet to be emailed! Worksheets SheetName. Activate If Err. All works awesome thanks. Hi I am trying to get this to work for me as I desperately need to email a nominated sheet of a workbook in excel. I would like to send it as a pdf and add the nominated cell that has the email address in it.

I have added that range to the code but cannot get the code to work. I have outlook open and press F5 but nothing happens. Try uploading a sample file to our forum create a new topic , so we can see why it does not work, if you still cannot make it work.

Any help you can give me would be great. Hi Wills, You have a reply in the forum, if you need more help, we can continue the conversation there. This code has been a lifesaver, so thank you very much! The problem I am having is sending the same document to 2 different emails. For some reason, it will only send the document to the first email but twice.

If you cannot manage to do it yourself, you can upload a sample file on our forum , we will help you fix it. Got it, thanks! I have a data table with customer ID, a list of the sheets the customer needs and their email addresses. Eventually I would like the macro to loop through the data table, save and combine the sheets each customer needs into one file, and send that specific file to their email address or printer.

Excel will not combine PDF files. If the formats are very different, it will not look right. I opted towards creating a macro that would print selected sheets based on a cell. All parts of my code work seperately but when I put it together the code stops at the loop.

Wondering if you might be able to help? Essentially I have a table with a list of sheets in column A, and yes or no to print in column B. Column B changes depending on whats in cell E2, and I have a list of numbers that will be pasted in cell E. So I need the code to go through each number in the list, paste it into column E, print the sheets according to the table, and then move onto the next cell. Hopefully that makes sense!

Cells j, 8. Select Selection. Select ActiveSheet. Cells i, 1. Cells i, 2. Select ActiveWindow. Hi Jacqueline, Can you please upload a sample file on our forum create a new topic , so we can see and test on your data structure?

Hi, I am a complete novice using vba, but have managed with this code to change most of the items needed. I would like it to do a couple of other things if possible!

I have 2 email accounts one for personal, one for business. I would like this macro to send from my business email by default if possible. Can the pdf be saved to a set folder without having to choose a location.

Your help is greatly appreciated. Hi Alan, The easiest way is to use this line:. Item 1 Change Item 1 to Item 2 depending on which account you want to send from. Count MsgBox OutApp. Hi Ed, What exactly are you trying to combine?

I see that you want to assign the pdf name from cell Q2. Text and the PDF will be named based on that cell text. What is the best option if I want the code to ignore these symbols when creating the PDF name? One option is to use a function to remove such chars. I see that you answered a similar question with new code further below. To evaluate all file names from a folder, you need to write specific code.

To get a comprehensive output, use something like:. I'm pretty mixed up on this. I am also running Excel I tried saving two sheets as a single PDF using:. It saved both sheets, but nothing on them. It wasn't until I used:. I tried manually saving these two pages using Selection in the Options dialog to save the two sheets I had selected, but got blank pages.

What gives? Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 8 years ago. Active 2 years, 1 month ago. Viewed k times.