VBA: Save file in different environments

[BIGTONE559]

I've seen many examples of this. However I'm curious of an effective, yet simple way of saving an excel file in a users "My Documents" Folder. Keeping in mind the environment may change. Some Users use Win 7 and others XP

Cheers!

 

[kingkyle2005]

We convert the function into one that accepts a parameter being fed to it by another macro.

Option Explicit

Function GetSpecialFolderNames(ObjProperty As String) As String
Dim objFolders As Object
Set objFolders = CreateObject("WScript.Shell").SpecialFolders

    GetSpecialFolderNames = objFolders(ObjProperty)
    
End Function

Then your other code can feed the "MyDocuments" string (or any of the others given before) to the function and get the answer back, then string that together with other strings to create the full path/file instruction.

Sub test()

    ActiveWorkbook.SaveAs GetSpecialFolderNames("MyDocuments") & "\MyFile.xls"

End Sub

Sub test()

ActiveWorkbook.SaveAs GetSpecialFolderNames("MyDocuments") & "\" & "MyFile.xls"

End Sub

My Question is after saving it in a special folder, how can you re-access that folder, in order to add it as an attachment to an email?

    'SAVES IN GIVENDIRECTORY
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Program\Reports\" & Range("A10") & "_" & Range("AH2") & "_" & Format(Date, "m-d-yy") & "_MR_" & Range("AV1"), Quality _
        :=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
    Set rng = ActiveSheet.Range("BK85", "cn105")  'You can also use a sheet name     'Set rng = Sheets("YourSheet").UsedRange
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .Attachment.Add GetObject("WScript.Shell").SpecialFolders("Desktop") & "\Program\Reports\" & Range("A10") & "_" & Range("AH2") & "_" & Format(Date, "m-d-yy") & "_MR_" & Range("AV1") & ".pdf\"        
        .Save
        .To = strto & Range("m2") 'Name a range of cells as "Email"
        .CC = ""
        .BCC = ""
        .Subject = ActiveSheet.Range("A10") & "_" & Range("AH2") & "_" & Format(Date, "m-d-yy") & "_MR_" & Range("AV1")
        .HTMLBody = RangetoHTML1(rng) & "Thank you, and have a blessed day!" & "
" & Range("o1") & "
 " & Range("m5") & "
"
        
        .send   'or use .Display
    End With
    On Error GoTo 0

The process works if I directly link it to my personal computer, but that will not be sufficient for all users. It does work on my computer with ' report = "C:\Users\kmoore\Desktop\Program\Reports\" & Range("A10") & "_" & Range("AH2") & "_" & Format(Date, "m-d-yy") & "_MR_" & Range("AV1") & ".pdf" and then having .Attachment.add report

 

[jbeaucaire]

When I want to use a variable string more than once, I store those temporary strings and use them via variables.

Dim fPATH As String, fNAME As String

fPATH = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Program\Reports\"
fNAME = Range("A10") & "_" & Range("AH2") & "_" & Format(Date, "m-d-yy") & "_MR_" & Range("AV1") & ".pdf"


'SAVES IN GIVEN DIRECTORY
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPATH & fNAME, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False




    With OutMail
        .Attachment.Add fPATH & fNAME