VBA Help Needed - Save Current Sheet as PDF and Email out to Recipient

[alecp]

Hello Mr. Excel Helpers,

I need lots of help with a nice VBA application, but I have absolutely no idea what I'm doing.

I need a macro to do the following things:

1. Save only the current tab as a pdf.
2. Send the pdf as an email with:
   1. the name of the recipient coming from a cell in the current sheet (i have many sheets, but will be the same cell in each)
   2. the subject able to be specified (only once - either in the code or somewhere in a sheet, it makes no difference. Just as long as I can change it in the future)
   3. the body able to be specified (same as above)

I have tried doing this with a couple of different postings online, but nothing has seemed to work for me. I have my email IMAP'ed into outlook, so that part is already done.

If you could help in any way, I would greatly appreciate it!

 

[Michael M]

Hi and welcome to the Board
Have a look here for Everything to do the things you require.
The code is already supplied as examples

www.ronDeBruin.nl/sendmail

 

[alecp]

Ah I had read some things from this poster.

I found exactly what I need here [from the same person]:

Creating and Sending PDF Files from Excel 2007 and Excel 2010

but I cannot get it to work! I do not know where I'm going wrong.

Any ideas?

 

[Michael M]

Ok, firstly....go to the website provided not msdn !
Ron has heaps of different options to do stuff like this on his website.
If it still fails to work you will have to provide information on
1. what you want to do
2. details of waht needs to be saved to where
3. where all the information is in the workbook
5. the code you have used
6. AND what is / isn't working for you, with any errors in the code, error messages, etc.

 

[alecp]

Okay thank you for your assistance. I have been experimenting with it and it's almost fine-tuned to how I want it! Here's the code for the particular part of his guide that i'm working with:

Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail()
Dim FileName As String


If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "There is more then one sheet selected," & vbNewLine & _
"be aware that every selected sheet will be published"
End If


'Call the function with the correct arguments
'Tip: You can also use Sheets("Sheet3") instead of ActiveSheet in the code(sheet not have to be active then)
FileName = RDB_Create_PDF(ActiveSheet, "C:\Users\Alec\Desktop\Tau\Financial Information Spring 2013\Personalized Invoices\export.pdf", True, False)


'For a fixed file name and overwrite it each time you run the macro use
'RDB_Create_PDF(ActiveSheet, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)


If FileName <> "" Then
RDB_Mail_PDF_Outlook FileName, "email from a cell in the active sheet", "subject from a specific cell on a specific sheet", _
"body from a specific cell on a specific sheet" _
& vbNewLine & vbNewLine & "signature from a specific cell on a specific sheet", False
Else
MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
"Microsoft Add-in is not installed" & vbNewLine & _
"You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
"The path to Save the file in arg 2 is not correct" & vbNewLine & _
"You didn't want to overwrite the existing PDF if it exist"
End If
End Sub


I've put in bold the things I want to change. I don't know how to do this (even as simple as it may be!) in VBA.
Any thoughts?

 

[Michael M]

Ok, but you don't appear to have made any changes from the code supplied by Ron !
This code is only a small part of what is required. You also need to setup the outlook object in the code.
If this is the case you need to refer back to Post #4