Saving to PDF

[jrudolph]

I have a series of worksheets (used as billing statements) that I need to be able to save to PDF if cell "F6" is marked "Email".

Below is some code I found online, slightly modified, however I cant seem to get it right. Any ideas?

Sub Email_Statements()
'
' Save statments on sheets marked for "email" in PDF and
' email to the email address listed
' REQUIREMENTS: Excel 2007 with Microsoft's Save As PDF plugin.
' NOTES: Default quality is set to "xlQualityMinimum" to keep file sizes reasonable. You can change this to
' "xlQualityStandard" if you want the best detail. I have found that the xlQualityMinimum quality is fine in most cases.
' By Default this code will over-write a file of the same name if it exists in the output folder.
Dim i As Integer
Dim sOutputPath As String
Dim WSname As String
Dim ws As Worksheet
sOutputPath = "C:\Users\Public\Desktop\Test"
WSname = ActiveSheet.Name
'Loop through each worksheet
For Each ws In ActiveWorkbook.Worksheets
 
If CodeName <> "New" Then 'Skipp worksheet if named New
'Save each sheet marked as "Email" to PDF using OutputPath
If ws.Range("F6") = "Email" Then
 
Debug.Print Worksheets(WSname).Index & " " & sOutputPath & WSname
ActiveWorkbook.Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
sOutputPath & WSname, Quality:=xlQualityMinimum _
, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
 
End If
 
End If
 
On Error Resume Next
 
Next ws
 
End Sub

 

[Xander117]

Just a quick question since Im new to VBA myself, where does it error when you run the debug?

 

[jrudolph]

Sorry I should have specified that. I actually dont get errors, it just doesnt do what I want it to, which is to save each worksheet with "Email" in cell "F6" to the to PDF into a folder on the desktop.

 

[daverunt]

Maybe Outputpath should be

"C:\Users\Public\Desktop\Test\"

 

[daverunt]

I tried the code and it doesn't work for me. I get an error subscript out of range on the print section.

The print code refers to the sheet (i) but this isn't added anywhere in the code.
Also WSname = ActiveSheet.Name will print the activesheet repeatedly so you have to activate each sheet as you check them.

sOutputPath = "C:\Users\Public\Desktop\Test[COLOR=red]\[/COLOR]" [COLOR=#ff0000]-- added[/COLOR]
 
'Loop through each worksheet
For Each ws In ActiveWorkbook.Worksheets
[COLOR=red][COLOR=black]i = ws.Index[/COLOR] -- added[/COLOR]
[COLOR=red][COLOR=black]ws.Activate[/COLOR] -- added[/COLOR]
 
[COLOR=blue][COLOR=black]WSname = ActiveSheet.Name[/COLOR] -- moved[/COLOR]

 

[jrudolph]

Hi Dave - those changes appear to have worked. Thanks!

One last question for this. If I want to make it so that the OuputPath dynamically changes depending on who is using the workbook how could I do that?

I.e. If I open the file on my machine it saves to:

C:\Users\jrudolp\Desktop\Statements

but if i email the file to my assistant to run the reports it should dynamically save to:

C:\Users\[user name]\Desktop\Statements

 

[daverunt]

Hi,

environ("UserName") is probably the one that is correct.
I would try it on each PC

UsrNme= environ("UserName")
MsgBox UsrNme
 
 
If UsrNme = "Jack"
OuputPath =  "C:\Users\Jack\Desktop\Statements" 
ElseIf UsrNme= "Jill"
OuputPath =  "C:\Users\Jill\Desktop\Statements" 
ElseIf UsrNme= "jrudolp"
OuputPath =  "C:\Users\jrudolp\Desktop\Statements"
 
 
etc..

Give it a go and see.

There's also determining which computer it's running on
=environ(ComputerName)
Or whose registered version of Excel is running
Application.UserName

 

[jrudolph]

Thanks! Using the application.username worked perfect. I should have remembered that one!