Macro Edit - Macro to save as PDF & Email as PDF

[CraigFord]

i have made a macro to save a file as a PDF (with filename from a cell) onto my desktop and then add it to an email as a PDF

my problem is this only works for me, once i put the document onto the server for others to use the macros dont work,

can somebody have a look at this for me,

the other problem is that allthough it saves as ref E5 the email does not

Sub SavePDF_EmailPDF()
'
' SavePDF_EmailPDF Macro
'

'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\craigford\Desktop\" & Cells(5, 5).Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\CRAIGF~1\AppData\Local\Temp\" & Cells(5, 5).Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Application.Dialogs(xlDialogSendMail).Show
End Sub

 

[Andrew Poulsom]

You can get the path to the desktop like this:

MsgBox CreateObject("WScript.Shell").SpecialFolders("Desktop")

 

[CraigFord]

You can get the path to the desktop like this:

MsgBox CreateObject("WScript.Shell").SpecialFolders("Desktop")

sorry im very new to macros, what would this need to replace?

 

[CraigFord]

You can get the path to the desktop like this:

MsgBox CreateObject("WScript.Shell").SpecialFolders("Desktop")

also if this is possable, is it possable to edit my code to save the PDF into the same location as the workbook?

this will not be a fixed location as there will be copy's of this form in multiple project files.

 

[Andrew Poulsom]

This:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\craigford\Desktop\" & Cells(5, 5).Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

could become:

Dim DeskTop as String
DeskTop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
Desktop & "\" & Cells(5, 5).Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

 

[Andrew Poulsom]

You can use ThisWorkbook.Path to return the path of the workbook that contains your code.

 

[CraigFord]

You can use ThisWorkbook.Path to return the path of the workbook that contains your code.

sorry! stupid question again, what would this replace in the code above.

P.s. the code above works perfectly

 

[Andrew Poulsom]

Like this?

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & Cells(5, 5).Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

 

[CraigFord]

Like this?

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & Cells(5, 5).Value & ".pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

you sir, are a legend.