• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Email active workbook as attachment

U

ushankar

New Member
I am trying to email active workbook via outlook. These are my codes. The email is displayed. however there is no attachment. Could anyone look at my codes and advise what I am missing?

Dim OutApp As Object

Dim OutMail As Object


Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)


On Error Resume Next

With OutMail

.To = "myself@abc.com"

.CC = ""

.BCC = ""

.Subject = "This is the Subject line"

.Body = "Hi there"

.Attachments.Add ActiveWorkbook.FullName

.Display

End With

On Error GoTo 0


Set OutMail = Nothing

Set OutApp = Nothing

End Sub
 
Hi Shankar,


All your code is working perfect. The only catch is that you may have not saved your spreadsheet on which you are trying to check this code.


Pls save the spreadsheet and then run the code again.


Regards,

Prasad DN

PS: You can remove "On Error Resume Next" line to check which line is causing trouble.
 
Thanks Prasad , is there a way to temporarily save and delete the file after mail send. I am trying to email a read only user form.

Appreciate your help
 
I am bit confused with your requirement..


Are you trying to add this VBA code in an excel file that is read only form?? OR Thru the automated file you want to attach some read only form file in your email and send?


Hope you are also aware that when you execute the above code (.Attachments.Add ActiveWorkbook.FullName), it will send the attachment with complete code as well, as that is the active file. Even if you delete the file, the file will still be available in sent items, or with recipient!


Regards,

Prasad DN
 
Sorry for the confusion Prasad. This is what I am trying to achive. I have a user form that I want to give to other people. I like them fill it up and email it to me without saving by clicking a command button on the user form. Hope it is clear now Thanks for ur help.
 
Thank you for clarifying me.


I tested your code and I even checked how it works for recipients after making changes in form as well, it works perfectly fine.


Just cross check two things:

1. When you are testing the code, the file should be saved with some file name. The recipient need not save, he can just open the attachment of email directly and update the form and click "button" on userform to send the file back to you.


2. The code also needs one more line:

Activeworkbook.save

else it would send the file without data


In case you are still getting any error, as suggested earlier remove or comment "On error resume next" code line and tell us what error you get and if you know debugging then also advise for which line the error is thrown.


Regards,

Prasad DN

PS: Hope I am able to solve this.
 
Thanks for all the help Prasad. there is still a small issue. As you suggested I added the extra line: Activeworkbook.save

It works fine on my desktop. I forward it to other people via email (or they open it in sharepoint). when others complete the form and try email it again, saving the worksheet doesnot work. I changed the line to Application.Dialogs(xlDialogsSaveAs).Show forcing the other user to save it first.

when the user save it, macro send the email which is good. if the user select CANCEL the save option, the macro still email the blank sheet.

Any suggestion to fix it very much appreciated.
 
Sorry did not notice you still had question.


Good you force the users to save it first, but since they are not following it, you have two options,

1. Telling/educating/warn them not to cancel save option

2. My favourite, use our vba skills again, by FORCING :) More effectively. I mean when the user select Cancel, bring back the saveas option again until they save it. ;)


Regards,

Prasad DN
 
Thank you Prasad. That is a great suggestion.

Could you please supply the codes to bring back the saveas option when the user select cancel?
 
No. Not all the users have access to the same directory. The local drive C is not accessible to every one. Therefore I have give the saveas option.
 
Just out of curiosity: how is it that somebody's local drive is not accessible to a person?
 
what I am looking for is if the user select "Cancel" in the dialog box, I want the codes to represent the dialog box again until the user click the save button.
 
Hi Again,


please place the following line of codes before the line:

Activeworkbook.save


'here is the code that forces the users to save the file


dim sFileNameSave as String


ReTake:

sFileNameSave = Application.GetSaveAsFilename(sFileSug, fileFilter:="Microsoft Excel File (*.xls), *.xls")


If sFileNameSave = False Then

Goto ReTake

end if


ActiveWorkbook.SaveAs sFileNameSave


'rest of your code you have after the line Activeworkbook.save


Regards,

Prasad DN

PS: This code will make every user to save the file.
 
Hi Prasad

The ReTake function works greatly when sFileNameSave is false. However when it is true I am getting an error mismatch. Could you please look at the codes one more time?
 
Don't worry Prasad, i have figured it out. I change the line from;

If sFileNameSave = False Then

to

If sFileNameSave = "False" Then
 
Hi all,


Interesting thread... here are my 2 cents based on my understanding...


Objectives / Scenario

1. A userform is being used to capture the data from the users and the data is saved on an excel sheet within the container workbook.

2. Next the user hits the send mail button and the Raw Data sheet has to be sent via email.


Proposed Solution


1. Use the Sheets(array("SheetName","OtherSheetName")).Copy to create a temporary workbook containing the raw data. (replace the sheet names as per the requirement.

2. Now Save the ActiveWorkbook.... Use the built in ENVIRON function to get the USERNAME (who is currently logged in onto the system).... add current date to this.


3. Create a temporary folder on the USER desktop and save the file there. (User may not have access to C: however they will have access to their desktop.


4. Use the temporary file from the desktop folder to be sent via email.

5. Delete the temp file and folder.


Benefits...


You will get the filename (for example JohnDoe_07-Feb-2012.xlsx) which will only contain the raw data and a sensible file name which can be used later for any analysis purposes...You also do not need to force the end user to save the file and provide a filename that may be inconsistent.


HTH

~VijaySharma
 
You must log in or register to reply here.
Back
Top