Macro to create PDF from Excel 2003 and send as attachment in email

Use code to make dyn file names from cell contents and save to dyn dir

Hey Ken,


Quick question for ya (I'm sure not a quick answer tho....:))


I would like to know what I will need to modify in your code to save a pdf with its name derived from a cell in the active sheet.

For example:

I have a sheet with a cell that contains a concatentation of things from other cells:

Sheet1!A5 = JoeSmith_12345_BillingStatement_05-21-2012

Thus I want the file name to be:
*CustomerName*_*CustomerAcctNumber*_BillingStatement_MM-DD-YYYY.pdf

and be saved to a UNC path:
\\ data \ wireless \ receivable \ *CustomerName* \ *CustomerName_YYYY*

so when you looke at the full path to the file you see:
UNCPath\ *CustomerName*_*CustomerAcctNumber*_BillingStatement_MM-DD-YYYY.pdf

Please note that that the items between the *'s are variable and are derived from other areas of the same sheet.

One other nice thing would be if the dir does not exist then mkdir on the fly without user intervention being needed.

I also will need to know what module I will need to palce the code for it to work. Thank a million in advance!!

- Jerry L Englehaupt
Wireless World
 
Saving Statements

Jerry,

See if this is what you might be shooting for.

1. create this directory or folder structure before you get started

C:\data\wireless\receivable

the macro will automaticallly create the rest of the folder structure and save the PDF .

just fill in a customer name Ex. ( Jon Doe) and a account number ex.( JD0001) and click the Save Statement Button

when it is done you would have the following folder structure

C:\data\wireless\receivable\Jon Doe\Jon Doe_2012\ JonDoe_JD0001_BillingStatement_05-21-2012.pdf

the page is real crude layout just so you can see how it all works.

Hope this helps .
 

Attachments

  • wireless_statements.xlsm
    23 KB · Views: 60
tommyt61,

Thanks for the quick reply. I think I failed to mention I am using office 2003. I went to use your sheet example; it wanted me to download the office compatibility pack for 2007. Not sure what to do at this point. Is there a way for this to be do natively in 2003 (.xls)?

BTW I am not familiar with all the new office stuff. Is the .xlsm for excel 2010?

Thanks,


Jerry L Englehaupt
Wireless World
 
Hi There

Having been involved with VB for the past week my skill-set is something to be desired. Lets see what the experts(have been's under pressure - sic) have to say!
 
.xls file for 2003

Here it is in 2003 format. Office 2007 and forward is a different format. i forget that alot of people are still running 2003 and earlier. The office compatability pack can be downloaded from Microsoft. Install it and it will allow 2003 to open files that have been created in 2007 and later.
 

Attachments

  • 2003_wireless_statements.xls
    50 KB · Views: 81
Thank you! I will check it out and let you know, but the test file works nice! I was able to get rid of the C:\ and use a UNC instead. Thanks! Thanks! ... and THANKS!!!

- Jerry Englehaupt
Wireless World
 
Hi Guys,


Now I am stuck with something like below, When Terms payslip & sheet1 refer to sample data attached.

Now I have certain requirement to a form which is payslip sheet, the data should be extracted from Sheet1 when any individual check box is created....at last the same data should be exported with name of the employee which is in sheet1 as a mail......to progress this i need your guidance.


Its kinda of urgent...please help me.


Thanks ahead,
Nani
 

Attachments

  • test.xls
    112.5 KB · Views: 55
HI Ken,
Please help me. I need to create a drop down list or buttons to give an option to convert an Excel 2003 to
1) Word
2) PDF
3) Power Point
I am totally new to macros. I tried using the above piece of code for PDF but I have CutePDF in my system so maybe that is why it does not work.
If you have an existing code to do so please paste it here so that I can work else guide me to do so.
 
I am not able to solve this datatype issue. please give me the solution.

I am not able to solve this datatype issue. please give me the solution. what changes i suppose to make to work this code?

Hi Ken,

Please bear with me. The above code works perfectly in the test workbook however when I transfer the same coding to my real project I'm having this error. Would really appreciate your help. Thanks alot.
View attachment 371
 
Hi Ken,
I would like to use the same code but i do not know where to paste this code and i believe i have the same problem with reference. Could pls help me step by step how to paste this code and where to paste it?

Thanks in advance..
 
Hi Paulmer,

How did you solve the reference problem. I have the same problem could you please help me with that.


Thanks in advance
 
I would like to use the same code but i do not know where to paste this code and i believe i have the same problem with reference. Could pls help me step by step how to paste this code and where to paste it?

Aaron,

In my signature line there is a big link to click to tell you where to put your code. Follow the steps there and you'll have it in the right place. Then the article I linked to in my previous post in this thread tells you how to set the reference issues. ;)
 
Dear Ken,

Thank alot for your time but i have tried as wat instructed in the article. I manage to find out about pasting the code but i have a new problem regarding the reference. The reference i have to choose is pdfcreator but i dont have this reference in my list. Could me help me??

Thank you again
 
Dear Ken,

Thank for your assitance. I have solved the reference problem. Has u know, I have to convert this excel activesheet into pdf and email it. I have combined the two set of code in the previous post but i don't why when i test it using my own email address i did not receive any email of the pdf. could you please check the code for me. the code is as below:

Code:
Sub PrintToPDF_Early()
'Author : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro Purpose: Print to PDF file using PDFCreator
' (Download from [URL]http://sourceforge.net/projects/pdfcreator/[/URL])
' Designed for early bind, set reference to PDFCreator
Dim pdfjob As PDFCreator.clsPDFCreator
Dim sPDFName As String
Dim sPDFPath As String
Dim bRestart As Boolean
Dim StrTo As String
Dim StrSubject As String
Dim StrBody As String
Dim Send As Boolean
Dim OutApp As Object
Dim OutMail As Object
'/// Change the output file name here! ///
sPDFName = "testPDF.pdf"
sPDFPath = ActiveWorkbook.Path & Application.PathSeparator
'Check if worksheet is empty and exit if so
If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub
'Activate error handling and turn off screen updates
On Error GoTo EarlyExit
Application.ScreenUpdating = False
Set pdfjob = New PDFCreator.clsPDFCreator
'Check if PDFCreator is already running and attempt to kill the process if so
Do
bRestart = False
Set pdfjob = New PDFCreator.clsPDFCreator
If pdfjob.cStart("/NoProcessingAtStartup") = False Then
'PDF Creator is already running. Kill the existing process
Shell "taskkill /f /im PDFCreator.exe", vbHide
DoEvents
Set pdfjob = Nothing
bRestart = True
End If
Loop Until bRestart = False
'Assign settings for PDF job
With pdfjob
.cOption("UseAutosave") = 1
.cOption("UseAutosaveDirectory") = 1
.cOption("AutosaveDirectory") = sPDFPath
.cOption("AutosaveFilename") = sPDFName
.cOption("AutosaveFormat") = 0 ' 0 = PDF
.cClearCache
End With
'Delete the PDF if it already exists
If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)
'Print the document to PDF
ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"
'Wait until the print job has entered the print queue
Do Until pdfjob.cCountOfPrintjobs = 1
DoEvents
Loop
pdfjob.cPrinterStop = False
'Wait until the file shows up before closing PDF Creator
Do
DoEvents
Loop Until Dir(sPDFPath & sPDFName) = sPDFName
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "xxx"
.CC = ""
.BCC = ""
.Subject = "Test"
.Body = "YYY"
.Attachments.Add sPDFPath & sPDFName
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Cleanup:
'Release objects and terminate PDFCreator
Set pdfjob = Nothing
Shell "taskkill /f /im PDFCreator.exe", vbHide
On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub
EarlyExit:
'Inform user of error, and go to cleanup section
MsgBox "There was an error encountered. PDFCreator has" & vbCrLf & _
"has been terminated. Please try again.", _
vbCritical + vbOKOnly, "Error"
Resume Cleanup
End Sub
 
Last edited by a moderator:
Hmm... not sure actually. Don't have time to troubleshoot right now, but try changing the .Send line to read .Display. That should pop the email up, and you can click the send button. Not ideal, but the code seems to be having issues resolving the email address for some reason.
 
Back
Top