Justin
asked on
Macro which automatically sends attachment to Outlook
Hi Guys, I have a macro which sends an Excel attachment to Outlook, sends it and then kills the attachment, then deletes the attachment.
I want the macro to save the attachment to the drive o:\ProductControl\DailyBS\ Sep 16. How do I do this?
Balance-Sheet_15Sep2016_FCD-Extract.xlsm
I want the macro to save the attachment to the drive o:\ProductControl\DailyBS\
Balance-Sheet_15Sep2016_FCD-Extract.xlsm
ASKER
I got the error message "Method 'SaveAs' of object '_Workbook' failed on this part of the code.
With Destwb
.SaveAs strFolder & Format(Date, "MMM YY") & "\" & TempFileName & FileExtStr
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
With Destwb
.SaveAs strFolder & Format(Date, "MMM YY") & "\" & TempFileName & FileExtStr
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
then try
MsgBox strFolder & Format(Date, "MMM YY") & "\" & TempFileName & FileExtStr
.SaveAs strFolder & Format(Date, "MMM YY") & "\" & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
What is the result of the msgbox?
MsgBox strFolder & Format(Date, "MMM YY") & "\" & TempFileName & FileExtStr
.SaveAs strFolder & Format(Date, "MMM YY") & "\" & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
What is the result of the msgbox?
ASKER
Result is "o:\ProductControl\Daily BS\Sep 16\ECB 15 Sep 2016.xlsx" in MsgBox
Then Method 'SaveAs' of object_Workbook' failed
.SaveAs strFolder & Format(Date, "MMM YY") & "\" & TempFileName & FileExtStr, FileFormat:=FileFormatNum
Then Method 'SaveAs' of object_Workbook' failed
.SaveAs strFolder & Format(Date, "MMM YY") & "\" & TempFileName & FileExtStr, FileFormat:=FileFormatNum
Does the folder already exist
ASKER
I am not sure why, but I recorded a Macro and saved it to that Folder and I received the message "Errors were detected whilst saving '\\10.32.64.28\Accounts\PR ODUCT CONTROL\Daily BS\ECB 15 Sep 2016.xlsm" Microsoft Excel may be able to save the file by removing or repairing some features."
I meant this folder
o:\ProductControl\Daily BS\Sep 16
o:\ProductControl\Daily BS\Sep 16
ASKER
Sorry, I was wrong . The path is o:\Product Control\Daily BS"
then try
MsgBox strFolder & TempFileName & FileExtStr
.SaveAs strFolder & TempFileName & FileExtStr, FileFormat:=FileFormatNum
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
ASKER
"Run time error '1004': Method 'Save as of object_ Workbook failed"
Since the error mentioned before
I am not sure why, but I recorded a Macro and saved it to that Folder and I received the message "Errors were detected whilst saving '\\10.32.64.28\Accounts\PR ODUCT CONTROL\Daily BS\ECB 15 Sep 2016.xlsm" Microsoft Excel may be able to save the file by removing or repairing some features."
I cannot help further
I am not sure why, but I recorded a Macro and saved it to that Folder and I received the message "Errors were detected whilst saving '\\10.32.64.28\Accounts\PR
I cannot help further
ASKER
Does it work when you try to save it to your Folder?
ASKER
I have switched it to another Folder and still getting: Run time error '1004': Method 'Save as of object_ Workbook failed"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
pls try
Open in new window
Regards