Need help with Output on VBA code

[Gkmorr]

Hey everyone, need some help here.

I have created the following code for emailing a survey (with a custom name) and then saving to a specified folder on a shared drive. There is no issue when emailing the report however I cannot get the file to save with a custom name in the shared folder.

I know it is probably something that is quite easy but I feel I am :banghead:

any help would be great

thanks in advance
____________________________________________________________

Private Sub EmailCallSurvey_Click()
   Dim stDocName As String
   Dim sAttachmentName As String
   Dim myPath As String
   Dim OutputTo As String
   Dim Name As Variant
   Dim emailto As String

   myPath = "shared drive\sAttachmentName" 'Shared drive folder where all Survey's are stored
   stDocName = "Rpt_call_survey_email" 'Name of the Access report Object to send
   sAttachmentName = Agent & " " & "#" & [Survey#] & " " & SurveyDate 'Name to be used for the attachment in the e-mail
   DoCmd.OpenReport stDocName, acViewPreview, , , acHidden
   Reports(stDocName).Caption = sAttachmentName
   Name = EmpName
   emailto = SpvEmail
   SurveyDate = SurveyDate
   stDocName = "Rpt_call_survey_email"
   DoCmd.SendObject acReport, stDocName, acFormatPDF, emailto, , , "Call Scorecard for" & " " & Name & " " & SurveyDate 'Subject line of email

   DoCmd.OutputTo acOutputReport, sAttachmentName, acFormatPDF, , , acHidden
   DoCmd.Close acReport, sAttachmentName

exit_EmailCallSurvey_Click:
   Exit Sub

Err_EmailCallSurvey_click:
   MsgBox Err.Description
   Resume exit_EmailCallSurvey_Click

End Sub

 

[isladogs]

I don't think you've defined the output path for the saved file, just the file name

Path:

myPath = "shared drive\sAttachmentName" 'Shared drive folder where all Survey's are stored

File name:

 sAttachmentName = Agent & " " & "#" & [Survey#] & " " & SurveyDate 'Name to be used for the attachment in the e-mail

Save file to:

DoCmd.OutputTo acOutputReport, sAttachmentName, acFormatPDF, , , acHidden

Try changing this to:

DoCmd.OutputTo acOutputReport, mypath & "\" & sAttachmentName, acFormatPDF, , , acHidden

Colin

 

[isladogs]

Sorry just read this post again
Reverse the order of your statements:

sAttachmentName = Agent & " " & "#" & [Survey#] & " " & SurveyDate

followed by the path as it depends on sAttachmentName:

myPath = "shared drive\sAttachmentName"

 

[MarkK]

...however I cannot get the file to save with a custom name in the shared folder.

When you say "you cannot get the file to save," what does that mean? Are you getting an error? You don't appear to be supplying a value to the DoCmd.OutputTo methods "OutputFile" parameter, and you are supplying acHidden to the TemplateFile parameter. Shouldn't this...

   DoCmd.OutputTo acOutputReport, sAttachmentName, acFormatPDF, , , acHidden

...look more like...

    DoCmd.OutputTo acOutputReport, sAttachmentName, acFormatPDF, "C:\SomeFolder\SomeFile123.pdf"

 

[Gkmorr]

OK I am now able to export to a shared folder after sending the email. Thanks for the suggestion. Now I am running into getting the output to save as a different name as the report name. I have tried a few things and cannot get the file to name as Agent Name & Survey # as I have stated sAttachmentName to use. It is only saving as sAttachmentName.pdf. Code below
Private Sub EmailCallSurvey_Click()
Dim stDocName As String
Dim sAttachmentName As String
Dim myPath As String
Dim OutputTo As String
Dim Name As Variant
Dim emailto As String
stDocName = "Rpt_call_survey_email" 'Name of the Access report Object to send
sAttachmentName = Agent & " " & "#" & [Survey#] 'Name to be used for the attachment in the e-mail
DoCmd.OpenReport stDocName, acViewPreview, , , acHidden
Reports(stDocName).Caption = sAttachmentName
Name = EmpName
emailto = SpvEmail
SurveyDate = SurveyDate
stDocName = "Rpt_call_survey_email"
DoCmd.SendObject acReport, stDocName, acFormatPDF, emailto, , , "Call Scorecard for" & " " & Name & " " & SurveyDate 'Subject line of email

DoCmd.OpenReport stDocName, acViewPreview, , , acHidden
Reports(stDocName).Caption = sAttachmentName
myPath = "Shared Folder\sAttachmentName"
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, myPath & "sAttachmentName.pdf"

exit_EmailCallSurvey_Click:
Exit Sub
Err_EmailCallSurvey_click:
MsgBox Err.Description
Resume exit_EmailCallSurvey_Click
End Sub

 

[Cronk]

Had you added a line

debug.print myPath & "sAttachmentName.pdf"

you would have seen in the immediate window this evaluates to

Shared Folder\sAttachmentNamesAttachmentName.pdf

Try

myPath = "Shared Folder\sAttachmentName\"
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, myPath & sAttachmentName

 

[Gkmorr]

Update***- the above code States outputting 'Test File #1' which is what I want but the file name is saving as sAttachmentNamesAttachmentName.pdf

 

[Gkmorr]

Try

myPath = "Shared Folder\sAttachmentName\"
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, myPath & sAttachmentName

Cronk - when I tried your suggestion I am not able to Output at all. I get a Run-time error '2501': The OutputTo action was cancelled.

Any other suggestions

 

[Gkmorr]

myPath = "Shared Folder\sAttachmentName\"
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, myPath & sAttachmentName

Cronk - I was playing around with what you had suggested and got it to work with taking out the "sAttachmentName" after myPath so it shows as:

myPath = "Shared Folder\" DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, myPath & sAttachmentName & ".pdf"

THANKS ALOT

Now I need to get the date of the Survey onto the attachment. The Survey Date is a field in the table shown as SurveyDate. When I add the field into the code I am getting a Run-time error '2501' again and will not output.

I am adding this piece at the top of the code
sAttachmentName = Agent & " " & "#" & [Survey#] & " " & SurveyDate

 

[MarkK]

I haven't read the whole thread here, but this is not a valid path in Windows...

SomeFolder\SomeSubFolder\

I would expect to either see a drive specifier first, or a double backslash for a computer name on a LAN. These are valid path examples...

C:\SomeFolder\SomeSubFolder\
\\SomeMachine\SomeFolder\SomeSubFolder\

hth