Saveas Environ "HOMEPATH" working on Windows 10 PC, but NOT in server environment. Why?

mikenelena

New Member
Joined
Mar 5, 2018
Messages
37
I have code that, among other things, is supposed to generate an Outlook e-mail from Access, and save that e-mail to a folder. The code works in a PC Windows 10 environment, but we are testing in Windows Server 2012 R2, and the code is throwing an error at the line in red below. We've tried simplifying the path to just save to the desktop, so I really don't think the path itself is the problem. Seems that HOMEPATH command may not be right for a server environment. If anyone can help me to fix this, I would be most grateful. Thanks in advance!

Code:
Private Sub cmdCAL_Click()


Dim strDocName As String
Dim strWhere As String
Dim objEmailItem As MailItem
Dim strSaveName As String
Dim StrName As String


Me.Refresh


If Len(Dir("S:" & "\Google Drive\" & [Forms]![Invoicing_Form]![Report Appraiser] & "\" & [Forms]![Invoicing_Form]![Invoice_Number], vbDirectory)) = 0 Then
MsgBox "This folder is not in Google Drive. It might have been deleted or moved to Archives."
Then
Exit Sub
Else


DoCmd.OpenReport "Acknowledgement Letter", acViewPreview, "", "[Invoice_Number]=Forms![Invoicing_Form]![Invoice_Number]", acNormal
              DoCmd.OutputTo acOutputReport, "Acknowledgement Letter", "PDFFormat(*.pdf)", "S:" & "\Google Drive\" & [Forms]![Invoicing_Form]![Report Appraiser] & "\" & [Forms]![Invoicing_Form]![Invoice_Number] & "\Acknowledgement Letter " & [Forms]![Invoicing_Form]![Claim Number] & ".pdf", False, "", 0, acExportQualityPrint
               Appraiser] & "\" & [Forms]![Invoicing_Form]![Invoice_Number] & "\Acknowledgement Letter " & [Forms]![Invoicing_Form]![Claim Number] & ".pdf", False, "", 0, acExportQualityPrint


StrName = "Acknowledgement Letter " & [Report_Acknowledgement Letter]![Claim Number]


Set objOutlook = GetObject(, "Outlook.Application")


If objOutlook Is Nothing Then
Set objOutlook = New Outlook.Application


End If


Set objEmailItem = objOutlook.CreateItem(objlMailItem)


With objEmailItem
    .Display
    .To = [Report_Acknowledgement Letter]![E-mail3]
    .CC = [Report_Acknowledgement Letter]![email3] 'Delete any existing reply recipients


       
    If Me.[Client Name] = "Redacted Client Name" Then
.Subject = [Report_Acknowledgement Letter]![Claim Number] & "  Acknowledgement Letter - " & [Report_Acknowledgement Letter]![Vehicle Owner]


    Else
    .Subject = "Acknowledgement Letter " & [Report_Acknowledgement Letter]![Claim Number] & " " & [Report_Acknowledgement Letter]![Vehicle Owner]


    End If


.HTMLBody = "******>Dear " & [Report_Acknowledgement Letter]![First Name] & "," & "
" & "
" & "Our claim acknowledgement letter is attached.  Please contact our appraiser directly if you have any questions." & vbNewLine & Signature
        
        
.Attachments.Add "S:" & "\Google Drive\" & [Forms]![Invoicing_Form]![Report Appraiser] & "\" & [Forms]![Invoicing_Form]![Invoice_Number] & "\Acknowledgement Letter " & [Forms]![Invoicing_Form]![Claim Number] & ".pdf"


[B][COLOR=#ff0000]objEmailItem.SaveAs Environ("HOMEPATH") & "\Google Drive\" & [Forms]![Invoicing_Form]![Report Appraiser] & "\" & [Forms]![Invoicing_Form]![Invoice_Number] & "\" & StrName & ".msg", olMSG[/COLOR][/B]




End With


Set objEmailItem = Nothing
Set objOutlook = Nothing


End If


End Sub
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

james_lankford

Well-known Member
Joined
Jan 11, 2009
Messages
1,171
open up a cmd prompt on the testing environment (Windows Server 2012 R2)
and type
echo %homepath%

see what it says

on my computer it says
\Users\james_lankford
which is a valid folder

does your output also produce a valid folder ?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
Along the same lines in the immediate window you can type:
?environ("homepath")
to see what value you are getting.
 

mikenelena

New Member
Joined
Mar 5, 2018
Messages
37
Thanks James_Langford and Xenou. I've tried both of your suggestions, and both produce valid folder paths. There seems to be something about the SaveAs command that just doesn't want to function properly on Windows Server 2012 R2. I can't figure it out. I've spent hours messing with it, changing and trying all kinds of paths, including those on the virtual desktop, and on the client-side PC. I really don't think it is a path issue. I suspect it has something to do with the SaveAs command itself in this environment. But what? And if it absolutely won't work no matter what I try, can anyone suggest a work-around? Thanks!
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
if you want to know if SaveAs works the simplest is to test with a literal string value:

Code:
objEmail.SaveAs "C:\GoogleDrive\MyMessage.msg"
Then you can rule out other variables (or this one).
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
777
objEmailItem perhaps?
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
I probably had the wrong variable name. As noted try,
Code:
objEmailItem.SaveAs "C:\GoogleDrive\MyMessage.msg"
 

mikenelena

New Member
Joined
Mar 5, 2018
Messages
37
Thanks, I had caught that, actually. The error I noted came up with the corrected variable name. smh
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,628
Office Version
2013
Platform
Windows
There is only one object in this line of code.
Code:
objEmailItem.SaveAs "C:\GoogleDrive\MyMessage.msg"
so there has to be a problem with the objEmailItem reference. Are you stepping through the code line by line?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,713
Messages
5,446,102
Members
405,379
Latest member
EDGOUG

This Week's Hot Topics

Top