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

mikenelena

Board Regular
Joined
Mar 5, 2018
Messages
139
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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 ?
 
Upvote 0
Along the same lines in the immediate window you can type:
?environ("homepath")
to see what value you are getting.
 
Upvote 0
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!
 
Upvote 0
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).
 
Upvote 0
I probably had the wrong variable name. As noted try,
Code:
objEmailItem.SaveAs "C:\GoogleDrive\MyMessage.msg"
 
Upvote 0
Thanks, I had caught that, actually. The error I noted came up with the corrected variable name. smh
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top