Thank you very much Dr. I met your videos only in last week and they help me to improve my knowledge in VBA =, Thank you so much again. Piyal Fernando (Sri Lanka)
Congrats! Dinesh on reaching 100 K subscribers! You have helped me put food on my table to feed my family for many years with your teaching. I am thankful
Dinesh Sir you are simply great .....This video was great help to me ...Thank you very much for your noble job of publishing so informative stuff through your videos...I am your great Fan Sir !!!!
this is very nice. What if the name of the pdf changes every time & recipient. The solution could be to just bring up the outlook email and no not write the send code then I will be able to change the recipient's name every time. that part is easy but what about the pdf as my macro allows me to save as pdf and brings up the save as for me to enter a specific location to save my pdf forms. This can be a task but I thank you for the inspiration and I will try this
That's a great idea! These links will guide: www.exceltrainingvideos.com/automate-invoice-report-generation-using-excel-vba/ www.exceltrainingvideos.com/how-to-transfer-data-to-an-invoice-template/ www.exceltrainingvideos.com/automatically-update-inventory-while-creating-invoice/
i am having an excel file with list of email IDs & file path against each mail ID. please help me with macro which will help me to send individual email with said file as attachment
Thank you for this Dinesh. My question is how to express formula to send the payslip to the respective email add...considering i have an email address column in my active sheet.Thank you so much.
awesome video. how to add on in this vba with adding password on the exported pdf? also is there a way to automate different password to different client or recipient name?
Thanks a lot for your courses, Takyar, If I want to save as pdf only one page in the sheet that would contain 3 pages. example : save as pdf page 2 or from cells a23 to g65. Thanks a lot for your help. Laurent
+Laurent de Moras Try this assuming you wish to save only the mentioned range: Sub mypdf() ActiveSheet.Range("F8:H88").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\Users\takyar\Desktop\test-save.pdf", OpenAfterPublish:=True End Sub Of course, use your own directory path!
Takyar, thank you so much, really really useful. if I wanted to do this for specific worksheets within a workbook, how would I do that? I want to create a button that when the user clicks on it, it attachs sheet 2 and sheet 3, rather than sheet 1 which has the button on it? Thank you for your help Martin
thanks a lot for the courses. sir do you have videos for dunning letter which i can generate thru excel and send via mail - outlook? if i have missed plz send me the link for the video. thanks.
Hi Dinesh. This video is great! My question is: how do I change the code to refer to a cell for the Filename. So specifically, the "test-save" part i would like to refer to a cell instead of hard-copy it in the vba code. The cell will include the supplier name and the invoice month in question. It would be greatly appreciated if you can help me out. Regards, Lisa
This is great. Thank you for sharing. I was able to implement this code, and it brings up the outlook dialog box with my stated message and attachment. However, when I hit send, nothing gets sent until I manually open Outlook. I made sure to check the Outlook box under Visual Basic - Tools - References. I would appreciate any advice you can provide. Thanks.
This link with a downloadable file will help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/ In my code I'm just displaying the message and not sending it. Therefore you need to comment out ',Display' and uncomment '.Send'.
Dinesh, I could run the VBA, issues I face, I shall like to pick up variables like File name, email address, subject from the excel sheet directly, or else what is a point if I need to keep on editing the VBA code for every memo I send? Will you bale to suggest how to bring the cell value to above?
This Excel VBA tutorial will guide: www.exceltrainingvideos.com/tag/send-emails-automatically-via-outlook-using-excel-vba/ It's a good idea to search our website www.exceltrainingvideos.com or this TH-cam channel to find solutions to your VBA queries.
Hello Mr Takyar, great video !!! In order to send an email I need to have my Outlook already open. Otherwise this HTML Emails sits somewhere and waits for OutLokk mail to be opened. How can I deal with that ?
Hi Dinesh, I tried this one and it worked really well. Thank you very much. Can you kindly help show how we can save it such that there is a user interface to put in a specific filename please. Hope you can help please. Thanks.
You can use an input box. This link will help: www.exceltrainingvideos.com/how-to-capture-user-input-via-input-box-and-validate-the-data-input-using-excel-vba/ Or search www.exceltrainingvideos.com
Hello Dinesh, I wrote my first code with your help and now I think I'm hooked for life. Your videos have taught me how to build a command button which converts my excel to PDF dependent on two cells. Now I would like to email this PDF which I created. I watched this video but I'm stuck on the myattachments part since my pdf name is created and has the name = Path & FileName1 & "_HF_" & FileName2 & ".pdf". What should I write under .myattachments. Thank you so much.
First have a look at this link: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/ Or alternatively you can use: .Attachments.Add Path & FileName1 & "_HF_" & FileName2 & ".pdf"
Excellent post! I just need your help with something. How can I modify the code in order to make the email address variable. This idea changes the ".to" line and "myAttachments" Line because the name of the file varies. Thank you very much!
Thanks for your video. How would I make it work in my instance? I have code that saves a file using time and date stamp every time a command button is pressed (saves to the desktop), but every time I refer to the saved file as an attachment, I get a 'the file could not be found' error message. My problem is how to refer to the saved file when adding code to send the email, since the file name is dynamic? Any help would be appreciated, thanks!
So each time I click the button, it saves a new file in the location I specify and I am able to open the files no problem. Here is the code I use to create a new filename each time the button is clicked: "filename1 = Format(CStr(Now), "yyyy_mm_dd_mm") Problem is how I reference this name when asking excel to attach the file to the email? Because the filename is different each time it is saved?
Good day thankyou for the tutorial Takyar I wonder if you could help a little more. When I run the marco all seem to work fine only when the email opens in Outllook I get new .TO and .Cc boxs appearing when I mouse over the excisting ones which in turn stops me from sending the email.
HI thank you for sharring! I have develop you code a little bit and when i save i gets different PDF files names. Therefor i cant send the PDF i have just createt, only the one named in the code - How can i send the newest add PDF add file insted, so i send the right one out :)
Hello SIr Great Video, My Question is that can i send full fill like all sheets . for the time being i am able to send only the current (One Sheet) not full Sheets of the file at-once
Create pdf current tab, select next tab create pdf, etc, first, then batch attachments or merge them. Or make temp tab and copy all other tab data to one tab, pdf, send.
Dr. Takyar, this is great, however, how would you go about automatically changing the file name you are saving it as? Example: If the PDF was saved daily, how would one save it as "30 Apr Report" then tomorrow saved as "1 May Report" and so on?
Hello and thank you for your video. Can you please tell me where can I add to export only one of the sheets to PDF? I am working on a multiple sheet Excel file. Thank you once again.
Very good video thank you! One question, how can I automate this process without me having to be in excel? I would like this email/pdf attachment to automatically send to staff 30 days before a particular date in the Excel document? This would serve as an automated email reminder from Excel that a task we are tracking in Excel has 30 days to be completed. Thanks
Great suggestion! These Excel VBA tutorials will help: www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/ www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/ www.exceltrainingvideos.com/sending-reminder-from-excel-using-gmail-with-cdo/?amp
Thank you for the video. If I need to save all excel charts and only one excel sheet in one pdf (each chart /sheet is a page) how can I do this? let's say I have 1 excel sheet and 15 excel charts and I need the excel sheet to be the first page and then I need to email this one pdf. Thank you so much!
Hi Dinesh, is there any guidance to change the script to include automation of the PDF at a specific time of day without having to manually run the macro? i would like to create a PDF and save it to a shared folder so i do not need to email the reports each time. Many Thanks in advance
Hi Dinesh, Could i email each individual sheet to a different user email(s) as pdf. Could the email be picked up from a particular cell in that individual sheet.
This link will guide: www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/ You can also 'hardwire' the email addresses in '.bcc',
Hi, Dinesh thank you for this tutorial, I am new to VBA and find it so exciting to learn. I tried this code and works really good... I have a question, if I want to name the converted pdf as per the Active Sheet Name, how to do it ? And also if I want to send the email to the email entered on a cell or multiple emails, is that possible? If anyone can help ... please... Thanks in advance.
Could you please help for generating multiple pdf through drop down cell value and email it with attachments and as email body as an image or snapshot of that pdf.
1. Put an incremental counter to a public intX as integer, and in the macro increment it after the first pdf is created like intX + 1 on every run. Since when VBA shuts down the app when run is stopped you'll have to keep the form open (or intX will reset back to zero), or enter an increment in a textbox or persist the count to an external source like the registry or ask the user for input. 2. You could use the filesystemObject to see if the file name exists and if so increment or append the filename so as not to overwrite the existing file, or rename the previously saved file when the macro ran, rename previous pdf to like prePDFFile.pdf or whatever, or move it to a "sent" folder. Or you could prompt for the filename before the pdf was created with the commondialog control or a input style message box, or from a textbox on the form.
Hi Dinesh, Thank you for this great tutorial. I have a problem I'm encountering and hope you can help. in ChDir i replaced the specific location with Thisworkbook.Path & "\" so that it will always export the PDF in the current folder it is in. This is because I created a sales quote template and will always copy paste it into a new client folder to create their own quotes. As such i want to ensure that everytime i run this code it a) exports the active sheet to PDF in the same folder as the excel file and b) attaches that exported PDF to the email. i've figured out most of this on my own but the problem i'm facing is when i'm trying to get the PDF to attach to the email. how do i tell it to find the pdf in the same (variable) directory? so far i've tried myattachments.Add ThisWorkbook.Path & Range("'Costing'!C1") & ".pdf" but it says it cannot find it, even though the PDF is there. thank you and i appreciate you taking the time to read this if you still are :) Ray
@@Exceltrainingvideos thank you for your quick reply. I actually figured out the solution a couple hours after posting this! my issue was that Outlook wasn't finding the PDF file after it was exported. I then added the following and it worked: Dim pathfilename As String With outlookmailitem .To = Range("'Costing'!C9") pathfilename = ThisWorkbook.Path & "\" & Range("'Costing'!C1") & ".pdf" myattachments.Add pathfilename
Hello, First thank you for sharing all this information. I have a main userform that branches out to two other forms and I need to save the Userform as a PDF and send it by email. The email attachment sent out must be the main Userform and access the other two Userforms. Example of my userforms: Main userform input all the customer data then I have a two comand buttons, each one opens a Userform for data entry and user returns back to main Userform.Can you help me with this? Thank you for you cooperationElsa
Hi, thank you for your reply. I tesed the coding today, but get run time error message, cannot execute the "PasteSpecial" of the worksheet.Error line: "ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False", can you help here?Thank you
clipboard.clear before you sendkeys Printscreen. Sometimes the clipboard gets closed prior to Prtscr and you have to fiddle with it so it loads the clipboard.setdata properly prior to the xl paste special.
Hi Dinesh, Thanks for the video. Do you have a way to export the spreadsheet as a fillable PDF form rather than a normal PDF using VBA? Thanks in advance. - Sumanth
These links to VBA tutorials will help: www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/ www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
Hi Dinesh.. I do have many PDF files where 3 PDFs each needs be merged and saved in a different folder with first PDF name.. do we have a macro for that.. I do have Adobe merger by selecting the PDFs and right click and combine.. but need to have a Macro to merge more than 300 pdd
You could mod this to work for 3: Dim AcroApp As Acrobat.CAcroApp Dim Part1Document As Acrobat.CAcroPDDoc Dim Part2Document As Acrobat.CAcroPDDoc Dim numPages As Integer Set AcroApp = CreateObject("AcroExch.App") Set Part1Document = CreateObject("AcroExch.PDDoc") Set Part2Document = CreateObject("AcroExch.PDDoc") 'AcroExch. Part1Document.Open ("C:\Temp\pdfa.pdf") Part2Document.Open ("C:\Temp\pdfb.pdf") ' Insert the pages of Part2 after the end of Part1 numPages = Part1Document.GetNumPages() If Part1Document.InsertPages(numPages - 1, Part2Document, _ 0, Part2Document.GetNumPages(), True) = False Then MsgBox "Cannot insert pages" End If If Part1Document.Save(PDSaveFull, "C:\temp\MergedFile.pdf") = False Then MsgBox "Cannot save the modified document" End If Part1Document.Close Part2Document.Close AcroApp.Exit Set AcroApp = Nothing Set Part1Document = Nothing Set Part2Document = Nothing MsgBox "Done"
Hello Dear, My i know if i have multiple email to send with multiple attachment how i can use vb micro code its possible to use cells value as file name to save the file as a pdf and can use attachment also as cell value . thanks in advance for your help.
Dear . This vedio I can see how to convert active sheet but I need to give file name to save as daynamicaly and attachments also need dynamic it's possible?
can we generate automatically appointment letter by data(name,post,address,koining etc)in excel sheet(employee master sheet) with a single button,and save it in a format of sr no. name_designation.doc.pls make a video on tht as well.
This link will help: www.exceltrainingvideos.com/tag/automate-mail-merge-without-word/ Alternatively do a search at www.exceltrainingvideos.com You may also like to invest in a good Excel book: Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU
Hi Sir, hope u r doing well, kindly tell me , in ur video where i add for password in pdf while i send to employee , every pdf having own password..for every employee
hi dineshji i want the automation to read my email subject line and accordingly attach a file to it from my desktop by matching the keyword in the subject line regards
Hi Sir, Great fan of you. I'm following your channel rigorously and implementing the lessons learnt; however, I'm stuck at the below part. Could you please help me in taking a screen shot of Userform and paste it in the body of the email?. I'm really tired of searching for this resolution across the google. Your help is much appreciated. Thanks in Advance.
hi Dinesh, can you please also help me with this. Instead of attaching a file as pdf, I need the file look like as screen shot of that template from an excel file that will automatically look like part of the body of the email. is that possible? thanks in advance.
The following ideas should help: 1. Select the data 2. From Home --> Copy --> Copy as Picture... 3. Paste the screenshot in your mail 4. If you wish you can paste the data in Paint and save the file as a 'jpg' file. OR Sub takeScreenshot() Sheet1.Range("A1:D18").Copy Sheet2.Activate Sheet2.Range("B1").Select ActiveSheet.Pictures.Paste Link:=True Application.CutCopyMode = False End Sub
Dear Dr Takyar, Is there a way to print a PDF file with VBA? I am using windows 10 , 32 bit version, and excel 2010. I searched much, but unable to find a working method. Thank you. Piyal Fernando (Sri Lanka)
Hi Denish. I am trying to transfer my Invoice to PDF but that will not work. This is the Code I use. Private Sub cmdPDF_Click() Dir "C:\Users\Desktop\Database" ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "C:\\Users\Desktop\Database\Incoice-save.pdf", OpenAfterPublish:=True End Sub And it will not either take ChDir I don't know if it has something to do with I using Windows 8
@@Exceltrainingvideos Dear Sirs, cause my payment voucher there is a company logo on it and I have set in the header. If i will go run this code, will it also capture the logo.
@@Exceltrainingvideos - just to confirm this part on the Tools > References I must remember to tick Microsoft office 15.0 Object Library and Microsoft outlook 15.0 Object Library
Sir, this is a great video, and I have two questions. I receive a popup with the following message: "Run-time error '2147024894 (80070002)'" and "Automation error The system cannot find the file specified", but the run appears to be successful because I see the PDF display. Instead of seeing an email box on the right side I see Adobe Export PDF that would convert the file. Also, is there a way to tie this to a button on the spreadsheet so clicking the button performs the function in place of clicking RUN? Thank you again.
@@Exceltrainingvideos No, I did not. How do I do that? Also, do you have a TH-cam video that demonstrates how to format a userform text box such that an entered phone number is properly formatted (e.g. (xxx) xxx-xxxx)? Thank you again.
DEAR SIR, I have made one user form(for my Off Job) and converted into pdf using your code's its working fine, Thanks for the video. I have given demo at my off and got good feedback's and appreciation. With the officer's feed back i have to circulate only a message with respective people using Thunderbird only. Can you help me. I will be waiting for you reply Thank you.
Put the Range("T18").Value cell values to a variable as a textstring at the start of the macro, format it and then set it to the body of your Outlook message, then send it as Dinesh has shown.
I have created a macro in my personal workbook but when I saved it(macro enabled) in a shared path my teammates are unable to run the macro and there is no VBA found in their system. Please help me to fix this
Hi Takyar, it is a brilliant video. I have two questions. 1. In Subject it is possible to add present date and then followed by data. 2. Can i use the same procedure for word.
I have an excel file with some data in it and I convert the excel file to CSV so that I can import the required data from CSV File to PDF Form however to complete each PDF Form I need to import the records from CSV File again and again. I wanted to know if there is a way out to fill multiple PDF Forms in 1 click. Kindly help.
You can use a looping process. This link will guide: www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/ Or search www.exceltrainingvideos.com
Dear Mr. Dinesh, First of all thank you very much for this brilliant explanation. But could you please explain us how to set up a VBA code in the case when user doesn't have Outlook or any similar applications. In my case I don't have permission to install any application like Outlook or similar so I have to use only hotmail. Please I would be grateful if you might be able to help me write a VBA code for this case. Thank you very much Kind regards
Dear Mr. Dinesh, Thank you so much for your prompt response to my question. This works perfectly when I'm using gmail, but as I mentioned above I need to set up a VBA code just for hotmail and no other email services such as gamail, yahoo or apps like Outlookm Thunderbird etc. Hope I was clear. Kind regards
Sir, I am looking for a macro that can convert multiple word documents into multiple PDF documents with a single click.. Will the below code help me? But I am getting error while running this code Sub Save_to_PDF() ' ' Save_to_PDF Macro ' ' With Dialogs(wdDialogFileSaveAs) .Format = wdFormatPDF .Show End With End Sub
Hi Dinesh Kumar Ji, I need a code for exporting excel data to a pdf, and that pdf can not be editable by any ony, i saw u r video its helpful to me, but that pdf is editable by using pdf edit software, could you please suggest me how to create uneditable pdf through macro
These two VBA solutions will guide: www.exceltrainingvideos.com/3-excel-vba-training-questions-on-youtube/ www.exceltrainingvideos.com/open-any-third-party-application-using-shell-function/
@@Exceltrainingvideos Thanks for the reply. when I change the .xls it's saving in xps format n not atattched as exportas fixed format is xltypepdf. please help me. if needed I can send my Excel format even via mail.
Hello Dinesh, I'm getting the 404 Error-Object not found by the line: myAttachments.Add "C:\Users\Sasu\Desktop\file-Test.pdf" PDF Creation works fine, but the sending nis ot working. any idea what I might be doing wrong? Thx btw, you're doing a great job!
I love that your videos pop up when I google how to complete a task in VBA. You are a great teacher - I just wanted to thank you!
Thank you very much Dr. I met your videos only in last week and they help me to improve my knowledge in VBA =, Thank you so much again. Piyal Fernando (Sri Lanka)
Glad to hear that. Please share with your friends too.
@@Exceltrainingvideos Sure sir....
I have simply followed Your instructions and all works perfectly ! Thanks for sharing Your knowledge.
Great to hear!
Congrats! Dinesh on reaching 100 K subscribers! You have helped me put food on my table to feed my family for many years with your teaching. I am thankful
Thank you so much 😀. Please share the Excel tutorials with your friends too.
You are so talent teacher I never see before. You are great. Thank you so much.
Dinesh Sir you are simply great .....This video was great help to me ...Thank you very much for your noble job of publishing so informative stuff through your videos...I am your great Fan Sir !!!!
this is very nice. What if the name of the pdf changes every time & recipient. The solution could be to just bring up the outlook email and no not write the send code then I will be able to change the recipient's name every time. that part is easy but what about the pdf as my macro allows me to save as pdf and brings up the save as for me to enter a specific location to save my pdf forms. This can be a task but I thank you for the inspiration and I will try this
That's a great idea! These links will guide:
www.exceltrainingvideos.com/automate-invoice-report-generation-using-excel-vba/
www.exceltrainingvideos.com/how-to-transfer-data-to-an-invoice-template/
www.exceltrainingvideos.com/automatically-update-inventory-while-creating-invoice/
Thank you Dinesh, once again you have rescued me from a place where I was stuck
i am having an excel file with list of email IDs & file path against each mail ID. please help me with macro which will help me to send individual email with said file as attachment
Thank you for this Dinesh. My question is how to express formula to send the payslip to the respective email add...considering i have an email address column in my active sheet.Thank you so much.
Dinesh you are truly a genius!
I like so much what you do!!
Thank you very much!!
awesome video. how to add on in this vba with adding password on the exported pdf? also is there a way to automate different password to different client or recipient name?
Thanks a lot for your courses, Takyar,
If I want to save as pdf only one page in the sheet that would contain 3 pages.
example : save as pdf page 2 or from cells a23 to g65.
Thanks a lot for your help.
Laurent
+Laurent de Moras Try this assuming you wish to save only the mentioned range:
Sub mypdf()
ActiveSheet.Range("F8:H88").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\takyar\Desktop\test-save.pdf", OpenAfterPublish:=True
End Sub
Of course, use your own directory path!
+Dinesh Kumar Takyar
Thank you so mutch for your fast answer.
Have a nice day.
Sincerely.
laurent
Easy to digest tutorial. thanks a lot!
Glad it helped! Please share the Excel VBA tutorial with your friends also on social media.
Takyar, thank you so much, really really useful. if I wanted to do this for specific worksheets within a workbook, how would I do that? I want to create a button that when the user clicks on it, it attachs sheet 2 and sheet 3, rather than sheet 1 which has the button on it? Thank you for your help Martin
Great tool, thanks so much!
Is there a way to generate multiple PDF and Email windows at same time if you had multiple sheets on the excel file?
You can use alooping process as shown in this video to access each sheet and save each sheet as PDF: th-cam.com/video/KI2GXPXTxOs/w-d-xo.html
Thanks a lot sir, this has helped a lot... God bless you!
Glad it helped. Please share my TH-cam channel on Excel VBA with your friends too. Thanks.
thanks a lot for the courses. sir do you have videos for dunning letter which i can generate thru excel and send via mail - outlook? if i have missed plz send me the link for the video. thanks.
Hi. Thanks for the video. Is there a way to export whole workbook instead of one sheet. Thanks 😊
This link will help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
@@Exceltrainingvideos Thanks a lot!
Hi Dinesh. This video is great! My question is: how do I change the code to refer to a cell for the Filename. So specifically, the "test-save" part i would like to refer to a cell instead of hard-copy it in the vba code. The cell will include the supplier name and the invoice month in question. It would be greatly appreciated if you can help me out.
Regards,
Lisa
This link might help: www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/
This is great. Thank you for sharing. I was able to implement this code, and it brings up the outlook dialog box with my stated message and attachment. However, when I hit send, nothing gets sent until I manually open Outlook. I made sure to check the Outlook box under Visual Basic - Tools - References. I would appreciate any advice you can provide. Thanks.
This link with a downloadable file will help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
In my code I'm just displaying the message and not sending it. Therefore you need to comment out ',Display' and uncomment '.Send'.
Dinesh, I could run the VBA, issues I face, I shall like to pick up variables like File name, email address, subject from the excel sheet directly, or else what is a point if I need to keep on editing the VBA code for every memo I send? Will you bale to suggest how to bring the cell value to above?
This Excel VBA tutorial will guide: www.exceltrainingvideos.com/tag/send-emails-automatically-via-outlook-using-excel-vba/
It's a good idea to search our website www.exceltrainingvideos.com or this TH-cam channel to find solutions to your VBA queries.
Hello Mr Takyar, great video !!! In order to send an email I need to have my Outlook already open. Otherwise this HTML Emails sits somewhere and waits for OutLokk mail to be opened. How can I deal with that ?
Hi Dinesh, is it possible to attach multiple PDF with multiple tabs in the excel sheet?
Hi Dinesh can you please share a Link where you have started on line courses from scrap.
You can start here: th-cam.com/users/excelvbaonline
Hi Dinesh, I tried this one and it worked really well. Thank you very much. Can you kindly help show how we can save it such that there is a user interface to put in a specific filename please. Hope you can help please. Thanks.
You can use an input box. This link will help: www.exceltrainingvideos.com/how-to-capture-user-input-via-input-box-and-validate-the-data-input-using-excel-vba/
Or search www.exceltrainingvideos.com
Hello Dinesh, I wrote my first code with your help and now I think I'm hooked for life. Your videos have taught me how to build a command button which converts my excel to PDF dependent on two cells. Now I would like to email this PDF which I created. I watched this video but I'm stuck on the myattachments part since my pdf name is created and has the name = Path & FileName1 & "_HF_" & FileName2 & ".pdf". What should I write under .myattachments. Thank you so much.
First have a look at this link: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
Or alternatively you can use:
.Attachments.Add Path & FileName1 & "_HF_" & FileName2 & ".pdf"
Excellent post!
I just need your help with something. How can I modify the code in order to make the email address variable. This idea changes the ".to" line and "myAttachments" Line because the name of the file varies.
Thank you very much!
Populate a drop down combo box with email references. Select combo item before run, as needed.
Thanks for your video. How would I make it work in my instance? I have code that saves a file using time and date stamp every time a command button is pressed (saves to the desktop), but every time I refer to the saved file as an attachment, I get a 'the file could not be found' error message. My problem is how to refer to the saved file when adding code to send the email, since the file name is dynamic? Any help would be appreciated, thanks!
First check whether you can open the file on your computer using the right path in a macro code.
So each time I click the button, it saves a new file in the location I specify and I am able to open the files no problem. Here is the code I use to create a new filename each time the button is clicked:
"filename1 = Format(CStr(Now), "yyyy_mm_dd_mm")
Problem is how I reference this name when asking excel to attach the file to the email? Because the filename is different each time it is saved?
A simple string is created using that line of code.
Good day thankyou for the tutorial Takyar I wonder if you could help a little more. When I run the marco all seem to work fine only when the email opens in Outllook I get new .TO and .Cc boxs appearing when I mouse over the excisting ones which in turn stops me from sending the email.
HI thank you for sharring!
I have develop you code a little bit and when i save i gets different PDF files names. Therefor i cant send the PDF i have just createt, only the one named in the code - How can i send the newest add PDF add file insted, so i send the right one out :)
This VBA tutorial will guide: th-cam.com/video/ZGfUxRP9O_8/w-d-xo.html
@@Exceltrainingvideos Thank you, I just dont show how to attatch the latest file to an outlook mail
And how do you in the same code, make it send from a specifik mail account everytime, if you have more than one ?
Hello SIr Great Video, My Question is that can i send full fill like all sheets . for the time being i am able to send only the current (One Sheet) not full Sheets of the file at-once
Create pdf current tab, select next tab create pdf, etc, first, then batch attachments or merge them. Or make temp tab and copy all other tab data to one tab, pdf, send.
Dr. Takyar, this is great, however, how would you go about automatically changing the file name you are saving it as? Example: If the PDF was saved daily, how would one save it as "30 Apr Report" then tomorrow saved as "1 May Report" and so on?
Vic AM You might get a tip here: www.exceltrainingvideos.com/create-invoices-using-template-with-userform-in-excel/
Diesh, Great video
Could you tell me what the parameter 0 in the line set OutlookMailItem=OutlookApp CreatItem (0) does?
+davidfunvideos 0 refers to 'olMailItem' or OutLookMailItem.
Hello and thank you for your video. Can you please tell me where can I add to export only one of the sheets to PDF? I am working on a multiple sheet Excel file.
Thank you once again.
This link will help: www.exceltrainingvideos.com/tag/how-to-save-invoice-template-as-pdf-file/
Or search www.exceltrainingvideos.com
Thank you. This solved a problem I have bee struggling with ever since I created my invoice
Very good video thank you!
One question, how can I automate this process without me having to be in excel? I would like this email/pdf attachment to automatically send to staff 30 days before a particular date in the Excel document?
This would serve as an automated email reminder from Excel that a task we are tracking in Excel has 30 days to be completed.
Thanks
Great suggestion! These Excel VBA tutorials will help:
www.exceltrainingvideos.com/using-dates-with-excel-vba-to-automate-email-reminders/
www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
www.exceltrainingvideos.com/sending-reminder-from-excel-using-gmail-with-cdo/?amp
Thank you for the video.
If I need to save all excel charts and only one excel sheet in one pdf (each chart /sheet is a page) how can I do this? let's say I have 1 excel sheet and 15 excel charts and I need the excel sheet to be the first page and then I need to email this one pdf.
Thank you so much!
Hi Dinesh, is there any guidance to change the script to include automation of the PDF at a specific time of day without having to manually run the macro? i would like to create a PDF and save it to a shared folder so i do not need to email the reports each time. Many Thanks in advance
Hi Dinesh,
Could i email each individual sheet to a different user email(s) as pdf. Could the email be picked up from a particular cell in that individual sheet.
Hi there, I need to do exactly the same you describe... hope you get an answer!
+Juan Silva
Go to this page and download the pdf examples.
www.rondebruin.nl/win/s5/pdf.htm
I used Macro 5 and made some changes.
Its pretty good.
+Paddy BEERAKA THANKS!!!!! i'm on it already!
+Paddy BEERAKA I can't thank you enough, it worked perfectly for me!! Regards
Hi Dinesh how can we email to multiple client using this format
This link will guide: www.exceltrainingvideos.com/how-to-send-email-reminder-automatically-from-excel-worksheet-using-vba/
You can also 'hardwire' the email addresses in '.bcc',
Hi, Dinesh thank you for this tutorial, I am new to VBA and find it so exciting to learn. I tried this code and works really good... I have a question, if I want to name the converted pdf as per the Active Sheet Name, how to do it ? And also if I want to send the email to the email entered on a cell or multiple emails, is that possible? If anyone can help ... please... Thanks in advance.
Search www.exceltrainingvideos.com
Could you please help for generating multiple pdf through drop down cell value and email it with attachments and as email body as an image or snapshot of that pdf.
Let me check.
@@Exceltrainingvideos Sir, please help your videos are so amazing and i am sure enough to get solution from your end.
How to do iteration for multiple files in excel ( for loop ? )
could you please tell what to do if i want to save new pdf every time i run the macro instead of overlapping the current pdf file.??
1. Put an incremental counter to a public intX as integer, and in the macro increment it after the first pdf is created like intX + 1 on every run. Since when VBA shuts down the app when run is stopped you'll have to keep the form open (or intX will reset back to zero), or enter an increment in a textbox or persist the count to an external source like the registry or ask the user for input.
2. You could use the filesystemObject to see if the file name exists and if so increment or append the filename so as not to overwrite the existing file, or rename the previously saved file when the macro ran, rename previous pdf to like prePDFFile.pdf or whatever, or move it to a "sent" folder.
Or you could prompt for the filename before the pdf was created with the commondialog control or a input style message box, or from a textbox on the form.
Can this be used for windows live Mail? What changes to code would I need to make? Thank you in advance.
Hi Dinesh,
Thank you for this great tutorial. I have a problem I'm encountering and hope you can help. in ChDir i replaced the specific location with Thisworkbook.Path & "\" so that it will always export the PDF in the current folder it is in. This is because I created a sales quote template and will always copy paste it into a new client folder to create their own quotes. As such i want to ensure that everytime i run this code it a) exports the active sheet to PDF in the same folder as the excel file and b) attaches that exported PDF to the email.
i've figured out most of this on my own but the problem i'm facing is when i'm trying to get the PDF to attach to the email. how do i tell it to find the pdf in the same (variable) directory? so far i've tried myattachments.Add ThisWorkbook.Path & Range("'Costing'!C1") & ".pdf" but it says it cannot find it, even though the PDF is there.
thank you and i appreciate you taking the time to read this if you still are :)
Ray
Use this line: Filename:=ThisWorkbook.Path & "\" & ThisWorkbook.Sheets("Costing").Range("C1") & ".pdf"
Share your feedback with the community!
@@Exceltrainingvideos thank you for your quick reply. I actually figured out the solution a couple hours after posting this! my issue was that Outlook wasn't finding the PDF file after it was exported. I then added the following and it worked:
Dim pathfilename As String
With outlookmailitem
.To = Range("'Costing'!C9")
pathfilename = ThisWorkbook.Path & "\" & Range("'Costing'!C1") & ".pdf"
myattachments.Add pathfilename
Hi I would like to Send email button, which will send the current sheet as pdf to one or two email addresses mentioned in the sheet
very useful video, how can I add button in the excel sheet so if I press the button it will export the PDF file?
+Amal SH This link might help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
Hello, First thank you for sharing all this information. I have a main userform that branches out to two other forms and I need to save the Userform as a PDF and send it by email. The email attachment sent out must be the main Userform and access the other two Userforms. Example of my userforms: Main userform input all the customer data then I have a two comand buttons, each one opens a Userform for data entry and user returns back to main Userform.Can you help me with this? Thank you for you cooperationElsa
Private Sub CommandButton2_Click()
Application.SendKeys "(%{1068})"
ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False
MyPDF = ActiveWorkbook.Path & "\" & Me.Name & "-" & Format(Now, "mm-dd-yyyy") & ".pdf"
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=MyPDF, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Worksheets(Worksheets.Count).Delete
Unload Me
End Sub
Hi, thank you for your reply. I tesed the coding today, but get run time error message, cannot execute the "PasteSpecial" of the worksheet.Error line: "ActiveSheet.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False", can you help here?Thank you
clipboard.clear before you sendkeys Printscreen. Sometimes the clipboard gets closed prior to Prtscr and you have to fiddle with it so it loads the clipboard.setdata properly prior to the xl paste special.
Its very useful for me thanks lot.....
If there have any option for multiple time auto email kindly advice
Hi Dinesh,
Thanks for the video. Do you have a way to export the spreadsheet as a fillable PDF form rather than a normal PDF using VBA?
Thanks in advance.
- Sumanth
This is awesome. Suppose I wanted to use my excel data as the name to save the PDF file how would I do that inVBA?
These links to VBA tutorials will help:
www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/
www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
Hi, it is perfect.. I am planning to add passowrd to the pdf file... can someone help me real quick please...
Hi Dinesh.. I do have many PDF files where 3 PDFs each needs be merged and saved in a different folder with first PDF name.. do we have a macro for that.. I do have Adobe merger by selecting the PDFs and right click and combine.. but need to have a Macro to merge more than 300 pdd
You could mod this to work for 3:
Dim AcroApp As Acrobat.CAcroApp
Dim Part1Document As Acrobat.CAcroPDDoc
Dim Part2Document As Acrobat.CAcroPDDoc
Dim numPages As Integer
Set AcroApp = CreateObject("AcroExch.App")
Set Part1Document = CreateObject("AcroExch.PDDoc")
Set Part2Document = CreateObject("AcroExch.PDDoc")
'AcroExch.
Part1Document.Open ("C:\Temp\pdfa.pdf")
Part2Document.Open ("C:\Temp\pdfb.pdf")
' Insert the pages of Part2 after the end of Part1
numPages = Part1Document.GetNumPages()
If Part1Document.InsertPages(numPages - 1, Part2Document, _
0, Part2Document.GetNumPages(), True) = False Then
MsgBox "Cannot insert pages"
End If
If Part1Document.Save(PDSaveFull, "C:\temp\MergedFile.pdf") = False Then
MsgBox "Cannot save the modified document"
End If
Part1Document.Close
Part2Document.Close
AcroApp.Exit
Set AcroApp = Nothing
Set Part1Document = Nothing
Set Part2Document = Nothing
MsgBox "Done"
Is that possible to assign a time or date to let this PDF excel file automate send by email in daily or weekly?
Hi, how should I change the code if I wanted to choose the mail sender ?
You can use an inputbox, for example.
is it possible using a company email? different from gmail, yahoo
Hello there! one quick question, does this run on iOS? and instead of Outlook with Mac Mail? Thanks!
This link will help: www.rondebruin.nl/mac/section2.htm
Hello Dear,
My i know if i have multiple email to send with multiple attachment how i can use vb micro code its possible to use cells value as file name to save the file as a pdf and can use attachment also as cell value .
thanks in advance for your help.
Do a search at www.exceltrainingvideos.com
Dear .
This vedio I can see how to convert active sheet but I need to give file name to save as daynamicaly and attachments also need dynamic it's possible?
can we generate automatically appointment letter by data(name,post,address,koining etc)in excel sheet(employee master sheet) with a single button,and save it in a format of sr no. name_designation.doc.pls make a video on tht as well.
This link will help: www.exceltrainingvideos.com/tag/automate-mail-merge-without-word/
Alternatively do a search at www.exceltrainingvideos.com
You may also like to invest in a good Excel book: Excel 2016 Power Programming with VBA: amzn.to/2kDP35V If you are from India you can get this book here: amzn.to/2jzJGqU
I want to add signature
how to upload a pdf file and save in to a folder using VBA
This link will guide: www.exceltrainingvideos.com/save-excel-files-automatically-using-cell-data/
Hi Sir, hope u r doing well, kindly tell me , in ur video where i add for password in pdf while i send to employee , every pdf having own password..for every employee
hi
dineshji
i want the automation to read my email subject line and accordingly attach a file to it from my desktop by matching the keyword in the subject line
regards
Hi Sir,
Great fan of you. I'm following your channel rigorously and implementing the lessons learnt; however, I'm stuck at the below part.
Could you please help me in taking a screen shot of Userform and paste it in the body of the email?. I'm really tired of searching for this resolution across the google. Your help is much appreciated. Thanks in Advance.
hi Dinesh, can you please also help me with this. Instead of attaching a file as pdf, I need the file look like as screen shot of that template from an excel file that will automatically look like part
of the body of the email. is that possible? thanks in advance.
The following ideas should help:
1. Select the data
2. From Home --> Copy --> Copy as Picture...
3. Paste the screenshot in your mail
4. If you wish you can paste the data in Paint and save the file as a 'jpg' file.
OR
Sub takeScreenshot()
Sheet1.Range("A1:D18").Copy
Sheet2.Activate
Sheet2.Range("B1").Select
ActiveSheet.Pictures.Paste Link:=True
Application.CutCopyMode = False
End Sub
how do I change the mail to equal an email listed in the document?
This link will help: th-cam.com/video/wjSvoWgb5Lk/w-d-xo.html
sir, how to export pdf in multiple specified work sheet
Thank you. Very Useful
Also I need a macro to retrieve invoice status from the website which has login details
Dear Dr Takyar,
Is there a way to print a PDF file with VBA? I am using windows 10 , 32 bit version, and excel 2010. I searched much, but unable to find a working method.
Thank you.
Piyal Fernando (Sri Lanka)
Let me check
Hi Denish. I am trying to transfer my Invoice to PDF but that will not work. This is the Code I use.
Private Sub cmdPDF_Click()
Dir "C:\Users\Desktop\Database"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\\Users\Desktop\Database\Incoice-save.pdf", OpenAfterPublish:=True
End Sub
And it will not either take ChDir
I don't know if it has something to do with I using Windows 8
This link with a downloadable sample file will help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
but sirs if my excel consist there is a co logo will it capture?
Query not clear.
@@Exceltrainingvideos Dear Sirs, cause my payment voucher there is a company logo on it and I have set in the header. If i will go run this code, will it also capture the logo.
@@Exceltrainingvideos - just to confirm this part on the Tools > References I must remember to tick
Microsoft office 15.0 Object Library and
Microsoft outlook 15.0 Object Library
How can we upload multiple excel file from local path to SharePoint url using VBA please suggest.
I'm not using Sharepoint at present.
Can you please share the code if possible
Sir, this is a great video, and I have two questions. I receive a popup with the following message: "Run-time error '2147024894 (80070002)'" and "Automation error The system cannot find the file specified", but the run appears to be successful because I see the PDF display. Instead of seeing an email box on the right side I see Adobe Export PDF that would convert the file. Also, is there a way to tie this to a button on the spreadsheet so clicking the button performs the function in place of clicking RUN? Thank you again.
Error may be related to the 'mail'. Did you activate the outlook library?
@@Exceltrainingvideos No, I did not. How do I do that? Also, do you have a TH-cam video that demonstrates how to format a userform text box such that an entered phone number is properly formatted (e.g. (xxx) xxx-xxxx)? Thank you again.
DEAR SIR, I have made one user form(for my Off Job) and converted into pdf using your code's its working fine, Thanks for the video.
I have given demo at my off and got good feedback's and appreciation. With the officer's feed back i have to circulate only a message with respective people using Thunderbird only. Can you help me. I will be waiting for you reply
Thank you.
How can I do it via IBM notes
I don't know.
hi how can we automatically send email with recorded macro that is copying cells or info in excel?
Put the Range("T18").Value cell values to a variable as a textstring at the start of the macro, format it and then set it to the body of your Outlook message, then send it as Dinesh has shown.
Can we use something other than PDF? I would like to just export the work sheet as it self
Much needed tutorial. Thanx!
Thank you Very much. It is Very useful.
Plz sir how can create account ledger in excell.
We'll work on this idea.
Hi sir I need mail merge pdf with password protected is there any option do it.
This VBA tutorial will help: th-cam.com/video/hRzNBRA1A1M/w-d-xo.html
I have created a macro in my personal workbook but when I saved it(macro enabled) in a shared path my teammates are unable to run the macro and there is no VBA found in their system. Please help me to fix this
Hi Takyar, it is a brilliant video. I have two questions. 1. In Subject it is possible to add present date and then followed by data. 2. Can i use the same procedure for word.
This link will guide: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
I have an excel file with some data in it and I convert the excel file to CSV so that I can import the required data from CSV File to PDF Form however to complete each PDF Form I need to import the records from CSV File again and again. I wanted to know if there is a way out to fill multiple PDF Forms in 1 click. Kindly help.
You can use a looping process. This link will guide:
www.exceltrainingvideos.com/copy-data-to-another-excel-workbook-based-on-criteria-using-vba/
Or search www.exceltrainingvideos.com
Dear Mr. Dinesh,
First of all thank you very much for this brilliant explanation. But could you please explain us how to set up a VBA code in the case when user doesn't have Outlook or any similar applications. In my case I don't have permission to install any application like Outlook or similar so I have to use only hotmail. Please I would be grateful if you might be able to help me write a VBA code for this case.
Thank you very much
Kind regards
This link will help: www.exceltrainingvideos.com/tag/using-cdo-to-send-email-automatically/
Dear Mr. Dinesh,
Thank you so much for your prompt response to my question. This works perfectly when I'm using gmail, but as I mentioned above I need to set up a VBA code just for hotmail and no other email services such as gamail, yahoo or apps like Outlookm Thunderbird etc.
Hope I was clear.
Kind regards
Excelente! Just what i needed. Thank You.
Divaldo Carlos Glad this helped!
Hello sir,
How can i save the selected range as pdf(like column A ,column L and column N only as pdf) in excel and send as attachment
Sir, I am looking for a macro that can convert multiple word documents into multiple PDF documents with a single click..
Will the below code help me? But I am getting error while running this code
Sub Save_to_PDF()
'
' Save_to_PDF Macro
'
'
With Dialogs(wdDialogFileSaveAs)
.Format = wdFormatPDF
.Show
End With
End Sub
Hi Dinesh Kumar Ji, I need a code for exporting excel data to a pdf, and that pdf can not be editable by any ony, i saw u r video its helpful to me, but that pdf is editable by using pdf edit software, could you please suggest me how to create uneditable pdf through macro
Which software have you used to edit a converted PDF file?
they are using Acrobat Reader Ji.
Dear Sir
Can I send it from my Firefox application.
These two VBA solutions will guide:
www.exceltrainingvideos.com/3-excel-vba-training-questions-on-youtube/
www.exceltrainingvideos.com/open-any-third-party-application-using-shell-function/
Can I send xls file Instead of PDF? If so what will be the code??
Yes. Just use your file name 'abc.xls' instead of 'abc.pdf'.
@@Exceltrainingvideos Thanks for the reply. when I change the .xls it's saving in xps format n not atattched as exportas fixed format is xltypepdf. please help me. if needed I can send my Excel format even via mail.
Hello Dinesh,
I'm getting the 404 Error-Object not found by the line: myAttachments.Add "C:\Users\Sasu\Desktop\file-Test.pdf"
PDF Creation works fine, but the sending nis ot working. any idea what I might be doing wrong?
Thx btw, you're doing a great job!
This link with a downloadable sample file will help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
Needed the Attachment code for another project. Thanks again.
www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
This is very nice indeed and good information. Keep getting errors on: Set OutLookApp = CreateObject(Outlook.Application)
This link with a downloadable sample file will help: www.exceltrainingvideos.com/automate-excel-to-pdf-and-email-pdf-document-using-vba/
you forgot quotes around ("OutLook.Application")
How can I save file as pdf only.
If I want my pdf attachment with today's date , what should be done??
Concatenate the filename with a date.
This tutorial will help: www.exceltrainingvideos.com/timer-in-excel-using-vba/
I need to do this with ms access
i can't do it with excel 2007 in windows XP. please help me.