I was trying to send a worksheet as a pdf but realise now that probably was not the best option. The better option would be to insert the worksheet into the body of the email instead. I have the need to send out a worksheet to many various people (about 40)
I have a great working knowledge of excel but a complete novice when it comes to adding VBA code.
I uploaded the following code but realised it was the wrong one.
Any assistance you can give me would be great, or better still if you could send me something I could insert
My preferred option is to also insert a command button and operate the Macro from there ??
The worksheet is called Invoice. the email address I need the code to look for is in cell D16
Regards
Hi Wills,
A sample file was a better option. I'm attaching a sample file, most probably it will not match your design, so you will still have to work on your file, to add the button and the code. The worksheet name is irrelevant, the code will take any active sheet.
You can add any shape as a button, from Insert Tab - Shapes, then right click the inserted shape and choose Assign macro, then choose this code from the list.
The code will take the email address from cell D6, and the month-year from F6, you can find these 2 parameters in lines 26 and 50 of the code.
Let us know if you managed to make it work.
Catalin- I've been reading through this blog for a while now, and I think i'm getting closer to my goal. I also have a workbook with multiple sheets. I'm new to VBA, and all I can get it to do is email one sheet. The 'Account Summary' Sheet. I would like the one button on the account summary sheet to convert and send all the sheets in the workbook. I've attached the Coffee Mess file as a test. All the emails in cell A7 are mine so i'm not clobbering peoples inboxes while i work on this. But each A7 value would correspond to a different email address.
Any help would be greatly appreciated! Thank you! And thanks for this awesome blog, its very helpful.
Tom
Try this version, it will send an email for each sheet, the PDF files will be saved in the same folder where this tool is saved (in a new folder named PDF Folder)
I will remove your email address from the file you posted, unless you like to receive lots of emails from users testing this file 🙂
Catalin-
I can't get the file attachment to open. It says the file is unavailable.
Here it is again.
Basically, instead of sending the active sheet only, I added a new procedure that will loop through all sheets, activate the sheet, then call the email procedure:
Sub SendAllSheets()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
create_and_email_pdf
Next
End Sub
You already have a very similar code, I just copied it and added the procedure to be called.
I also replace the FolderPicker dialog, with a predefined destination folder:
DestFolder = ThisWorkbook.Path & Application.PathSeparator & "PDF Folder"
If Len(Dir$(DestFolder, vbDirectory)) = 0 Then MkDir DestFolder
