Rick Crucial
04-21-2015, 08:04 PM
Hi, I have a workbook that is completed by "User A" as a form, then by activating a macro through a button is sent to a nominated email address via Outlook to "User B". That part works fine.
'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "PCR Submission " & ActiveSheet.Range("J18").Text & " " & Format(Now, "mm-dd-yy")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = Range("G49")
.CC = Range("D52") & ";" & Range("D53") & ";" & Range("D55") & ";" & Range("D56") & ";" & Range("D57") & ";" & Range("D58")
.Subject = "Project Completion Review Submission" & " - " & Range("E22")
.HTMLBody = body of email removed
.Attachments.Add Destwb.FullName
.Send
End With
On Error GoTo 0
.Close savechanges:=False
End With
The problem occurs with the next step which is the recipient either approving or rejecting it via a very similar macro to the one above. It appears that because the attached file contains the file full name including path it produces an error because the recipient cannot access that path (the temp directory for each user has a path that contains that users unique ID).
eg D:\112233\\PCR Submission 71043 04-16-15 (2).xlsm
112233 is the User ID
PCR Submission 71043 04-16-15 (2).xlsm is the Filename
When I created the form and tested each step, it works fine, presumably because I can access my own filepath to the temp folder.
Any suggestions how I can save the file temporarily to email it but then email it as an attachment without the 'FullName'
'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "PCR Submission " & ActiveSheet.Range("J18").Text & " " & Format(Now, "mm-dd-yy")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.to = Range("G49")
.CC = Range("D52") & ";" & Range("D53") & ";" & Range("D55") & ";" & Range("D56") & ";" & Range("D57") & ";" & Range("D58")
.Subject = "Project Completion Review Submission" & " - " & Range("E22")
.HTMLBody = body of email removed
.Attachments.Add Destwb.FullName
.Send
End With
On Error GoTo 0
.Close savechanges:=False
End With
The problem occurs with the next step which is the recipient either approving or rejecting it via a very similar macro to the one above. It appears that because the attached file contains the file full name including path it produces an error because the recipient cannot access that path (the temp directory for each user has a path that contains that users unique ID).
eg D:\112233\\PCR Submission 71043 04-16-15 (2).xlsm
112233 is the User ID
PCR Submission 71043 04-16-15 (2).xlsm is the Filename
When I created the form and tested each step, it works fine, presumably because I can access my own filepath to the temp folder.
Any suggestions how I can save the file temporarily to email it but then email it as an attachment without the 'FullName'